How to find packages where table name is used more than twice

Question

I've a task to list down package names for an owner where the same table names are used more than twice or 5 times in some cases.

I came up with following query, however dba_dependencies doesn't give the result I want.

select type,REFERENCED_NAME from dba_dependencies where REFERENCED_NAME in
 (select 
  REFERENCED_NAME
 from 
   dba_dependencies
 where 
    owner = 'SITTEST' and REFERENCED_TYPE='TABLE'
   and type='PACKAGE'
 group by REFERENCED_NAME having count(*)>5);

I tried using all_source and dba_source but no dice.

Answer 1

An odd requirement! You would have to search the source code of the packages since DBA_DEPENDENCIES will only contain a single row per dependency.

Something like this should do it (I have used USER% views for simplicity):

select t.table_name, p.object_name, count(*)
from user_tables t
cross join user_objects p
join user_source s on s.name = p.object_name
         and s.type = 'PACKAGE BODY'
         and upper(s.text) like '%' || upper(t.table_name) || '%'
where p.object_type = 'PACKAGE BODY'
group by t.table_name, p.object_name
having count(*) > 5;

But - that will count false positives since it is just looking for the text, which may not really be a usage.