Getting ORACLE programming object definitions

问题:

Let's say I have an ORACLE schema with contains a package. That package defines types, functions, procedures, etc:

CREATE PACKAGE...
  DECLARE 
  FUNCTION ...
  PROCEDURE ...
END;

Is there a query I can execute to get the definitions of those individual objects, without the wrapping package?

回答1:

If you need the procedures/functions of the package, you can use user_procedures:

Create Package package_test As
  Procedure dummy( x In Number );
  Function dummy2 Return Number;
End package_test;

Select object_name, procedure_name From user_procedures Where object_name = 'PACKAGE_TEST';

returns

PACKAGE_TEST    DUMMY
PACKAGE_TEST    DUMMY2
PACKAGE_TEST    

To get the parameters, select from user_arguments.

---

If you want the code for your packages/procedures/functions, use user_source for objects of your user, all_source for objects your user has grants for, and dba_source for all objects:

Select *
From user_source
Where name = 'YOUR_TEST'
And type = 'PROCEDURE';

If you created the procedure your_test before:

Create Procedure your_test As
Begin
  dbms_output.put_line('test');
End your_test;

it returns

YOUR_TEST  PROCEDURE  1  Procedure your_test As
YOUR_TEST  PROCEDURE  2  Begin
YOUR_TEST  PROCEDURE  3    dbms_output.put_line('test');
YOUR_TEST  PROCEDURE  4  End your_test; 

回答2:

It is possible to retrieve the signatures of individual methods from the data dictionary. The relevant view is USER/ALL/DBA_ARGUMENTS.

There are a couple of snags to watch out for. If an argument has a postition of 0 it is the return value of a function not a parameter. If the parameter is a complex type (such as a nested table or PL/SQL record) it will appear multiple times, with each layer incrementing the data_level.

The following query joins arguments with their owning procedures. I am excluding the entries in USER_PROCEDURES at the package level (that is, subprogram_id=0) and the entries in USER_ARGUMENTS for the lower levels of complex_type (that is, data_level > 0). The query also handles procedures without any parameters.

SQL> select o.object_type
  2          , case
  3                when o.object_type in ( 'PACKAGE', 'TYPE') then p.object_name
  4                else null
  5            end as object_name
  6          , case
  7                when o.object_type in ( 'PACKAGE', 'TYPE') then p.procedure_name
  8                else p.object_name
  9            end as procedure_name
 10          , case
 11               when a.position != 0 then a.argument_name
 12               when a.position = 0 then '** return value **'
 13               else null
 14            end as argument_name
 15          , a.data_type
 16  from user_arguments a
 17       , user_procedures p
 18       , user_objects o
 19  where o.object_type in ( 'PACKAGE', 'TYPE', 'FUNCTION', 'PROCEDURE')
 20  and   p.object_name = o.object_name
 21  and   p.subprogram_id != 0
 22  and   a.object_id (+) = p.object_id
 23  and   a.subprogram_id (+) = p.subprogram_id
 24  and   a.data_level(+) = 0
 25  order by o.object_type, o.object_name, p.subprogram_id, a.position
 26  /

OBJECT_TYPE         OBJECT_NAME                    PROCEDURE_NAME                 ARGUMENT_NAME          DATA_TYPE
------------------- ------------------------------ ------------------------------ ------------------------------ -------
FUNCTION                                           COMPARE_OBJECTS                ** return value **     PL/SQL BOOLEAN
FUNCTION                                           COMPARE_OBJECTS                OBJ1                   UNDEFINED
FUNCTION                                           COMPARE_OBJECTS                OBJ2                   UNDEFINED
FUNCTION                                           GET_EMPS                       ** return value **     REF CURSOR
FUNCTION                                           GET_EMPS                       P_ARGS                 UNDEFINED
FUNCTION                                           STR_TO_NUMBER_TOKENS           ** return value **     TABLE
FUNCTION                                           STR_TO_NUMBER_TOKENS           P_STRING               VARCHAR2
FUNCTION                                           STR_TO_NUMBER_TOKENS           P_SEPARATOR            VARCHAR2
PACKAGE             P23                            POP_ARRAY                      ** return value **     TABLE
PACKAGE             P23                            POP_ARRAY                      P_NO                   NUMBER
PACKAGE             P23                            INS_TABLE                      P_ARRAY                TABLE
PROCEDURE                                          CHANGE_EMP_SAL                 P_ENO                  NUMBER
PROCEDURE                                          CHANGE_EMP_SAL                 P_NEW_SAL              NUMBER
PROCEDURE                                          PRINTE
TYPE                NEW_EMP                        EQUALS                         ** return value **     RAW
TYPE                NEW_EMP                        EQUALS                         SELF                   OBJECT

22 rows selected.

SQL>

I could have selected other useful information from USER_ARGUMENTS such as IN_OUT or whether the parameter is DEFAULTED, but I figured it scrolled enough already.