how to find specific text in oracle schema using a function

The following function will find any text matching the arguments given at command line.
(output may not display on screen, but data will be inserted in the relevant table)

To execute: [sql]select find_in_schema(‘ABC_553’) from dual;[/sql]

Credit:: Rene Nyffenneger on Oracle

PL/SQL code:

[sql]create or replace function find_in_schema(val varchar2) return varchar2 is
  v_old_table user_tab_columns.table_name%type;
  v_where Varchar2(4000);
  v_first_col boolean := true;
  type ref_cur is ref cursor;
  cur ref_cur;
  v_rowid varchar2(20);

begin
  for r in (select t.*
              from user_tab_cols t, user_all_tables a
             where t.table_name = a.table_name
               and t.data_type like '%CHAR%'
             order by t.table_name) loop

    if v_old_table is null then
      v_old_table := r.table_name;
    end if;

    if v_old_table <> r.table_name then
      v_first_col := true;

      --dbms_output.put_line('searching ' || v_old_table);
      --dbms_output.put_line('select rowid from "' || v_old_table || '" ' || v_where);

      open cur for 'select rowid from "' || v_old_table || '" ' || v_where;

      fetch cur
        into v_rowid;

      loop
        exit when cur% notfound;
        dbms_output.put_line('  rowid: ' || v_rowid || ' in ' || v_old_table);
        --TODO
        --insert into records_found(rowid_number,table_name)
        --     values(v_rowid,v_old_table);
        fetch cur
          into v_rowid;
      end loop;

      v_old_table := r.table_name;
    end if;

    if v_first_col then
      v_where     := ' where ' || r.column_name || ' = ''' || val || '''';
      v_first_col := false;
    else
      v_where := v_where || ' or ' || r.column_name || ' = ''' || val || '''';
    end if;

  end loop;
  return 'Done searching.';
end;
[/sql]