The following function will find any text matching the variable set within the procedure.
(the output will display on screen -dbms_output)

To execute: <SQL> [sql]execute update_found_records_proc;[/sql]

We could also add a parameter on calling the procedure as:
CREATE OR REPLACE PROCEDURE FIND_IN_RECORDS_PROC(val IN varchar2) IS…
note: ‘val’ is set once and used anywhere later in the code

Credit:: Rene Nyffenneger on Oracle

PL/SQL code:

[sql]CREATE OR REPLACE PROCEDURE "UPDATE_FOUND_RECORDS_PROC" 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);
  val varchar2(100); --the value to be found

BEGIN
  begin
    val := 'ABC_553';
    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);
          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;
    close cur;
    commit;
    --return 'Done searching.';
  end;

EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001, 'Error encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM);
END UPDATE_FOUND_RECORDS_PROC;
[/sql]