StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can find details about the functions and procedures in a package by querying the <a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/statviews_1014.htm" rel="nofollow">ALL_ARGUMENTS</a> data dictionary view, or its brethren USER_ARGUMENTS and DBA_ARGUMENTS.</p> <p>For an example I created the following package:</p> <pre><code>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; </code></pre> <p>I then ran the following query against it:</p> <pre> 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 </pre> <p>Here you can see all of the arguments to the functions and procedures in our package. Note that there is an extra entry with a null argument name for the return value for each of the two functions. Also, the procedure that has no arguments has a row with a null argument name and a zero <code>SEQUENCE</code> value.</p> <p>So, to list all functions, you could search for all entries in this view with a null argument name and a <code>SEQUENCE</code> value not equal to 0:</p> <pre> 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 </pre> <p>Listing procedures in a similar way is a little trickier:</p> <pre> 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 </pre> <p>While this approach appears to work with procedures and functions, I don't know how to list the package-scope variables, types and other things declared within a package header without parsing the package spec, as suggested by @wweicker.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload