1

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.

user1261129
  • 227
  • 1
  • 3
  • 7

3 Answers3

4

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.

peter.hrasko.sk
  • 4,043
  • 2
  • 19
  • 34
  • I never tried it. Nice. My solution seems to be more of a work, perhaps an alternative. There are so many ways of doing same thing. – Lalit Kumar B Oct 09 '14 at 14:22
0

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
neshkeev
  • 6,280
  • 3
  • 26
  • 47
  • But how does it lists all the data types inside the package? OP wants to get an output of the data types declared in a package. – Lalit Kumar B Oct 09 '14 at 13:45
0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • Nice. Then my solution becomes an alternative, rather more of work ;-) – Lalit Kumar B Oct 09 '14 at 13:54
  • You like it? Then click on the "upvote". :-) We're all just a bunch of reputation-junkies. ;-) – peter.hrasko.sk Oct 09 '14 at 14:10
  • I already did. Let's see if OP and others like my alternate solution. – Lalit Kumar B Oct 09 '14 at 14:20
  • 1
    Unless there is a special case, regular expressions are unable to parse PL/SQL. Parsing PL/SQL reminds me of [this famous question](http://stackoverflow.com/questions/1732348/regex-match-open-tags-except-xhtml-self-contained-tags). – Jon Heller Oct 10 '14 at 00:38
  • Well, that question is about reading html tags. In this case, the datatypes are in plain text in the description column. Though I am not a huge fan of regex as they are high resource consuming tool, but in this case it will be simpler to write. – Lalit Kumar B Oct 10 '14 at 04:41