How to get information about all types of data declared in a given package

Question

Using the information from the representations of the data dictionary, get information about all types of data declared in a given package.

NAME            |   TYPE                |   PACKAGE
----------------------------------------------------
T_ASSOCIATIVE   |   ASSOCIATIVE ARRAY   |   MY_TYPES
T_TABLE_TYPE    |   NESTED TABLE        |   MY_TYPES
T_CURSOR_TYPE   |   EREFCURSOR          |
...

The program should be issued in the form of an anonymous block.

1) desc package_name;

no variants to get certain rows, just parse: bad practive for such task

2)

select * from user_types;
select * from user_type_attrs;
select * from user_type_methods;
select * from user_procedures;
select * from user_source;

Doesn't get any result, only package type, but not types declared within this package

Answer 1

You can see PL/SQL types in the all_plsql_types view

SQL>  > desc all_plsql_types
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 OWNER                                                             NOT NULL VARCHAR2(128)
 TYPE_NAME                                                                  VARCHAR2(136)
 PACKAGE_NAME                                                      NOT NULL VARCHAR2(128)
 TYPE_OID                                                          NOT NULL RAW(16)
 TYPECODE                                                                   VARCHAR2(58)
 ATTRIBUTES                                                                 NUMBER
 CONTAINS_PLSQL                                                             VARCHAR2(3)

... or the dba_ or user_ versions if you prefer.

---

That view is only available from 12c. In 11gR2, if you PL/Scope enabled, you can extract that information from the all_identifiers view:

SQL> desc all_identifiers;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 NAME                                               VARCHAR2(30)
 SIGNATURE                                          VARCHAR2(32)
 TYPE                                               VARCHAR2(18)
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 OBJECT_TYPE                                        VARCHAR2(13)
 USAGE                                              VARCHAR2(11)
 USAGE_ID                                           NUMBER
 LINE                                               NUMBER
 COL                                                NUMBER
 USAGE_CONTEXT_ID                                   NUMBER

... or the dba_ or user_ versions if you prefer.

Quick demo:

alter session set PLSCOPE_SETTINGS='IDENTIFIERS:ALL';

create package my_types as
  type T_ASSOCIATIVE is table of number index by pls_integer;
  type T_TABLE_TYPE is table of number;
  type T_CURSOR_TYPE is ref cursor;
end my_types;
/

select name, type
from user_identifiers
where object_name = 'MY_TYPES'
and usage = 'DECLARATION'
and type != 'PACKAGE'
order by name;

NAME                           TYPE              
------------------------------ ------------------
T_ASSOCIATIVE                  INDEX TABLE       
T_CURSOR_TYPE                  REFCURSOR         
T_TABLE_TYPE                   NESTED TABLE      

You may need to recompile existing objects; either by recreating them or less intrusively with alter package:

alter session set PLSCOPE_SETTINGS='IDENTIFIERS:ALL';

alter package my_types compile;