Getting the PL / SQL Procedure Schema

Question

I need to get PL / SQL procedure parameter definitions.

In MS SQL we use Information_schema.Parameters ; What is the partner (if any) in Oracle?

Answer 1

Most (if not all) of the same data can be accessed in Oracle from the ALL_ARGUMENTS dictionary table . ALL_ARGUMENTS shows the arguments for all the procedures that you have to resolve. USER_ARGUMENTS shows the arguments for all of your procedures. And DBA_ARGUMENTS shows you the arguments for all the procedures that exist in the database, but you need additional privileges to access DBA_* .

Answer 2

Most information about stored procedure parameters can be found in ALL_ARGUMENTS and similarly in USER_ARGUMENTS and DBA_ARGUMENTS

Here is an example using USER_ARGUMENTS

 CREATE OR REPLACE PROCEDURE my_proc (p_number IN NUMBER, p_varchar IN OUT VARCHAR2 , p_clob IN OUT NOCOPY CLOB, p_timestamp OUT TIMESTAMP ) IS BEGIN NULL; END; / CREATE OR REPLACE FUNCTION my_func (p_date IN DATE, p_varchar IN VARCHAR2) RETURN BOOLEAN IS BEGIN return TRUE; END; / SELECT package_name,object_name, argument_name, IN_OUT , pls_type ,position FROM user_arguments WHERE object_name IN ('MY_PROC','MY_FUNC') ORDER BY package_name, object_name, position; 

which gives the result ...

 Procedure created. Function created. PACKAGE_NAME OBJECT_NAME ARGUMENT_NAME IN_OUT PLS_TYPE POSITION --------------------- ------------------------------ ------------------------- --------- -------------------- ---------- MY_FUNC OUT BOOLEAN 0 MY_FUNC P_DATE IN DATE 1 MY_FUNC P_VARCHAR IN VARCHAR2 2 MY_PROC P_NUMBER IN NUMBER 1 MY_PROC P_VARCHAR IN/OUT VARCHAR2 2 MY_PROC P_CLOB IN/OUT CLOB 3 MY_PROC P_TIMESTAMP OUT TIMESTAMP 4 7 rows selected. 

As you can see, it has the most useful information .. but the NOCOPY hint is not displayed. ARGUMENT_NAME, which is null, is the "return value" of the function

In version ALL_ and DBA_, an additional OWNER column will be added.

Additional information about the stored procedure itself can be found in ALL_PROCEDURES , ALL_PLSQL_OBJECT_SETTINGS and ALL_OBJECTS depending on what level of detail you are looking for.