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

A second method to find out which packages reference which columns. This one uses the elegant "dba_dependency_columns" method presented by Rob van Wijk.
The view ted_dependency_columns_v I use is identical to Rob's dba_dependency_columns view, except for the formatting.

I have noticed that dependecies of packages on synonyms (for tables/views) are not always included in the results. I suspect that these dependencies are not included in the sys.dependency$ view,but that is something to be investigated.

In the report above I have added an outer join with dba_source, to check if the column is actually mentioned in the source text. That way you avoid finding matches that reference the entire row, in constructions like table%rowtype or select * from table, but that never actually use the columns you're looking for.
Note that the source of (materialized) views is not checked; for that you'd need a select on dba_(m)views and some way to convert the longs to clobs.

In this example, we're looking for packages that reference column 'disposition' 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 "_CURRENT_EDITION_OBJ" to tedstruik; grant select on disk_and_fixed_objects to tedstruik; grant select on dependency$ to tedstruik; grant select on user$ to tedstruik; grant select on col$ to tedstruik;

Region

Identification
	Sequence	20
	Title	Package Dependencies 2
	Type	Report
Source
	Source Type	SQL_QUERY
	Region Source	with c as ( select upper('TEDSTRUIK') as referenced_owner , upper('TED_MAIL_TEST_PARTS') as referenced_name , upper('disposition') as referenced_column from dual ) , s as ( select distinct t.owner , t.name , t.type , c2.referenced_column from dba_source t , ( select distinct c.referenced_column from c ) c2 -- distinct is not really needed here but should be a bit faster where upper(t.text) like '%' || c2.referenced_column || '%' ) select d.* , ( case when d.type in ('VIEW','MATERIALIZED VIEW') then '?' when s.name is not null then 'YES' else 'NO' end ) as column_name_in_source from ted_dependency_columns_v d , s , c where d.owner = s.owner (+) and d.name = s.name (+) and d.type = s.type (+) and d.referenced_column = s.referenced_column (+) and d.referenced_owner = c.referenced_owner and d.referenced_name = c.referenced_name and d.referenced_column = c.referenced_column order by d.owner , d.name , d.type

View

View
	Name	TED_DEPENDENCY_COLUMNS_V
	DDL	select d.u_name as owner , d.o_name as name , decode( d.o_type# , 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER' , 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE' , 8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT' , 11, 'PACKAGE BODY', 12, 'TRIGGER' , 13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY' , 28, 'JAVA SOURCE', 29, 'JAVA CLASS' , 32, 'INDEXTYPE', 33, 'OPERATOR' , 42, 'MATERIALIZED VIEW', 43, 'DIMENSION' , 46, 'RULE SET', 55, 'XML SCHEMA', 56, 'JAVA DATA' , 59, 'RULE', 62, 'EVALUATION CONTXT' , 92, 'CUBE DIMENSION', 93, 'CUBE' , 94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS' , 'UNDEFINED' ) as type , nvl2( d.po_linkname, d.po_remoteowner, d.pu_name) as referenced_owner , d.po_name as referenced_name , decode( d.po_type# , 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER' , 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE' , 8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT' , 11, 'PACKAGE BODY', 12, 'TRIGGER' , 13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY' , 28, 'JAVA SOURCE', 29, 'JAVA CLASS' , 32, 'INDEXTYPE', 33, 'OPERATOR' , 42, 'MATERIALIZED VIEW', 43, 'DIMENSION' , 46, 'RULE SET', 55, 'XML SCHEMA', 56, 'JAVA DATA' , 59, 'RULE', 62, 'EVALUATION CONTXT' , 92, 'CUBE DIMENSION', 93, 'CUBE' , 94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS' , 'UNDEFINED' ) as referenced_type , d.po_linkname as referenced_link_name , c.name as referenced_column , decode(bitand(d.d_property, 3), 2, 'REF', 'HARD') as dependency_type from ( select obj# , u_name , o_name , o_type# , pu_name , po_name , po_type# , po_remoteowner , po_linkname , d_property , colpos from sys."_CURRENT_EDITION_OBJ" o , sys.disk_and_fixed_objects po , sys.dependency$ d , sys.user$ u , sys.user$ pu where o.obj# = d.d_obj# and o.owner# = u.user# and po.obj# = d.p_obj# and po.owner# = pu.user# and d.d_attrs is not null model return updated rows partition by ( po.obj# obj# , u.name u_name , o.name o_name , o.type# o_type# , po.linkname po_linkname , pu.name pu_name , po.remoteowner po_remoteowner , po.name po_name , po.type# po_type# , d.property d_property ) dimension by (0 i) measures (0 colpos, substr(d.d_attrs,9) attrs) rules iterate (1000) until (iteration_number = 4 * length(attrs[0]) - 2) ( colpos[iteration_number+1] = case bitand ( to_number ( substr ( attrs[0] , 1 + 2*trunc((iteration_number+1)/8) , 2 ) ,'XX' ) , power(2,mod(iteration_number+1,8)) ) when 0 then null else iteration_number+1 end ) ) d , sys.col$ c where d.obj# = c.obj# and d.colpos = c.col#