ops$tkyte@ORA920.US.ORACLE.COM> create or replace
  2  procedure find_string( p_str in varchar2 )
  3  authid current_user
  4  as
  5      l_query    long;
  6      l_case     long;
  7      l_runquery boolean;
  8      l_tname    varchar2(30);
  9      l_cname    varchar2(30);
 10  begin
 11      dbms_application_info.set_client_info( '%' || upper(p_str) || '%' );
 12
 13      for x in (select * from user_tables )
 14      loop
 15          l_query := 'select ''' || x.table_name || ''', $$
 16                        from ' || x.table_name || '
 17                       where rownum = 1 and ( 1=0 ';
 18          l_case := 'case ';
 19          l_runquery := FALSE;
 20          for y in ( select *
 21                       from user_tab_columns
 22                      where table_name = x.table_name
 23                        and data_type in ( 'VARCHAR2', 'CHAR' )
 24                   )
 25          loop
 26              l_runquery := TRUE;
 27              l_query := l_query || ' or upper(' || y.column_name ||
 28                         ') like userenv(''client_info'') ';
 29              l_case := l_case || ' when upper(' || y.column_name ||
 30                        ') like userenv(''client_info'') then ''' ||
 31                        y.column_name || '''';
 32          end loop;
 33          if ( l_runquery )
 34          then
 35              l_case := l_case || ' else NULL end';
 36              l_query := replace( l_query, '$$', l_case ) || ')';
 37              begin
 38                  execute immediate l_query into l_tname, l_cname;
 39                  dbms_output.put_line
 40                  ( 'Found in ' || l_tname || '.' || l_cname );
 41              exception
 42                  when no_data_found then
 43                      dbms_output.put_line
 44                      ( 'No hits in ' || x.table_name );
 45              end;
 46          end if;
 47
 48      end loop;
 49  end;
 50  /