-- Compile objects containing references to table: begin execute immediate q'[alter session set plscope_settings = 'IDENTIFIERS:ALL, STATEMENTS:ALL']'; -- for r in ( select distinct 'alter '||replace(d.type, ' BODY')||' '||d.name||' compile' as sql_text from user_dependencies d join user_plsql_object_settings o on o.name = d.name and o.type = d.type where d.referenced_name = '%&Table%' and d.referenced_type = 'TABLE' ) loop dbms_output.put_line(r.sql_text); execute immediate r.sql_text; end loop; end; / -- Alternatively begin execute immediate q'[alter session set plscope_settings = 'IDENTIFIERS:ALL, STATEMENTS:ALL']'; for r in ( select distinct 'alter '||replace(s.type, ' BODY')||' '||s.name||' compile' as sql_text from user_source s where upper(s.text) like '%&Table%' ) loop dbms_output.put_line(r.sql_text); execute immediate r.sql_text; end loop; end; / select s.name, s.type, s.plscope_settings from user_plsql_object_settings s where s.plscope_settings <> 'IDENTIFIERS:NONE' order by 1,2; with subprograms (name, type, line, subprogram) as ( select /*+ materialize */ s.name, s.type, s.line, upper(regexp_substr(s.text, '[^ ().]+', 1, 2)) from user_source s where regexp_like(s.text,'^ *(PROCEDURE|FUNCTION) ', 'i') and s.name in (select i.object_name from user_identifiers i) ) select /*+ no_push_subq */ i.name as column_name, i.object_type, i.object_name , ( select subprogram from subprograms s where s.type = i.object_type and s.name = i.object_name and s.line < i.line order by line desc fetch first row only ) as subprogram , i.line from user_identifiers i where i.type = 'COLUMN' and i.usage = 'REFERENCE' and i.signature in ( select signature from user_identifiers s where s.usage = 'DECLARATION' and s.type = 'COLUMN' and s.object_type = 'TABLE' and s.object_name in ('&Table') ) order by i.name, i.object_name, i.object_type, i.line; with column_names as ( select s.object_name as table_name, s.name as column_name, s.signature from user_identifiers s where s.type = 'COLUMN' and s.object_type = 'TABLE' and usage = 'DECLARATION' and s.object_name in (/*'PSN_BPSS_BS_SEC',*/ '&Table') ) , subprograms (name, type, line, subprogram) as ( select /*+ materialize */ s.name, s.type, s.line, upper(regexp_substr(s.text, '[^ ().]+', 1, 2)) from user_source s where regexp_like(s.text,'^ *(PROCEDURE|FUNCTION) ', 'i') and s.name in (select i.object_name from user_identifiers i) ) select /*+ no_push_subq */ c.table_name, c.column_name, i.object_type, i.object_name , ( select subprogram from subprograms s where s.type = i.object_type and s.name = i.object_name and s.line < i.line order by line desc fetch first row only ) as subprogram , i.line from user_identifiers i join column_names c on c.signature = i.signature where i.type = 'COLUMN' and i.usage = 'REFERENCE' order by i.name, i.object_name, i.object_type, i.line; with column_names as ( select /*+ materialize */ s.object_name as table_name, s.name as column_name, s.signature from user_identifiers s where s.type = 'COLUMN' and s.object_type = 'TABLE' and s.usage = 'DECLARATION' and s.object_name in ('&Table') ) , subprograms (name, type, line, subprogram) as ( select /*+ materialize */ s.name, s.type, s.line, upper(regexp_substr(s.text, '[^ ().]+', 1, 2)) from user_source s where regexp_like(s.text,'^ *(PROCEDURE|FUNCTION) ', 'i') and s.name in (select i.object_name from user_identifiers i) ) , identifiers as ( select /*+ materialize */ name, object_name, object_type, line, signature from user_identifiers where type = 'COLUMN' and usage = 'REFERENCE' ) select /*+ dynamic_sampling(11) no_push_subq */ c.table_name, c.column_name, i.object_type, i.object_name , ( select sp.subprogram from subprograms sp where sp.type = i.object_type and sp.name = i.object_name and sp.line < i.line order by line desc fetch first row only ) as subprogram , i.line , sc.text from identifiers i join column_names c on c.signature = i.signature join user_source sc on sc.name = i.object_name and sc.type = i.object_type and sc.line = i.line order by i.name, i.object_name, i.object_type, i.line; -- Or just the distinct procedure names: with column_names as ( select /*+ materialize */ s.object_name as table_name, s.name as column_name, s.signature from user_identifiers s where s.type = 'COLUMN' and s.object_type = 'TABLE' and s.usage = 'DECLARATION' and s.object_name in ('&Table') ) , subprograms (name, type, line, subprogram) as ( select /*+ materialize */ s.name, s.type, s.line, upper(regexp_substr(s.text, '[^ ().]+', 1, 2)) from user_source s where regexp_like(s.text,'^ *(PROCEDURE|FUNCTION) ', 'i') and s.name in (select i.object_name from user_identifiers i) ) , identifiers as ( select /*+ materialize */ name, object_name, object_type, line, signature from user_identifiers where type = 'COLUMN' and usage = 'REFERENCE' ) select /*+ dynamic_sampling(11) no_push_subq */ distinct c.table_name, c.column_name, i.object_type, i.object_name , ( select sp.subprogram from subprograms sp where sp.type = i.object_type and sp.name = i.object_name and sp.line < i.line order by line desc fetch first row only ) as subprogram from identifiers i join column_names c on c.signature = i.signature order by 1,2,3,4,5;