Thursday, 8 September 2011

Is there a system view in Oracle that describes the parameters for stored procedures

Question:

Is there a system view in Oracle that describes the parameters to stored procedures?
dba_procedure_parameters?
dba_proc_parms?
dba_function_args?
dba_package_proc_args?
dba_obj_columns?
I know how to get a listing of packages and procedures using dba_objects and dba_procedures, but what about getting a view down to the parameter level?

Answer:

Howard, ALL_ARGUMENTS and DBMS_DESCRIBE.DESCRIBE_PROCEDURE will give you most of the information you need.
ALL_ARGUMENTS is packed full of useful information about arguments, but it is also a very tricky data dictionary view to work with and understand. See my Codecheck Series for lots more explanation.
DBMS_DESCRIBE.DESCRIBE_PROCEDURE retrieves lots of the same information as ALL_ARGUMENTS into a set of collections. Check out the psdesc package that is on the download page for a utility to help you use and test this program.
By the way, you did an admirable job of guessing at the data dictionary view. May I suggest that in the future you run something like this:
SELECT object_name
FROM all_objects
WHERE owner = 'SYS' AND object_name LIKE '%ARGUMENT%'
OR object_name LIKE '%PARAMETER%';
or this:
SELECT view_name
FROM all_views
WHERE owner = 'SYS' AND view_name LIKE '%ARGUMENT%'
OR view_name LIKE '%PARAMETER%';
These queries will give you lots more than ALL_ARGUMENTS (such as ALL_JAVA_ARGUMENTS...), but they will certainly give you a nice starting point for your research.

No comments:

Post a Comment