Dev: Find string in table

--
-- Sometimes it is needed to find column/rowid in table with value from screen of web --application etc. That procedure helps to do that, example:
--09:09:43 SQL> create table test as select * from dba_objects;
--...
--09:10:53 SQL> /
--Enter value for table_name: test
--Enter value for string: DBA_OBJECTS
--GENERATED
--OBJECT_NAME
--rowid: AAAMvZAABAAAPk+AAj in SYS.TEST <<-- finding only first occurence
-- Should not be used as is for partitioned tables
--
set serveroutput on
declare
procedure find_string_in_table(val varchar2)
is
v_where Varchar2(32767);
type rc is ref cursor;
c rc;
v_rowid rowid;
begin
for r in (
select
t.owner||'.'||t.table_name table_name, t.column_name
from
dba_tab_cols t
where t.table_name = upper(trim('&table_name'))
and t.data_type like '%CHAR%'
order by t.column_name) loop
dbms_output.put_line(r.column_name);
v_where := ' where ' || r.column_name || ' like ''%' || val || '%''';

open c for 'select rowid from ' || r.table_name || ' ' || v_where;

fetch c into v_rowid;
loop
fetch c into v_rowid;
exit when c%notfound;
dbms_output.put_line(' rowid: ' || v_rowid || ' in ' || r.table_name);
return;
end loop;
end loop;
end;
begin
find_string_in_table('&string');
end;
/