Query to find data in Oracle tables.

SET SERVEROUTPUT ON

declare

     num_rows number;

     sql_text varchar2(250);

     sql_info varchar2(100);

     v_schema varchar2(100):= 'schema name that contains tables';

 begin

     dbms_output.enable(10000000);

     for x in (select table_name, column_name from dba_tab_columns

                where data_type in ('VARCHAR','VARCHAR2','CHAR')

                  and owner=v_schema

                  AND ( TABLE_NAME NOT LIKE 'BIN%'))

     loop

        begin

            sql_text:='select count(0) into :num_rows from '||v_schema||'.'||x.table_name||' where lower( '||x.column_name||' ) =''test data''';

            -- dbms_output.put_line (sql_text);

            execute immediate sql_text into num_rows;

            if num_rows>0

            then

              sql_info:='select '||x.column_name||'  from '||x.table_name||'

              union all';

              -- dbms_output.put_line (sql_info);

            end if;

        exception when others then

        dbms_output.put_line (sql_text);

        end;

     end loop;

 end;