Package Announcement Content

Question

How can I see the contents of the package aria description and specific types? (pl sql)

For example, in package p1, I have 3 functions f1, f2, f3 ; 2 procedures p1, p2 and 2 variables v1, v2 .

I need a list with two columns: one for the name ( f1, f2 , etc.) and one for the type (function, procedure, variable, etc.).

Answer 1

Information about the functions and procedures in the package can be found by querying the ALL_ARGUMENTS data dictionary or its counterparts USER_ARGUMENTS and DBA_ARGUMENTS.

As an example, I created the following package:

 CREATE OR REPLACE PACKAGE demo AS PROCEDURE p_none; PROCEDURE p_two(a INTEGER, b INTEGER); FUNCTION f_none RETURN INTEGER; FUNCTION f_three(c INTEGER, q INTEGER, z INTEGER) RETURN INTEGER; END; 

Then I requested the following query against it:

  SQL> select object_name, argument_name, sequence, in_out
   2 from all_arguments
   3 where package_name = 'DEMO'
   4 order by object_name, sequence;

 OBJECT_NAME ARGUMENT_NAME SEQUENCE IN_OUT
 ------------------------------ -------------------- ---------- ---------- ---------
 F_NONE 1 OUT
 F_THREE 1 OUT
 F_THREE C 2 IN
 F_THREE Q 3 IN
 F_THREE Z 4 IN
 P_NONE 0 IN
 P_TWO A 1 IN
 P_TWO B 2 IN

Here you can see all the arguments of functions and procedures in our package. Note that there is an additional entry with a null argument name for the return value for each of the two functions. In addition, a procedure that has no arguments has a string with the name of the null argument and the zero value of SEQUENCE .

So, to list all the functions, you can search all the records in this view with the name of the null argument and the value of SEQUENCE not equal to 0:

  SQL> select distinct object_name
   2 from all_arguments
   3 where package_name = 'DEMO'
   4 and argument_name is null
   5 and sequence! = 0;

 OBJECT_NAME
 ------------------------------
 F_three
 F_none

Listing procedures in a similar way is a bit more complicated:

  SQL> select distinct object_name
   2 from all_arguments a1
   3 where package_name = 'DEMO'
   4 and (sequence = 0
   5 or not exists (select 0
   6 from all_arguments a2
   7 where a2.package_name = 'DEMO'
   8 and a2.object_name = a1.object_name
   9 and a2.argument_name is null));

 OBJECT_NAME
 ------------------------------
 P_TWO
 P_NONE

Although this approach seems to work with procedures and functions, I don't know how to list the variables, types, and other package objects declared in the package header indiscriminately of the package specification, as suggested by @wweicker.

Answer 2

This is not a trivial task! You will have to analyze the package specification by going line by line, i.e.

  select * from all_source where owner = '<the package owner>' and name = '<the package name>' and type = 'PACKAGE' order by line;