set serveroutput on linesize 4000
declare
sqltxt clob;
cnt integer;
begin
cnt:=0;
for x in (select sql_id, sql_fulltext from gv$sql where regexp_like (sql_fulltext ,'OBJECT_NAME','i')
and not regexp_like (sql_fulltext ,'^declare|sql_fulltext|sys.col','i'))
loop
cnt:=cnt+1;
DBMS_OUTPUT.PUT_LINE(lpad(cnt,5,0)||': '|| x.sql_id ||'-'||x.sql_fulltext );
end loop;
exception
WHEN OTHERS then
declare
errcd NUMBER := SQLCODE;
errmsg VARCHAR2(300) := SQLERRM;
begin
DBMS_OUTPUT.PUT_LINE('Error: rec#'|| cnt || ' rc='|| errcd ||','||errmsg);
end;
end;
/
