Retrieving PL/SQL procedure schema

Question

I need to get the parameter definitions of a PL/SQL procedure.

On MS SQL, we use Information_schema.Parameters; what is the counterpart ( if any ) in Oracle?

Answer 1

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

Answer 2

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

Here is a quick sample 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 output of..

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 most useful information.. but does not show the NOCOPY hint. the ARGUMENT_NAME that is null is the 'return value' of the function

the ALL_ and DBA_ version will have an additional OWNER column.

additinal 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.