VIEW: SYS.ALL_TYPES

Source

View Text - Preformatted

select u.name, o.name, t.toid,
       decode(t.typecode, 108, 'OBJECT',
                          122, 'COLLECTION',
                          o.name),
       t.attributes, t.methods,
       decode(bitand(t.properties, 16), 16, 'YES', 0, 'NO'),
       decode(bitand(t.properties, 256), 256, 'YES', 0, 'NO'),
       decode(bitand(t.properties, 8), 8, 'NO', 'YES'),
       decode(bitand(t.properties, 65536), 65536, 'NO', 'YES'),
       su.name, so.name, t.local_attrs, t.local_methods, t.typeid
from sys.user$ u, sys.type$ t, sys."_CURRENT_EDITION_OBJ" o,
     sys."_CURRENT_EDITION_OBJ" so, sys.user$ su
where bitand(t.properties, 64) != 64 -- u.name
  and o.owner# = u.user#
  and o.oid$ = t.tvoid
  and o.subname IS NULL -- only the most recent version
  and o.type# <> 10 -- must not be invalid
  and bitand(t.properties, 2048) = 0 -- not system-generated
  and t.supertoid = so.oid$ (+) and so.owner# = su.user# (+)
  and t.package_obj# IS NULL  -- no PL/SQL 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 */)))
UNION
select null, o.name, t.toid,
       decode(t.typecode, 108, 'OBJECT',
                          122, 'COLLECTION',
                          o.name),
       t.attributes, t.methods,
       decode(bitand(t.properties, 16), 16, 'YES', 0, 'NO'),
       decode(bitand(t.properties, 256), 256, 'YES', 0, 'NO'),
       decode(bitand(t.properties, 8), 8, 'NO', 'YES'),
       decode(bitand(t.properties, 65536), 65536, 'NO', 'YES'),
       su.name, so.name, t.local_attrs, t.local_methods, t.typeid
from sys.type$ t, sys."_CURRENT_EDITION_OBJ" o,
     sys."_CURRENT_EDITION_OBJ" so, sys.user$ su
where bitand(t.properties, 64) = 64  -- u.name is null
  and o.oid$ = t.tvoid
  and o.subname IS NULL -- only the most recent version
  and o.type# <> 10 -- must not be invalid
  and bitand(t.properties, 2048) = 0 -- not system-generated
  and t.supertoid = so.oid$ (+) and so.owner# = su.user# (+)
  and t.package_obj# IS NULL  -- no PL/SQL 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
, T.TOID
, DECODE(T.TYPECODE
, 108
, 'OBJECT'
, 122
, 'COLLECTION'
, O.NAME)
, T.ATTRIBUTES
, T.METHODS
, DECODE(BITAND(T.PROPERTIES
, 16)
, 16
, 'YES'
, 0
, 'NO')
, DECODE(BITAND(T.PROPERTIES
, 256)
, 256
, 'YES'
, 0
, 'NO')
, DECODE(BITAND(T.PROPERTIES
, 8)
, 8
, 'NO'
, 'YES')
, DECODE(BITAND(T.PROPERTIES
, 65536)
, 65536
, 'NO'
, 'YES')
, SU.NAME
, SO.NAME
, T.LOCAL_ATTRS
, T.LOCAL_METHODS
, T.TYPEID FROM SYS.USER$ U
, SYS.TYPE$ T
, SYS."_CURRENT_EDITION_OBJ" O
, SYS."_CURRENT_EDITION_OBJ" SO
, SYS.USER$ SU WHERE BITAND(T.PROPERTIES
, 64) != 64 -- U.NAME
AND O.OWNER# = U.USER#
AND O.OID$ = T.TVOID
AND O.SUBNAME IS NULL -- ONLY THE MOST RECENT VERSION
AND O.TYPE# <> 10 -- MUST NOT BE INVALID
AND BITAND(T.PROPERTIES
, 2048) = 0 -- NOT SYSTEM-GENERATED
AND T.SUPERTOID = SO.OID$ (+)
AND SO.OWNER# = SU.USER# (+)
AND T.PACKAGE_OBJ# IS NULL -- NO PL/SQL 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 */))) UNION SELECT NULL
, O.NAME
, T.TOID
, DECODE(T.TYPECODE
, 108
, 'OBJECT'
, 122
, 'COLLECTION'
, O.NAME)
, T.ATTRIBUTES
, T.METHODS
, DECODE(BITAND(T.PROPERTIES
, 16)
, 16
, 'YES'
, 0
, 'NO')
, DECODE(BITAND(T.PROPERTIES
, 256)
, 256
, 'YES'
, 0
, 'NO')
, DECODE(BITAND(T.PROPERTIES
, 8)
, 8
, 'NO'
, 'YES')
, DECODE(BITAND(T.PROPERTIES
, 65536)
, 65536
, 'NO'
, 'YES')
, SU.NAME
, SO.NAME
, T.LOCAL_ATTRS
, T.LOCAL_METHODS
, T.TYPEID FROM SYS.TYPE$ T
, SYS."_CURRENT_EDITION_OBJ" O
, SYS."_CURRENT_EDITION_OBJ" SO
, SYS.USER$ SU WHERE BITAND(T.PROPERTIES
, 64) = 64 -- U.NAME IS NULL
AND O.OID$ = T.TVOID
AND O.SUBNAME IS NULL -- ONLY THE MOST RECENT VERSION
AND O.TYPE# <> 10 -- MUST NOT BE INVALID
AND BITAND(T.PROPERTIES
, 2048) = 0 -- NOT SYSTEM-GENERATED
AND T.SUPERTOID = SO.OID$ (+)
AND SO.OWNER# = SU.USER# (+)
AND T.PACKAGE_OBJ# IS NULL -- NO PL/SQL 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 */)))