I would like to be able to analyze large PL/SQL code base to identify internal PL/SQL subprograms called in PL/SQL application code.
I know that I can get package level dependencies with DBA_DEPENDENCIES and that PL/Scope should give subprograms level information.
But I cannot make it work with Oracle 26 Free.
Here my scenario:
SQL>
SQL> alter session set plscope_settings='identifiers:all, statements:all';
Session altered.
SQL> show parameter PLSCOPE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
plscope_settings string identifiers:all, statements:al
l
SQL>
SQL> CREATE OR REPLACE PACKAGE pcaller AS
2 -- This must be in the spec to be visible to other packages
3 FUNCTION fcaller(param bool) RETURN BOOLEAN;
4 END pcaller;
5 /
Package created.
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY pcaller AS
2 FUNCTION fcaller(param bool) RETURN BOOLEAN IS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('called');
5 DBMS_LOCK.SLEEP(1);
6 SYS.DBMS_RANDOM.SEED(2);
7 RETURN false;
8 END fcaller;
9 END pcaller;
10 /
Package body created.
SQL> show errors
No errors.
SQL> --
SQL> show errors
No errors.
SQL> --
SQL> -- list dependencies
SQL> -- from PL/SQL objects (including triggers) belonging to application schemas
SQL> -- on Oracle internal PL/SQL packages (including owner is 'PUBLIC' but excluding STANDARD package)
SQL> --
SQL> set linesize 200
SQL> --
SQL> col owner format a30
SQL> col name format a30
SQL> col referenced_owner format a30
SQL> col referenced_name format a30
SQL> col object_type format a30
SQL> select d.owner, d.name, o1.object_type, d.referenced_owner, d.referenced_name, o2.object_type
2 from dba_dependencies d
3 join dba_objects o2 on d.referenced_owner = o2.owner and d.referenced_name = o2.object_name
4 join dba_objects o1 on d.owner = o1.owner and d.name = o1.object_name
5 where o1.object_type in ('PACKAGE', 'PACKAGE BODY', 'TRIGGER', 'PROCEDURE', 'FUNCTION') and d.owner in (select username from dba_users where oracle_maintained='N')
6 and o2.object_type in ('PACKAGE', 'PACKAGE BODY','PROCEDURE','FUNCTION', 'SYNONYM')
7 and referenced_name <> 'STANDARD'
8 and d.referenced_owner in (select username from dba_users where oracle_maintained='Y'
9 union select 'PUBLIC' from dual)
10 order by d.owner, d.name, d.referenced_owner, d.referenced_name
11 ;
OWNER NAME OBJECT_TYPE REFERENCED_OWNER REFERENCED_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
TEST PCALLER PACKAGE BODY PUBLIC DBMS_LOCK SYNONYM
TEST PCALLER PACKAGE PUBLIC DBMS_LOCK SYNONYM
TEST PCALLER PACKAGE BODY PUBLIC DBMS_OUTPUT SYNONYM
TEST PCALLER PACKAGE PUBLIC DBMS_OUTPUT SYNONYM
TEST PCALLER PACKAGE SYS DBMS_RANDOM PACKAGE BODY
TEST PCALLER PACKAGE BODY SYS DBMS_RANDOM PACKAGE BODY
TEST PCALLER PACKAGE SYS DBMS_RANDOM PACKAGE
TEST PCALLER PACKAGE BODY SYS DBMS_RANDOM PACKAGE
8 rows selected.
SQL> --
SQL> --
SQL> -- try PL/SCOPE
SQL> --
SQL> alter session set plscope_settings='identifiers:all, statements:all';
Session altered.
SQL> show parameter PLSCOPE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
plscope_settings string identifiers:all, statements:al
l
SQL> alter package pcaller compile;
Package altered.
SQL> show errors
No errors.
SQL> COLUMN full_text FORMAT A50
SQL>
SQL> SELECT line,
2 col,
3 type,
4 full_text
5 FROM dba_statements
6 WHERE object_name = 'PCALLER'
7 AND object_type = 'PACKAGE'
8 ORDER BY 1;
no rows selected
SQL>
SQL> SELECT owner,
2 name,
3 type,
4 usage,
5 line,
6 object_name,
7 object_type
8 FROM dba_identifiers
9 where owner='TEST';
OWNER NAME TYPE USAGE LINE OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------ ----------- ---------- ------------------------------ ------------------------------
TEST BOOLEAN BOOLEAN DATATYPE REFERENCE 3 PCALLER PACKAGE
TEST BOOL SUBTYPE REFERENCE 3 PCALLER PACKAGE
TEST BOOLEAN BOOLEAN DATATYPE REFERENCE 2 PCALLER PACKAGE BODY
TEST BOOL SUBTYPE REFERENCE 2 PCALLER PACKAGE BODY
TEST PUT_LINE PROCEDURE CALL 4 PCALLER PACKAGE BODY
TEST PARAM FORMAL IN DECLARATION 3 PCALLER PACKAGE
TEST FCALLER FUNCTION DECLARATION 3 PCALLER PACKAGE
TEST PCALLER PACKAGE DECLARATION 1 PCALLER PACKAGE
TEST PARAM FORMAL IN DECLARATION 2 PCALLER PACKAGE BODY
TEST FCALLER FUNCTION DEFINITION 2 PCALLER PACKAGE BODY
TEST PCALLER PACKAGE DEFINITION 1 PCALLER PACKAGE BODY
11 rows selected.
I don't understand why I get only PUT_LINE and SLEEP and SEED are missing ?