-- 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;


