List of procedures in a package? Easy:
select * from user_identifiers
where object_type like 'PACKAGE%'
and usage in ('DECLARATION', 'DEFINITION')
and type in ('FUNCTION', 'PROCEDURE');
List of tables in a package? Easy:
select distinct referenced_name
from user_dependencies
where referenced_type = 'TABLE'
and type like 'PACKAGE%'
(assuming there's no dynamical SQL...)
List of tables within each procedure in each package? Ummmm.....
The problem is, packaged procedures/functions aren't objects in their own right. So they don't have their own entry in *_dependencies. That's just at the package level.
So you have to kludge together a query that:
- Finds the tables from user_dependencies/user_tables
- Joins this to user_source where the upper(text) like '%' || table_name || '%'
- Find use the line declaration/definitions of procedures from user_identifiers to calculate the start/end line of these
- Return the user_source lines between these start and ends:
drop table t2 purge;
drop table t1 purge;
create table t1 (
x int
);
create table t2 (
x int
);
create or replace package pkg as
procedure p;
function f return t2.x%type;
end pkg;
/
create or replace package body pkg as
procedure p as
begin
for c in (select * from t1) loop
null;
end loop;
end p;
function f return t2.x%type as
retval t2.x%type;
begin
select x into retval from t2
where rownum = 1;
return retval ;
end f;
end pkg;
/
select pname, tab, type, text from (
select ui.name pname, ud.table_name tab, us.type,
ui.st, ui.en, us.line, us.text,
max(line) over (partition by us.name, us.type) mx_line
from user_source us
join user_tables ud
on upper(us.text) like '%' || table_name || '%'
join (select name, object_type, object_name,
line st, lead(line) over (partition by object_type order by line)-1 en
from user_identifiers
where type in ('FUNCTION', 'PROCEDURE')
and usage in ('DECLARATION', 'DEFINITION')
and object_type like 'PACKAGE%') ui
on ui.object_name = us.name
and ui.object_type = us.type
where us.name = 'PKG'
)
where line between st and nvl(en, mx_line);
PNAME TAB TYPE TEXT
F T2 PACKAGE function f return t2.x%type;
P T1 PACKAGE BODY for c in (select * from t1) loop
F T2 PACKAGE BODY select x into retval from t2
F T2 PACKAGE BODY retval t2.x%type;
F T2 PACKAGE BODY function f return t2.x%type as
Of course, there are a few gotchas with this:
- It doesn't pick up package level variables
- The table name might be in the code but not as a table reference (e.g. a comment)
- Nested procedures break it
create or replace package body pkg as
global_v t2.x%type;
procedure p as
begin
for c in (select * from t1) loop
null;
end loop;
end p;
function f return t2.x%type as
retval t2.x%type;
procedure nest is
begin
null;
end nest;
begin
/* t1 comment */
select x into retval from t2
where rownum = 1;
return retval ;
end f;
end pkg;
/
select pname, tab, type, text from (
select ui.name pname, ud.table_name tab, us.type,
ui.st, ui.en, us.line, us.text,
max(line) over (partition by us.name, us.type) mx_line
from user_source us
join user_tables ud
on upper(us.text) like '%' || table_name || '%'
join (select name, object_type, object_name,
line st, lead(line) over (partition by object_type order by line)-1 en
from user_identifiers
where type in ('FUNCTION', 'PROCEDURE')
and usage in ('DECLARATION', 'DEFINITION')
and object_type like 'PACKAGE%') ui
on ui.object_name = us.name
and ui.object_type = us.type
where us.name = 'PKG'
)
where line between st and nvl(en, mx_line);
PNAME TAB TYPE TEXT
F T2 PACKAGE function f return t2.x%type;
P T1 PACKAGE BODY for c in (select * from t1) loop
F T2 PACKAGE BODY retval t2.x%type;
F T2 PACKAGE BODY function f return t2.x%type as
NEST T2 PACKAGE BODY select x into retval from t2
NEST T1 PACKAGE BODY /* t1 comment */
So use with care...