CODEWITHSUNDEEP
oracle-databaseplsqlplsqldeveloper
JB2
JB2

Reputation: 1607

Listing procedure parameters in PL/SQL

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?

Upvotes: 3

Views: 2986

Answers (2)

Mark Stewart
Mark Stewart

Reputation: 2098

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

Upvotes: 2

road242
road242

Reputation: 2532

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>';

Upvotes: 9

Related Questions