Tip of the Week
Tip for Week of April 25, 2005

Search Using Dynamic PL/SQL

This tip comes from Bhagwan Singh, an Assistant Consultant at TCS Ltd, in Mumbai, India.

This script returns the column and table names for a value input by a user. For example, if we know that there is a character value of 'US-15017094' with no other details available, this routine will search the user schema and list both table and column name for that value.

Declare
searchtxt VARCHAR2(30):='US-15017094';
sqltxt VARCHAR2(255);
resultcount pls_integer:=0;
Begin
for c in (select table_name, column_name from USER_tab_columns
where table_name in ('ES_ALERT','AM_REGION','ES_ALERT_CACHE_PO')
and data_type in ('CHAR','VARCHAR2'))
loop
resultcount:=0;
sqltxt := 'select count(1) from '|| c.table_name||' 
where '|| c.column_name||' ='||chr(39)||searchtxt||chr(39);
execute immediate sqltxt into resultcount;
IF resultcount=1 THEN
dbms_output.put_line(c.table_name || '.' || c.column_name);
END IF;
end loop;
End;