Oracle Stored Procedure List Options

Question

I am developing a .NET interface that interacts with an Oracle database. I figured out how to get a list of stored procedures to execute, but I don't know how to get a list of parameters related to a stored procedure. I want to show a list of all parameters that are input and output parameters of a stored procedure.

I tried using DBA_SOURCE, DBA_PROCEDURES, ALL_DEPENDENCIES, but I did not see anything that shows the parameters related to the specified stored procedure.

Any ideas?

Answer 1

, , , , , , :

    SELECT 
         ARGUMENT_NAME
         , PLS_TYPE
         , DEFAULT_VALUE
    FROM 
         USER_ARGUMENTS
    WHERE
         OBJECT_NAME = '<my_stored_proc>'

OracleParameter, .

Answer 2

DBA/ALL/USER_ARGUMENTS.

Answer 3

This is the query we are using, more or less:

SELECT *
FROM 
  ALL_ARGUMENTS
WHERE
  DATA_TYPE IS NOT NULL

  -- This check removes package procedure arguments that don't really
  -- seem to mean anything
AND
  DATA_LEVEL = 0 

  -- Use this predicate to remove entries for the return value of functions
AND
  POSITION > 0
ORDER BY
  OWNER,
  PACKAGE_NAME,
  OBJECT_NAME,
  OBJECT_ID,
  OVERLOAD,
  POSITION