Discover PL/SQL package-level types using Oracle dictionary views

Question

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.

Answer 1

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> 

Answer 2

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:

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

Answer 3

Complex solution that works without any special compilation flags

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.

Relatively easy (but incomplete) solution that depends on a special compilation flag

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.