Listing procedure parameters in PL/SQL

Question

I am able to retrieve a list of all procedures by querying dba_procedures. However I am only able to extract the procedure names. But I also need their parameters.

Now, I know that I can access the "text source of the stored objects accessible to the current user" by querying the all_source table.

However is there any other way to retrieve the parameters of a procedure?

Answer 1

You can query the parameters using SYS.ALL_ARGUMENTS Table:

SELECT * FROM SYS.ALL_ARGUMENTS WHERE 
  PACKAGE_NAME = '<null_or_package_name>' AND 
  OBJECT_NAME = '<procedure_name>';

Answer 2

From SQL*Plus or SQL Developer, you can use the DESCribe procedure_name command:

Oracle> desc dbms_metadata
FUNCTION ADD_TRANSFORM RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 HANDLE                         NUMBER                  IN
 NAME                           VARCHAR2                IN
 ENCODING                       VARCHAR2                IN     DEFAULT
 OBJECT_TYPE                    VARCHAR2                IN     DEFAULT
FUNCTION CHECK_MATCH_TEMPLATE RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 POBJNO                         NUMBER                  IN
 SPCNT                          NUMBER                  IN
...