Decode column type and element type in dba_coll_types table
SQL>
SQL>
SQL> prompt
SQL> prompt Collections
Collections
SQL>
SQL> column type_name format a35 heading "Collection Name"
SQL> column coll_type format a8 heading "Type"
SQL> column element_type format a35 heading "Element Type"
SQL>
SQL> select type_name,
2 decode(coll_type,'TABLE','Table','Varray') coll_type,
3 decode(elem_type_owner,null,'',elem_type_owner||'.') || elem_type_name element_type
4 from dba_coll_types
5 where rownum < 50
6 order by 1, 2
7 /
Collection Name Type Element Type
----------------------------------- -------- -----------------------------------
AQ$_DEQUEUE_HISTORY_T Table SYS.AQ$_DEQUEUE_HISTORY
AQ$_HISTORY Varray SYS.AQ$_DEQUEUE_HISTORY
AQ$_JMS_BYTES_MESSAGES Varray SYS.AQ$_JMS_BYTES_MESSAGE
AQ$_JMS_MAP_MESSAGES Varray SYS.AQ$_JMS_MAP_MESSAGE
AQ$_JMS_MESSAGES Varray SYS.AQ$_JMS_MESSAGE
AQ$_JMS_MESSAGE_PROPERTIES Varray SYS.AQ$_JMS_MESSAGE_PROPERTY
AQ$_JMS_OBJECT_MESSAGES Varray SYS.AQ$_JMS_OBJECT_MESSAGE
AQ$_JMS_STREAM_MESSAGES Varray SYS.AQ$_JMS_STREAM_MESSAGE
AQ$_JMS_TEXT_MESSAGES Varray SYS.AQ$_JMS_TEXT_MESSAGE
AQ$_JMS_USERPROPARRAY Varray SYS.AQ$_JMS_USERPROPERTY
AQ$_MIDARRAY Varray VARCHAR2
AQ$_POST_INFO_LIST Varray SYS.AQ$_POST_INFO
AQ$_RECIPIENTS Varray SYS.AQ$_AGENT
AQ$_REG_INFO_LIST Varray SYS.AQ$_REG_INFO
AQ$_SUBSCRIBERS Varray SYS.AQ$_AGENT
AQ$_SUBSCRIBER_T Table SYS.AQ$_SUBSCRIBER
CREATE_TABLE_COST_COLUMNS Varray SYS.CREATE_TABLE_COST_COLINFO
DBMSOUTPUT_LINESARRAY Varray VARCHAR2
ODCIARGDESCLIST Varray SYS.ODCIARGDESC
ODCIBFILELIST Varray BFILE
ODCICOLINFOLIST Varray SYS.ODCICOLINFO
ODCICOLINFOLIST2 Varray SYS.ODCICOLINFO
ODCIDATELIST Varray DATE
ODCIGRANULELIST Varray NUMBER
ODCINUMBERLIST Varray NUMBER
ODCIOBJECTLIST Varray SYS.ODCIOBJECT
ODCIRAWLIST Varray RAW
ODCIRIDLIST Varray VARCHAR2
ODCISECOBJTABLE Table SYS.ODCISECOBJ
ODCIVARCHAR2LIST Varray VARCHAR2
RE$ATTRIBUTE_VALUE_LIST Varray SYS.RE$ATTRIBUTE_VALUE
RE$COLUMN_VALUE_LIST Varray SYS.RE$COLUMN_VALUE
RE$NAME_ARRAY Varray VARCHAR2
RE$NV_ARRAY Varray SYS.RE$NV_NODE
RE$RULE_HIT_LIST Varray SYS.RE$RULE_HIT
RE$RULE_LIST Varray VARCHAR2
RE$TABLE_ALIAS_LIST Varray SYS.RE$TABLE_ALIAS
RE$TABLE_VALUE_LIST Varray SYS.RE$TABLE_VALUE
RE$VARIABLE_TYPE_LIST Varray SYS.RE$VARIABLE_TYPE
RE$VARIABLE_VALUE_LIST Varray SYS.RE$VARIABLE_VALUE
SYS_PLSQL_4060_32_1 Table SYS.SYS_PLSQL_4060_9_1
SYS_PLSQL_4060_DUMMY_1 Table NUMBER
SYS_PLSQL_4236_409_1 Table SYS.SYS_PLSQL_4236_401_1
SYS_PLSQL_4236_543_1 Table SYS.SYS_PLSQL_4236_525_1
SYS_PLSQL_4236_688_1 Table SYS.SYS_PLSQL_4236_592_1
SYS_PLSQL_4236_773_1 Table SYS.SYS_PLSQL_4236_728_1
SYS_PLSQL_4236_829_1 Table SYS.SYS_PLSQL_4236_802_1
SYS_PLSQL_4236_877_1 Table SYS.SYS_PLSQL_4236_837_1
SYS_PLSQL_4236_DUMMY_1 Table NUMBER
49 rows selected.
SQL>
SQL> --
Related examples in the same category