Mega Code Archive

 

Categories / Oracle PLSQL / System Tables Views

 

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