Hi Tom,
first of all, I was right about that “and rownum = 1” that I mentioned earlier! Insert smug grin here! However, as you noted, you just get the same message over and obver until your session runs out of “unlimited” dbms_output buffer space.
What you need to do to fix this is to replace this:
open l_cursor for 'select ’ || l_cols || ‘else null end cname from LEEDS_CONFIG.’ ||
x.table_name || l_where || ‘) and rownum=1’;
With the following:
open l_cursor for 'select DISTINCT ’ || l_cols || ‘else null end cname from LEEDS_CONFIG.’ ||
x.table_name || l_where || ‘)’;
We are adding a DISTINCT and removing the afore mentioned “and rownum = 1”.
The “rownum = 1” clause forced the database to only return the first column in its list where there was a find. All the rest were ignored. Removing that clause means that we get EVERY columns that has a find. However, we get the column name once for each row in the table that has the search text in that column. Hence the DISTINCT.
The DISTINCT will reduce this to one row per column. So you only get a small list of columns rather than a huge one.
So I tried it out here on a small table, with only a few hundred rows, three columns have the desired search text.
With the “and rownum = 1” left in, and no DISTINCT, I only saw a single row. This contained only the first column that had the search text in it. This is what you are seeing.
With “and rownum = 1” removed, I got hundreds of rows returned, with numerous duplicates. This caused my “unlimited” dbms_output buffer to blow up. So much for unlimited then!
Adding DISTINCT reduced the output to that desired. Only a single mention of the columns and all the columns were listed. This is what I think you are looking for.
My test results were limited to a single table, and there were three columns that definitely had the search text in:
Time Start: 2017/04/19 13:31:13
Data Searched: ABCXYZ
FOUND: DATABASES - “COLUMN_ONE”
select * from NORM.DATABASES where “COLUMN_ONE” like ‘%ABCXYZ%’;
FOUND: DATABASES - “COLUMN_TWO”
select * from NORM.DATABASES where “COLUMN_TWO” like ‘%ABCXYZ%’;
FOUND: DATABASES - “COLUMN_THREE”
select * from NORM.DATABASES where “COLUMN_THREE” like ‘%ABCXYZ%’;
3 tables found.
PL/SQL procedure successfully completed.
Time End: 2017/04/19 13:31:14
Elapsed Time for Script Execution: 945 msecs
This script will runs for some time if you have a lot of tables and/or those tables have a large number of rows. There should be a manner of limiting the search that creates the list of columns, to a single hit, but with the various CASE statements, I’m not sure that it can be done. So we are hitting every row in the table to determine if there are any hits on the search text even when we have already found at least one row containing the search text. An exercise for the reader - as they say! 
By the way, I added the “FOUND” bit to the output for debugging, but I left it in the final script that I ran. Which is:
set serveroutput on size unlimited
declare
i_count number:=0;
l_cols long;
l_where long;
l_cursor sys_refcursor;
l_cname varchar2(4000);
p_string varchar2(100):=‘ABCXYZ’;
begin
dbms_application_info.set_client_info( ‘%’ || p_string || ‘%’ );
dbms_output.put_line(‘Data Searched: ’ || p_string);
dbms_output.put_line(’ ');
for x in ( select table_name
from all_tables
where owner = 'NORM'
order by table_name )
loop
l_cols := 'case when 1=0 then ''x'' ';
l_where := ' where ( 1=0 ';
for y in ( select '"' || column_name || '"' column_name
from all_tab_columns
where table_name = x.table_name
and owner = 'NORM'
and (data_type in ( 'CHAR', 'NVARCHAR2', 'VARCHAR2' ))
) loop
l_cols := l_cols || ' when ' || y.column_name ||
' like sys_context(''userenv'',''client_info'') then ' ||
' ''' || y.column_name || '''';
l_where := l_where || ' or ' || y.column_name || ' like sys_context(''userenv'',''client_info'') ';
end loop;
open l_cursor for 'select distinct ' || l_cols || 'else null end cname from NORM.' ||
x.table_name || l_where || ')';
loop
fetch l_cursor into l_cname;
exit when l_cursor%notfound;
i_count:=i_count + 1;
dbms_output.put_line('FOUND: ' || x.table_name || ' - ' || l_cname );
dbms_output.put_line('select * from NORM.' || x.table_name || ' where ' || l_cname || ' like ''%' || p_string || '%'';');
dbms_output.put_line(' ');
end loop;
close l_cursor;
end loop;
dbms_output.put_line(' ');
dbms_output.put_line(i_count || ' tables found.');
dbms_output.put_line(' ');
end;
/
I also added in the owner of the tables to the various SELECT scripts and cursors, it saves me having to be in that user. Hard coded in my test, but you could concatenate the username and a dot into the SQL so you only have to change it once, if you ever need to change the table owner. (NORM in the above.)
HTH
