Oracle PL/SQL/System Tables Views/dba coll types

Decode column type and element type in dba_coll_types table

   <source lang="sql">

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> --

</source>