Searching for a value in an entire table

Imagine, you have a table with some 40 columns, and you want to search for a specific value within these rows. One approach would be to do something like select * from the_table where row_01 like '%VAL%' or row_02 like '%VAL%'....

set      feedback off
set      pagesize 0
set      verify   off

accept table_name prompt 'Enter table name: '
accept value_str  prompt 'Enter value to be searched: '

set termout off
spool fi.sql
select 'set feedback off' from dual;
select 'prompt' from dual;
select 'prompt ****************************************' from dual;
select 'prompt &value_str is found in the following rows' from dual;
select 'prompt ****************************************' from dual;
select 'select * from &table_name where ' from dual;
select 'upper(' || column_name || ') like upper(''%&value_str.%'')  or ' 
  from user_tab_columns 
  where 
    table_name=upper('&table_name') and 
    column_id < ( select max(column_id) 
      from user_tab_columns 
      where table_name = upper('&table_name'));
select 'upper(' || column_name || ') like upper(''%&value_str%''); ' 
  from user_tab_columns 
  where table_name = upper('&table_name') and 
    column_id = (select max(column_id) 
    from user_tab_columns 
    where table_name = upper('&table_name'));
spool off
set termout on
@fi.sql


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