How to get information on all types of data declared in a given package

Question

How can I use information from the dictionary views to get information on all types of data declared in a given package in Oracle 11g.

Answer 1

I don't think there is any dynamic view to directly get that information.

I am thinking of querying ALL_SOURCE using REGULAR EXPRESSION to find all possible DATA TYPE in the DESCRIPTION column. Put the PACKAGE NAME in the where clause as WHERE name = package_name

NOTE

Based on other answer, there seems to be a direct way, so my solution would be an alternative. As the saying goes, there are many ways of doing the same task, this is one of them.

Answer 2

Use PL/Scope ...

alter session set plscope_settings = 'IDENTIFIERS:ALL';

... and recompile the package (UTL_LOG in my case) ...

alter package utl_log compile;
alter package utl_log compile body;

... and then query the user_identifiers view ...

select name, type, object_name, object_type, line, col
from user_identifiers
where object_name = 'UTL_LOG'
    and usage = 'DECLARATION'
    and type not in ('VARIABLE','FUNCTION','FORMAL IN','FORMAL OUT','CONSTANT','PROCEDURE','FUNCTION','PACKAGE')
;

... which would (in my case) yield ...

NAME                TYPE    OBJECT_ OBJECT_ LINE COL
------------------- ------- ------- ------- ---- ---
ARR_SOME_COLLECTION VARRAY  UTL_LOG PACKAGE   19   6
REC_SOME_RECORD     RECORD  UTL_LOG PACKAGE   15   6
TYP_LOG_CODE        SUBTYPE UTL_LOG PACKAGE    8   9

Please note that PL/Scope can be used for any identifier declared/defined in any program unit, not only for data type declarations.

Answer 3

If you want to know how a package looks like run: desc PACKAGE_NAME:

SQL> desc dbms_output
PROCEDURE DISABLE
PROCEDURE ENABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 BUFFER_SIZE                    NUMBER(38)              IN     DEFAULT
PROCEDURE GET_LINE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LINE                           VARCHAR2                OUT
 STATUS                         NUMBER(38)              OUT
PROCEDURE GET_LINES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LINES                          TABLE OF VARCHAR2(32767) OUT
 NUMLINES                       NUMBER(38)              IN/OUT
PROCEDURE GET_LINES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LINES                          DBMSOUTPUT_LINESARRAY   OUT
 NUMLINES                       NUMBER(38)              IN/OUT
PROCEDURE NEW_LINE
PROCEDURE PUT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 A                              VARCHAR2                IN
PROCEDURE PUT_LINE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 A                              VARCHAR2                IN

If you want to get all dependencies see ALL_DEPENDENCIES:

SQL> ed
Wrote file afiedt.buf

  1  create or replace package t1_pkg
  2  as
  3    procedure fake_proc;
  4* end t1_pkg;
SQL> /

Package created.

SQL> ed
Wrote file afiedt.buf

  1  create or replace package body t1_pkg
  2  as
  3    procedure fake_proc
  4    as
  5      l_count number(10);
  6    begin
  7      select count(*)
  8        into l_count
  9        from user_objects;
 10    end fake_proc;
 11* end t1_pkg;
SQL> /

Package body created.

SQL> select referenced_name, referenced_type from user_dependencies where name = 'T1_PKG';

REFERENCED_NAME REFERENCED_TYPE
--------------- ------------------
STANDARD        PACKAGE
USER_OBJECTS    SYNONYM
T1_PKG          PACKAGE