Finding specific data in Oracle Tables

Imagine, there are a few tables in your schema and you want to find a specific value in all columns within these tables. Ideally, there would be an sql function like

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

Unfortunately, there is no such function.

However, a PL/SQL function can be written that does that. The following function iterates over all character columns in all tables of the current schema and tries to find val in them.

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 ref cursor;
  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%'
    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);
 
      open c for 'select rowid from "' || v_old_table || '" ' || v_where;
 
      fetch c into v_rowid;
      loop
        exit when c%notfound;
        dbms_output.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;
/

The function in action

Let's see the function in action. First, some tables are created:

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)
);

Then, the tables are filled:

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' );

The function uses dbms_output. Therefore, we need to enable serveroutput in SQL*Plus.

set serveroutput on size 1000000 format wrapped

Executing the function:

select find_in_schema('Pear') from dual;

The output:

  rowid: AAACQNAAEAAAAHCAAB in TEST_FIND_1
  rowid: AAACQOAAEAAAAHKAAB in TEST_FIND_2

Now, these rowids can be used to find the rows:

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

Thanks

Thanks to Steve Stowers who found a bug and improved the code otherwise.