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 ?
Interesting, I can't replicate your PUT_LINE output - see below but there's a couple of things at play here:
SQL>
SQL> ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';
Session altered.
SQL>
SQL> create or replace
2 procedure scott.other_schema_proc is
3 x int;
4 begin
5 x := 0;
6 end;
7 /
Procedure created.
SQL> CREATE OR REPLACE package pack1 as
2 PROCEDURE proc1;
3 PROCEDURE proc2;
4 END;
5 /
Package created.
SQL>
SQL> CREATE OR REPLACE package BODY pack1 as
2 PROCEDURE proc1 IS
3 BEGIN
4 NULL;
5 END;
6 PROCEDURE proc2 IS
7 BEGIN
8 proc1;
9 dbms_random.seed(1);
10 dbms_session.sleep(1);
11 END;
12 PROCEDURE proc3 IS
13 BEGIN
14 proc1;
15 proc2;
16 dbms_output.put_line('x');
17 scott.other_schema_proc;
18 END;
19 END;
20 /
Package body created.
SQL>
SQL> col IDENTIFIER_USAGE_CONTEXTS format a120
SQL>
SQL> WITH v AS (
2 SELECT Line,
3 Col,
4 INITCAP(NAME) Name,
5 LOWER(TYPE) Type,
6 LOWER(USAGE) Usage,
7 USAGE_ID,
8 USAGE_CONTEXT_ID, line lineno
9 FROM USER_IDENTIFIERS
10 WHERE Object_Name = 'PACK1'
11 AND Object_Type = 'PACKAGE BODY'
12 )
13 SELECT lineno, RPAD(LPAD(' ', 2*(Level-1)) ||
14 Name, 40, '.')||' '||
15 RPAD(Type, 20)||
16 RPAD(Usage, 20)
17 IDENTIFIER_USAGE_CONTEXTS
18 FROM v
19 START WITH USAGE_CONTEXT_ID = 0
20 CONNECT BY PRIOR USAGE_ID = USAGE_CONTEXT_ID
21 ORDER SIBLINGS BY Line, Col
22 /
LINENO IDENTIFIER_USAGE_CONTEXTS
---------- ------------------------------------------------------------------------------------------------------------------------
1 Pack1................................... package definition
2 Proc1................................. procedure definition
6 Proc2................................. procedure definition
8 Proc1............................... procedure call
12 Proc3................................. procedure declaration
12 Proc3............................... procedure definition
14 Proc1............................. procedure call
15 Proc2............................. procedure call
17 Other_Schema_Proc................. procedure call
9 rows selected.
SQL>
1) We're not going to pick up anything that is wrapped (which makes sense because the code is 'inacessible'
2) Even if it was not wrapped, you'd need to compile the referenced objects (DBMS_OUTPUT etc) with the appropriate pl/scope settings to expose them as well.
So in a nutshell - you're unlikely to see details on SYS objects