Discovering PL / SQL Package Types Using Oracle Dictionary Views

Question

Can I detect types declared in Oracle PL / SQL packages using dictionary views? That is, when I declare this:

CREATE OR REPLACE PACKAGE my_types AS TYPE t_cursor_type IS REF CURSOR; TYPE t_table_type IS TABLE OF some_table%rowtype; END my_types; 

I would like to officially open t_cursor_type and t_table_type . They do not seem to be listed in

 SYS.ALL_TYPES 

By "formally" I mean that I need some "formal metadata", that is, the USER_SOURCE request USER_SOURCE not do the trick.

Answer 1

A complete solution that works without any special compilation flags

Here is a solution I developed for the jOOQ code generator in version 3.9 to detect PL / SQL RECORD types. It only detects the types that are actually referenced:

 SELECT x.type_owner, x.type_name, x.type_subname, a.* FROM all_arguments a JOIN ( SELECT type_owner, type_name, type_subname, MIN(owner ) KEEP (DENSE_RANK FIRST ORDER BY owner, package_name, subprogram_id, sequence) owner, MIN(package_name ) KEEP (DENSE_RANK FIRST ORDER BY owner, package_name, subprogram_id, sequence) package_name, MIN(subprogram_id) KEEP (DENSE_RANK FIRST ORDER BY owner, package_name, subprogram_id, sequence) subprogram_id, MIN(sequence ) KEEP (DENSE_RANK FIRST ORDER BY owner, package_name, subprogram_id, sequence) sequence, MIN(next_sibling ) KEEP (DENSE_RANK FIRST ORDER BY owner, package_name, subprogram_id, sequence) next_sibling, MIN(data_level ) KEEP (DENSE_RANK FIRST ORDER BY owner, package_name, subprogram_id, sequence) data_level FROM ( SELECT LEAD(sequence, 1, sequence) OVER ( PARTITION BY owner, package_name, subprogram_id, data_level ORDER BY sequence ) next_sibling, a.type_owner, a.type_name, a.type_subname, a.owner, a.package_name, a.subprogram_id, a.sequence, a.data_level, a.data_type FROM all_arguments a ) a WHERE data_type = 'PL/SQL RECORD' GROUP BY type_owner, type_name, type_subname ) x ON (a.owner, a.package_name, a.subprogram_id) = ((x.owner, x.package_name, x.subprogram_id)) AND a.sequence BETWEEN x.sequence AND x.next_sibling AND a.data_level = x.data_level + 1 ORDER BY x.type_owner, x.type_name, x.type_subname, a.sequence ; 

More on this above can be found here .

A relatively simple (but incomplete) solution, depending on a special compilation flag

I just discovered this extremely interesting website that lists a query that uses vocabulary representations here referred to in zep. . Using the package from the question, use this query:

 WITH plscope_hierarchy AS (SELECT line , col , name , TYPE , usage , usage_id , usage_context_id FROM all_identifiers WHERE owner = USER AND object_name = 'MY_TYPES' AND object_type = 'PACKAGE') SELECT LPAD (' ', 3 * (LEVEL - 1)) || TYPE || ' ' || name || ' (' || usage || ')' identifier_hierarchy FROM plscope_hierarchy START WITH usage_context_id = 0 CONNECT BY PRIOR usage_id = usage_context_id ORDER SIBLINGS BY line, col; 

Satisfying this result

 PACKAGE MY_TYPES (DECLARATION) REFCURSOR T_CURSOR_TYPE (DECLARATION) NESTED TABLE T_TABLE_TYPE (DECLARATION) 

Unfortunately, the type of the nested table is no longer allowed.

Answer 2

Only from 11.1 onwards.

From the manual:

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17125/adfns_plscope.htm#ADFNS02204

http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams189.htm#REFRN10271

PL / Scope is a compiler-driven tool that collects identifier data in PL / SQL source code during compilation of program modules and makes it available in a static data dictionary view . The data collected includes information on the types of identifiers, customs (declaration, definition, reference, call, destination) and the location of each use in the source code.

DEMO

 CREATE OR REPLACE PACKAGE my_types AS TYPE t_cursor_type IS REF CURSOR; TYPE t_table_type IS TABLE OF employees%rowtype; type t_associative is table number index by varchar2(20); END my_types; alter package my_types compile plscope_settings='IDENTIFIERS:ALL' reuse settings; select * from user_identifiers ui where ui.object_type = 'PACKAGE' and ui.usage = 'DECLARATION' and ui.usage_context_id = '1'; NAME SIGNATURE TYPE OBJECT_NAME OBJECT_TYPE USAGE USAGE_ID LINE COL USAGE_CONTEXT_ID ------------------------------ -------------------------------- ------------------ ------------------------------ ------------- ----------- ---------- ---------- ---------- ---------------- T_ASSOCIATIVE 9A18FE6BCB72110F39CED9E08B932ECB ASSOCIATIVE ARRAY MY_TYPES PACKAGE DECLARATION 4 4 8 1 T_TABLE_TYPE 77067FE9732B492C166D38221DC3DF37 NESTED TABLE MY_TYPES PACKAGE DECLARATION 3 3 8 1 T_CURSOR_TYPE EDEC9260784B7721BC3F3DAB293F23DD REFCURSOR MY_TYPES PACKAGE DECLARATION 2 2 8 1 zep@dev > 

Answer 3

EDIT: from the documentation found by Zep, the instruction applies only to oracle versions prior to 11.1

Bad news for you:

These types exist only when the package is executed, so they will never be seen in the oracle dictionary. In other words, these types are not in the database; rather, they are in the package.

Good new for you:

The package is in the data dictionary, so we can run several sql to search for procedures and packages containing keywords that you care about; in this example "TYPE":

 SELECT * FROM dba_source WHERE type IN ('PROCEDURE','PACKAGE','PACKAGE BODY') AND text LIKE '%TYPE%IS%'; 

Identify the owner you care about and do some regular expression or some other fancy code to pull the data you need.

Alternatively, if you want to see types in a data dictionary, you will have to create them outside the package and then reference that type from the package.

Literature:

Oracle® 11g Database Reference Issue 1 (11.1) - ALL_TYPES

Answer 4

It will not be accessible from all_types or all_objects , but as an alternative, you can request user_source for the type name and it should appear.

Answer 5

Try using the collection as an argument to the package / procedure function and then in the user_arguments dictionary:

 select * from user_arguments a where a.object_name = 'MYFUNCTIONNAME' order by a.sequence;