René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback

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

  for r in (
      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;
        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;
      v_where := v_where || ' or ' || r.column_name || ' like ''%' || val || '%''';
    end if;
  end loop;
  return 'Success';

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:
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 to Steve Stowers who found a bug and improved the code otherwise.


Niall Litchfield has written a Java Class that does obviously the same.