Oracle Stored Procedure List Parameters

Question

I'm developing a .NET front end that interacts with an Oracle database. I have figured out how to get a list of stored procedures to execute, but I don't know how to get a list of parameters that belong to the stored procedure. I want to be able to show a list of all the parameters that are both input and output parameters for the stored procedure.

I have tried using the DBA_SOURCE, DBA_PROCEDURES, ALL_DEPENDENCIES, but I haven't seen anything that shows the parameters that belongs to the specified stored procedure.

Any ideas?

As others have said - you can find most of the metadata in the _ARGUMENTS views, with the caveat that you've got more digging to do if the parameter is a pl/sql record type or what structure might be expected of a sys_refcursor variable being passed into a stored procedure. — Michael Broughton, Oct 27 '15 at 19:19

score 2 · Accepted Answer · edited Nov 04 '20 at 16:09

I believe that both responses I received are correct, but I ended up finding a different query which gives me exactly what I'm looking for:

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

This has been working for me so far and pulls all the OracleParameter information that I want as well.

score 1 · Answer 2 · answered Oct 27 '15 at 16:43

1

You find parameter metadata in DBA/ALL/USER_ARGUMENTS view.

answered Oct 27 '15 at 16:43

Husqvik

5,669
1
19
29

score 1 · Answer 3 · answered Oct 27 '15 at 17:32

This is the query that we use, 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

Oracle Stored Procedure List Parameters

3 Answers3