Retrieving PL/SQL procedure schema
Most (if not all) of the same data can be accessed in Oracle from the ALL_ARGUMENTS data dictionary table. ALL_ARGUMENTS
shows you the arguments for all the procedures that you have permission to execute. USER_ARGUMENTS
shows you the arguments for all the procedures that you own. And DBA_ARGUMENTS
shows you the arguments for all the procedures that exist in the database but you need additional privileges to access the DBA_*
views.
Most of the information about stored procedure parameters can be found in ALL_ARGUMENTS and similarly in USER_ARGUMENTS
and DBA_ARGUMENTS
Here is a quick sample using USER_ARGUMENTS
CREATE OR REPLACE PROCEDURE my_proc(p_number IN NUMBER,p_varchar IN OUT VARCHAR2 ,p_clob IN OUT NOCOPY CLOB,p_timestamp OUT TIMESTAMP)ISBEGIN NULL;END;/CREATE OR REPLACE FUNCTION my_func(p_date IN DATE,p_varchar IN VARCHAR2)RETURN BOOLEANISBEGINreturn TRUE;END;/SELECT package_name,object_name, argument_name, IN_OUT , pls_type ,positionFROM user_argumentsWHERE object_name IN ('MY_PROC','MY_FUNC')ORDER BY package_name, object_name, position;
which gives the output of..
Procedure created.Function created. PACKAGE_NAME OBJECT_NAME ARGUMENT_NAME IN_OUT PLS_TYPE POSITION--------------------- ------------------------------ ------------------------- --------- -------------------- ---------- MY_FUNC OUT BOOLEAN 0 MY_FUNC P_DATE IN DATE 1 MY_FUNC P_VARCHAR IN VARCHAR2 2 MY_PROC P_NUMBER IN NUMBER 1 MY_PROC P_VARCHAR IN/OUT VARCHAR2 2 MY_PROC P_CLOB IN/OUT CLOB 3 MY_PROC P_TIMESTAMP OUT TIMESTAMP 47 rows selected.
As you can see it has most useful information.. but does not show the NOCOPY hint.the ARGUMENT_NAME that is null is the 'return value' of the function
the ALL_ and DBA_ version will have an additional OWNER column.
additinal information about the stored procedure itself can be found in ALL_PROCEDURES , ALL_PLSQL_OBJECT_SETTINGS and ALL_OBJECTS depending on what level of detail you are looking for.