select * from * where any(column) = 'value';

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 rc     is <a href="http://www.adp-gmbh.ch/ora/plsql/cursors/ref_cursors.html"><span style="color: #553333;">ref cursor</span></a>;
  c           rc;
  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%'
    <a href="http://www.adp-gmbh.ch/ora/sql/order_by.html"><span style="color: #553333;">order by</span></a> t.table_name) loop

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

    if v_old_table &lt;&gt; r.table_name then
      v_first_col := true;

      -- dbms_output.put_line('searching ' || v_old_table);

      open c for 'select <a href="http://www.adp-gmbh.ch/ora/concepts/rowid.html"><span style="color: #553333;">rowid</span></a> from "' || v_old_table || '" ' || v_where;

      fetch c into v_rowid;
      loop
        exit when c%notfound;
        <a href="http://www.adp-gmbh.ch/ora/plsql/dbms_output.html#put_line"><span style="color: #553333;">dbms_output</span></a>.put_line('  rowid: ' || v_rowid || ' in ' || v_old_table);
        fetch c into v_rowid;
      end loop;

      v_old_table := r.table_name;
    end if;

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

  end loop;
  return 'Success';
end;
/

create table test_find_1 (
  a number,
  b varchar2(10),
  c varchar2(20),
  d varchar2(30)
);

create table test_find_2 (
  e number,
  f varchar2(30),
  g varchar2(20),
  h varchar2(10)
);

create table test_find_3 (
  i number,
  j varchar2(15),
  k varchar2(15),
  l varchar2(15)
);

insert into test_find_1 values (1, 'Orange' , 'Grape'     , 'Papaya' );
insert into test_find_1 values (2, 'Apple'  , 'Pear'      , 'Coconut');
insert into test_find_1 values (3, 'Mango'  , 'Lime'      , 'Banana' );

insert into test_find_2 values (1, 'Apricot', 'Kiwi'      , 'Lemon'  );
insert into test_find_2 values (2, 'Peach'  , 'Dates'     , 'Pear'   );
insert into test_find_2 values (3, 'Lime'   , 'Mango'     , 'Grape'  );

insert into test_find_3 values (1, 'Papaya' , 'Banana'    , 'Mango'  );
insert into test_find_3 values (2, 'Lime'   , 'Plum'      , 'Cherry' );
insert into test_find_3 values (3, 'Rhubarb', 'Pineapple' , 'Carrot' );

<a href="http://www.adp-gmbh.ch/ora/sqlplus/serveroutput.html"><span style="color: #553333;">set serveroutput</span></a> on size 1000000 format wrapped

select find_in_schema('Pear') from dual;

  rowid: AAACQNAAEAAAAHCAAB in TEST_FIND_1
  rowid: AAACQOAAEAAAAHKAAB in TEST_FIND_2

select * from test_find_1 where rowid = 'AAACQNAAEAAAAHCAAB';
select * from test_find_2 where rowid = 'AAACQOAAEAAAAHKAAB';