Can I discover types declared in Oracle PL/SQL packages using dictionary views? I.e. 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'd like to formally discover t_cursor_typeand t_table_type. They don't seem to be listed in
SYS.ALL_TYPES
By "formally" I mean I'd like some "formal meta-data", i.e. querying USER_SOURCE won't do the trick.
Only from the 11.1 onward.
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 data about identifiers in PL/SQL source code at program-unit compilation time and makes it available in static data dictionary views. The collected data includes information about identifier types, usages (declaration, definition, reference, call, assignment) and the location of each usage 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>
Here's a solution that I've devised for the jOOQ code generator in version 3.9 to discover PL/SQL RECORD types. It only discovers those 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 details about the above technique can be found here.
I've just discovered this extremely interesting website, which lists a query that uses the dictionary views mentioned in zep's answer here. 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;
Yielding this result
PACKAGE MY_TYPES (DECLARATION)
REFCURSOR T_CURSOR_TYPE (DECLARATION)
NESTED TABLE T_TABLE_TYPE (DECLARATION)
Unfortunately, the nested table type is not resolved any further.
EDIT: From documentation found by Zep, statement only applies oracle versions prior to 11.1
The bad news for you:
Those types only exist upon execution of the package, so they would never be seen in the oracle dictionary. In other words, those types are not in the database, rather they are in the package.
The good new for you:
The package is in the data dictionary so we could run some sql to search procedures and packages that contain the keywords you care about; in this example 'TYPE':
SELECT *
FROM dba_source
WHERE type IN ('PROCEDURE','PACKAGE','PACKAGE BODY')
AND text LIKE '%TYPE%IS%';
From there define the owner you care about and the do some regex or other fancy code to pull the data you need.
Alternatively, if you want to see the types in the data dictionary you would have to create them outside the package and then reference that type from the package.
References:
It won't be available from all_types or all_objects but as an alternative you can query user_source for the name of the type, and it should show up.
Nowadays (18c+ IIRC) there are all_plsq_* views that do the job. For record types see user_plsql_types and all_plsql_type_attrs.
Try to use collection as an argument of package function/procedurę and next see into dictionary user_arguments:
select *
from user_arguments a
where a.object_name = 'MYFUNCTIONNAME'
order by a.sequence;
