Retrieving ORACLE Object Descriptions

Question

Say I have an ORACLE schema containing a package. This package defines types, functions, procedures, etc.:

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

Can I run the query to get definitions for these individual objects without a package?

Answer 1

If you need package procedures / functions, 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, process_name From user procedures Where object_name = 'PACKAGE_TEST';

returns

 PACKAGE_TEST DUMMY PACKAGE_TEST DUMMY2 PACKAGE_TEST 

To get the parameters, select user_arguments .

---

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

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

If you created your_test procedure before:

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

he 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; 

Answer 2

You can get the signatures of individual methods from the data dictionary. The corresponding view is USER / ALL / DBA_ARGUMENTS.

There are several snags that you need to follow. If the argument has a postulation of 0, this return value of the function is not a parameter. If this parameter is complex (for example, a nested table or a PL / SQL record), it will be displayed several times, with each level increasing data_level .

The following query combines arguments with their ownership procedures. I exclude entries in USER_PROCEDURES at the package level (i.e. subprogram_id=0 ) and entries in USER_ARGUMENTS for lower levels of complex_type (i.e. data_level > 0 ). The request also processes the 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 select other useful information from USER_ARGUMENTS, for example IN_OUT, or the DEFAULTED parameter, but I decided that it is already scrollable enough.