VIEW: SYS.ALL_COLL_TYPES

Source

View Text - Preformatted

select u.name, o.name, co.name, c.upper_bound,
       decode(bitand(c.properties, 32768), 32768, 'REF',
              decode(bitand(c.properties, 16384), 16384, 'POINTER')),
       nvl2(c.synobj#, (select u.name from user$ u, "_CURRENT_EDITION_OBJ" o
            where o.owner#=u.user# and o.obj#=c.synobj#),
            decode(bitand(et.properties, 64), 64, null, eu.name)),
       nvl2(c.synobj#, (select o.name from "_CURRENT_EDITION_OBJ" o where o.obj#=c.synobj#),
            decode(et.typecode,
                   9, decode(c.charsetform, 2, 'NVARCHAR2', eo.name),
                   96, decode(c.charsetform, 2, 'NCHAR', eo.name),
                   112, decode(c.charsetform, 2, 'NCLOB', eo.name),
                   eo.name)),
       c.length, c.precision, c.scale,
       decode(c.charsetform, 1, 'CHAR_CS',
                             2, 'NCHAR_CS',
                             3, NLS_CHARSET_NAME(c.charsetid),
                             4, 'ARG:'||c.charsetid),
       decode(bitand(c.properties, 131072), 131072, 'FIXED',
              decode(bitand(c.properties, 262144), 262144, 'VARYING')),
       decode(bitand(c.properties, 65536), 65536, 'NO', 'YES'),
       decode(bitand(c.properties, 4096), 4096, 'C', 'B')
from sys.user$ u, sys."_CURRENT_EDITION_OBJ" o, sys.collection$ c, sys."_CURRENT_EDITION_OBJ" co,
     sys."_CURRENT_EDITION_OBJ" eo, sys.user$ eu, sys.type$ et
where o.owner# = u.user#
  and o.type# <> 10 -- must not be invalid
  and o.oid$ = c.toid
  and o.subname IS NULL -- only the most recent version
  and c.coll_toid = co.oid$
  and c.elem_toid = eo.oid$
  and eo.owner# = eu.user#
  and c.elem_toid = et.tvoid
  and c.package_obj# IS NULL -- filter out plsql types
  and (o.owner# = userenv('SCHEMAID')
       or
       o.obj# in (select oa.obj#
                  from sys.objauth$ oa
                  where grantee# in (select kzsrorol
                                     from x$kzsro))
       or /* user has system privileges */
       exists (select null from v$enabledprivs
               where priv_number in (-184 /* EXECUTE ANY TYPE */,
                                     -181 /* CREATE ANY TYPE */)))

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, CO.NAME
, C.UPPER_BOUND
, DECODE(BITAND(C.PROPERTIES
, 32768)
, 32768
, 'REF'
, DECODE(BITAND(C.PROPERTIES
, 16384)
, 16384
, 'POINTER'))
, NVL2(C.SYNOBJ#
, (SELECT U.NAME
FROM USER$ U
, "_CURRENT_EDITION_OBJ" O
WHERE O.OWNER#=U.USER#
AND O.OBJ#=C.SYNOBJ#)
, DECODE(BITAND(ET.PROPERTIES
, 64)
, 64
, NULL
, EU.NAME))
, NVL2(C.SYNOBJ#
, (SELECT O.NAME
FROM "_CURRENT_EDITION_OBJ" O
WHERE O.OBJ#=C.SYNOBJ#)
, DECODE(ET.TYPECODE
, 9
, DECODE(C.CHARSETFORM
, 2
, 'NVARCHAR2'
, EO.NAME)
, 96
, DECODE(C.CHARSETFORM
, 2
, 'NCHAR'
, EO.NAME)
, 112
, DECODE(C.CHARSETFORM
, 2
, 'NCLOB'
, EO.NAME)
, EO.NAME))
, C.LENGTH
, C.PRECISION
, C.SCALE
, DECODE(C.CHARSETFORM
, 1
, 'CHAR_CS'
, 2
, 'NCHAR_CS'
, 3
, NLS_CHARSET_NAME(C.CHARSETID)
, 4
, 'ARG:'||C.CHARSETID)
, DECODE(BITAND(C.PROPERTIES
, 131072)
, 131072
, 'FIXED'
, DECODE(BITAND(C.PROPERTIES
, 262144)
, 262144
, 'VARYING'))
, DECODE(BITAND(C.PROPERTIES
, 65536)
, 65536
, 'NO'
, 'YES')
, DECODE(BITAND(C.PROPERTIES
, 4096)
, 4096
, 'C'
, 'B') FROM SYS.USER$ U
, SYS."_CURRENT_EDITION_OBJ" O
, SYS.COLLECTION$ C
, SYS."_CURRENT_EDITION_OBJ" CO
, SYS."_CURRENT_EDITION_OBJ" EO
, SYS.USER$ EU
, SYS.TYPE$ ET WHERE O.OWNER# = U.USER#
AND O.TYPE# <> 10 -- MUST NOT BE INVALID
AND O.OID$ = C.TOID
AND O.SUBNAME IS NULL -- ONLY THE MOST RECENT VERSION
AND C.COLL_TOID = CO.OID$
AND C.ELEM_TOID = EO.OID$
AND EO.OWNER# = EU.USER#
AND C.ELEM_TOID = ET.TVOID
AND C.PACKAGE_OBJ# IS NULL -- FILTER OUT PLSQL TYPES
AND (O.OWNER# = USERENV('SCHEMAID') OR O.OBJ# IN (SELECT OA.OBJ#
FROM SYS.OBJAUTH$ OA
WHERE GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)) OR /* USER HAS SYSTEM PRIVILEGES */ EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-184 /* EXECUTE ANY TYPE */
, -181 /* CREATE ANY TYPE */)))