Finding specific data in Oracle Database (GREP)

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

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.

<a href="http://www.adp-gmbh.ch/ora/sqlplus/serveroutput.html"><span style="color: #553333;">set serveroutput</span></a> 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';