Packages and the tables & columns they reference, take 1. Click here for take 2.

A method to find out which packages reference which columns. It's not always perfect (because it uses text matching it tends to find too much), but I have found it to be very useful when trying to find out what impact a table/column change is likely to have.

See the package code for more details.
In this example, we're looking for packages that either reference columns named like '%employee_id%' or '%department_id%', or the 'disposition' or 'filename' columns in table 'ted_mail_test_parts'. You'll want to adjust the where clauses to your needs.

Either run this as sys/system, or give the user these privileges: grant select on dba_dependencies to tedstruik; grant select on dba_source to tedstruik; grant select on dba_users to tedstruik; grant select on dba_col_comments to tedstruik; grant select on dba_tab_cols to tedstruik; grant select on dba_tables to tedstruik;

Region

Identification
	Sequence	10
	Title	Tables & Columns
	Type	Report
Source
	Source Type	SQL_QUERY
	Region Source	select tc.owner || '.' || tc.table_name as table_name , tc.column_name , tc.data_type || '(' || trim(tc.data_length) || ')' as data_type , tc.comments from table(ted_p1079.tab) tc order by 1,2

Region

Identification
	Sequence	20
	Title	Package Dependencies 1
	Type	Report
Source
	Source Type	SQL_QUERY
	Region Source	select d.s_owner || ' / ' || d.s_name as object_name , d.s_type as object_type , d.tc_owner || ' / ' || d.tc_table_name as tc_table_name , d.tc_mask , d.s_linenumbers , apex_item.textarea(2,d.s_lines,5,60, 'class="codemirror-plsql"') as s_lines from table(ted_p1079.dep) d order by 1,2,3,4

Package

Package
	Name	TED_P1079
	Source	package ted_p1079 is type tab_rec is record ( owner varchar2( 100) , table_name varchar2( 100) , column_name varchar2( 100) , mask varchar2( 100) , data_type varchar2( 100) , data_length number , comments varchar2(4000) ) ; type tab_tab is table of tab_rec; type dep_rec is record ( s_owner varchar2( 100) , s_name varchar2( 100) , s_type varchar2( 100) , tc_owner varchar2( 100) , tc_table_name varchar2( 100) , tc_mask varchar2( 100) , s_linenumbers varchar2(4000) , s_lines clob , lvl number , path_text varchar2(4000) , d1 varchar2(4000) , d1_owner varchar2( 100) , d1_name varchar2( 100) , d1_type varchar2( 100) , d2 varchar2(4000) , d2_owner varchar2( 100) , d2_name varchar2( 100) , d2_type varchar2( 100) , n number ) ; type dep_tab is table of dep_rec; function tab return tab_tab pipelined ; function dep return dep_tab pipelined ; end ted_p1079;

Package Body

Package Body
	Name	TED_P1079
	Source	package body ted_p1079 is cursor c_tab is -- all the tables/columns you want to find dependencies for -- this is just an example to select some table/column rows, feel free to change this query select tc.owner , tc.table_name , tc.column_name , '%' || lower(tc.column_name) || '%' as mask , tc.data_type , tc.data_length , cc.comments from dba_tables t , dba_tab_cols tc , dba_col_comments cc where t.owner = tc.owner and t.table_name = tc.table_name and tc.owner = cc.owner and tc.table_name = cc.table_name and tc.column_name = cc.column_name -- and ( tc.column_name like '%EMPLOYEE_ID%' or tc.column_name like '%DEPARTMENT_ID%' or ( tc.table_name = 'TED_MAIL_TEST_PARTS' and tc.column_name in ('DISPOSITION', 'FILENAME') ) ) and tc.column_name not in ( 'SOMECOLUMNNAME' ) -- and tc.owner in ( 'TEDSTRUIK', 'HR' ) -- and tc.owner||'.'||tc.table_name not in ( 'TEDSTRUIK.SOMETABLE' ) and tc.table_name not like 'TMP%' and tc.table_name not like 'MDRT%' and tc.table_name not like 'EBA%' order by 1,2,3 ; cursor c_dep ( p_d2_owner in varchar2 , p_d2_name in varchar2 , p_d2_type in varchar2 , p_mask in varchar2 ) is with users as -- all relevant schemas ( select usr.username from dba_users usr where usr.username in ( 'TEDSTRUIK', 'HR' ) union all select 'PUBLIC' from dual ) , d as /* The dba_dependencies view describes all dependencies in the database between procedures, packages, functions, package bodies, and triggers, including dependencies on views created without any database links. */ ( select dpy.owner||'.'||dpy.name||'#'||dpy.type as d1 , dpy.owner as d1_owner , dpy.name as d1_name , dpy.type as d1_type , dpy.referenced_owner||'.'||dpy.referenced_name||'#'||dpy.referenced_type as d2 , dpy.referenced_owner as d2_owner , dpy.referenced_name as d2_name , dpy.referenced_type as d2_type from dba_dependencies dpy where dpy.owner in ( select username from users ) and dpy.type in ('SYNONYM', 'VIEW', 'TABLE', 'PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE', 'MATERIALIZED VIEW') and dpy.referenced_owner in ( select username from users ) and dpy.referenced_type not in ('NON-EXISTENT', 'TYPE', 'SEQUENCE') ) , dd as -- all dependencies with their startpoint and endpoint ( select level as lvl , sys_connect_by_path(d2, '->') || '->' || d1 as path_text , d1 , d1_owner , d1_name , d1_type , d2 , d2_owner , d2_name , d2_type from d -- exclude some objects if needed -- having these where clauses here makes the query much faster here than in the dpy part above where d1_name not like 'TMP%' and d1_name not like 'MDRT%' and d1_name not like 'EBA%' and d1_name not like 'BU%' start with d2_owner = p_d2_owner and d2_name = p_d2_name and d2_type = p_d2_type connect by nocycle prior d.d1 = d.d2 ) , s as ( select s2.owner , s2.name , s2.type , s2.d1_owner , s2.d1_name , s2.d1_type , to_char( rtrim(xmlagg(xmlelement(e,s2.line,', ').extract('//text()') order by s2.line).GetClobVal(),', ') ) as linenumbers , rtrim(xmlagg(xmlelement(e,s2.line || ': ' || s2.text,null).extract('//text()') order by s2.line).GetClobVal(),chr(10)||chr(13)) as lines from ( select distinct src.* , dd.d1_owner , dd.d1_name , dd.d1_type from dba_source src , dd where lower(src.text) like p_mask and src.owner = dd.d1_owner and src.name = dd.d1_name and src.type = dd.d1_type and src.type in ('PACKAGE', 'PACKAGE BODY') -- exclude some objects if needed and src.name not like 'TMP%' ) s2 group by s2.owner , s2.name , s2.type , s2.d1_owner , s2.d1_name , s2.d1_type ) select s.owner as s_owner , s.name as s_name , s.type as s_type , p_d2_owner as tc_owner , p_d2_name as tc_table_name , p_mask as tc_mask , s.linenumbers as s_linenumbers , dbms_xmlgen.convert(xmldata => s.lines, flag => 1) as s_lines , dd.* , count(*) over (partition by s.owner, s.name, s.type order by dd.path_text rows between unbounded preceding and current row) as n from dd , s where dd.d1_owner = s.d1_owner (+) and dd.d1_name = s.d1_name (+) and dd.d1_type = s.d1_type (+) ; function tab return tab_tab pipelined is begin for r_tab in c_tab loop pipe row(r_tab); end loop; return; end tab; function dep return dep_tab pipelined is begin for r_tab in c_tab loop for r_dep in c_dep ( p_d2_owner => r_tab.owner , p_d2_name => r_tab.table_name , p_d2_type => 'TABLE' , p_mask => r_tab.mask ) loop if r_dep.s_linenumbers is not null and r_dep.n = 1 -- only show distinct results then pipe row(r_dep); end if; end loop; end loop; return; end dep; end ted_p1079;