rem 
rem $Header:
rem 
Rem Copyright (c) 1995, 1996, 1997, 1998 by Oracle Corporation 
Rem
Rem NAME
Rem   catadt.sql
Rem DESCRIPTION
Rem   This SQL script creates data dictionary views for showing meta-data
Rem   information for types and other object features in the RDBMS.
Rem NOTES
Rem   This script must be run while connected as SYS or INTERNAL.
Rem MODIFIED
Rem     jwijaya    01/16/98 -  add ATTR_NO to _TYPE_ATTRS
Rem     jweisz     12/18/97  - coll types for image compression flags          
Rem     cxcheng    12/31/96 -  fix bug in type views for invaild types
Rem     thoang     11/22/96 -  Update views for NCHAR
Rem     jwijaya    11/19/96 -  revise object terminologies
Rem     cxcheng    11/11/96 -  more changes to typecodes
Rem     cxcheng    11/08/96 -  change typecodes to match latest changes
Rem     jwijaya    10/14/96 -  fix coll_types comments
Rem     cxcheng    10/02/96 -  revert to version using type$.properties 64
Rem     jwijaya    07/18/96 -  add method_no;
Rem                            don't show pre-defined or built-in types
Rem     jwijaya    06/14/96 -  check for EXECUTE ANY TYPE
Rem     cxcheng    05/29/96 -  fix bugs
Rem     cxcheng    05/29/96 -  add charsetid and charsetform
Rem     asurpur    05/28/96 -  Dictionary Protection: granting privileges
Rem     mmonajje   05/22/96 -  Replace precision col name with precision#
Rem     tcheng     05/30/96 -  fix all_types, dba_types to skip sys-gen types
Rem     tcheng     05/09/96 -  don't display sys-gen types in USER_TYPES
Rem     jwijaya    05/09/96 -  NCHAR support
Rem     jwijaya    05/08/96 -  continue work
Rem     jwijaya    04/29/96 -  test the views
Rem     jwijaya    03/22/96 -  revisit type tables
Rem     jwijaya    11/29/95 -  Creation 
Rem
remark
remark  FAMILY "TYPES"
remark
remark  Views for showing information about types:
remark  USER_TYPES, ALL_TYPES, and DBA_TYPES
remark
create or replace view USER_TYPES
    (TYPE_NAME, TYPE_OID,
     TYPECODE, ATTRIBUTES, METHODS,
     PREDEFINED, INCOMPLETE)
as
select 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')
from sys.type$ t, sys.obj$ o
where o.owner# = userenv('SCHEMAID')
  and o.oid$ = t.toid
  and o.type# <> 10 -- must not be invalid
  and bitand(t.properties, 2048) = 0 -- not system-generated
/
comment on table USER_TYPES is
'Description of the user''s own types'
/
comment on column USER_TYPES.TYPE_NAME is
'Name of the type'
/
comment on column USER_TYPES.TYPE_OID is
'Object identifier (OID) of the type'
/
comment on column USER_TYPES.TYPECODE is
'Typecode of the type'
/
comment on column USER_TYPES.ATTRIBUTES is
'Number of attributes (if any) in the type'
/
comment on column USER_TYPES.METHODS is
'Number of methods (if any) in the type'
/
comment on column USER_TYPES.PREDEFINED is
'Is the type a predefined type?'
/
comment on column USER_TYPES.INCOMPLETE is
'Is the type an incomplete type?'
/
drop public synonym USER_TYPES
/
create public synonym USER_TYPES for USER_TYPES
/
grant select on USER_TYPES to PUBLIC with grant option
/
create or replace view ALL_TYPES
    (OWNER, TYPE_NAME, TYPE_OID,
     TYPECODE, ATTRIBUTES, METHODS,
     PREDEFINED, INCOMPLETE)
as
select decode(bitand(t.properties, 64), 64, null, 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')
from sys.user$ u, sys.type$ t, sys.obj$ o
where o.owner# = u.user#
  and o.oid$ = t.toid
  and o.type# <> 10 -- must not be invalid
  and bitand(t.properties, 2048) = 0 -- not system-generated
  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 */)))
/
comment on table ALL_TYPES is
'Description of types accessible to the user'
/
comment on column ALL_TYPES.OWNER is
'Owner of the type'
/
comment on column ALL_TYPES.TYPE_NAME is
'Name of the type'
/
comment on column ALL_TYPES.TYPE_OID is
'Object identifier (OID) of the type'
/
comment on column ALL_TYPES.TYPECODE is
'Typecode of the type'
/
comment on column ALL_TYPES.ATTRIBUTES is
'Number of attributes in the type'
/
comment on column ALL_TYPES.METHODS is
'Number of methods in the type'
/
comment on column ALL_TYPES.PREDEFINED is
'Is the type a predefined type?'
/
comment on column ALL_TYPES.INCOMPLETE is
'Is the type an incomplete type?'
/
drop public synonym ALL_TYPES
/
create public synonym ALL_TYPES for ALL_TYPES
/
grant select on ALL_TYPES to PUBLIC with grant option
/
create or replace view DBA_TYPES
    (OWNER, TYPE_NAME, TYPE_OID,
     TYPECODE, ATTRIBUTES, METHODS,
     PREDEFINED, INCOMPLETE)
as
select decode(bitand(t.properties, 64), 64, null, 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')
from sys.user$ u, sys.type$ t, sys.obj$ o
where o.owner# = u.user#
  and o.oid$ = t.toid
  and o.type# <> 10 -- must not be invalid
  and bitand(t.properties, 2048) = 0 -- not system-generated
/
comment on table DBA_TYPES is
'Description of all types in the database'
/
comment on column DBA_TYPES.OWNER is
'Owner of the type'
/
comment on column DBA_TYPES.TYPE_NAME is
'Name of the type'
/
comment on column DBA_TYPES.TYPE_OID is
'Object identifier (OID) of the type'
/
comment on column DBA_TYPES.TYPECODE is
'Typecode of the type'
/
comment on column DBA_TYPES.ATTRIBUTES is
'Number of attributes in the type'
/
comment on column DBA_TYPES.METHODS is
'Number of methods in the type'
/
comment on column DBA_TYPES.PREDEFINED is
'Is the type a predefined type?'
/
comment on column DBA_TYPES.INCOMPLETE is
'Is the type an incomplete type?'
/
drop public synonym DBA_TYPES
/
create public synonym DBA_TYPES for DBA_TYPES
/
grant select on DBA_TYPES to select_catalog_role
/
remark
remark  FAMILY "COLL_TYPES"
remark
remark  Views for showing information about named collection types
remark  (also categorized under named primitive types):
remark  USER_COLL_TYPES, ALL_COLL_TYPES, and DBA_COLL_TYPES
remark
create or replace view USER_COLL_TYPES
    (TYPE_NAME, COLL_TYPE, UPPER_BOUND,
     ELEM_TYPE_MOD, ELEM_TYPE_OWNER, ELEM_TYPE_NAME,
     LENGTH, PRECISION, SCALE, CHARACTER_SET_NAME, ELEM_STORAGE, 
     NULLS_STORED)
as
select o.name, co.name, c.upper_bound,
       decode(bitand(c.properties, 32768), 32768, 'REF',
              decode(bitand(c.properties, 16384), 16384, 'POINTER')),
       decode(bitand(et.properties, 64), 64, null, eu.name),
       decode(et.typecode,
              52, decode(c.charsetform, 2, 'NVARCHAR2', eo.name),
              53, decode(c.charsetform, 2, 'NCHAR', eo.name),
              54, decode(c.charsetform, 2, 'NCHAR VARYING', eo.name),
              61, 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')
from sys.obj$ o, sys.collection$ c, sys.obj$ co,
     sys.obj$ eo, sys.user$ eu, sys.type$ et
where o.owner# = userenv('SCHEMAID')
  and o.oid$ = c.toid
  and o.type# <> 10 -- must not be invalid
  and c.coll_toid = co.oid$
  and c.elem_toid = eo.oid$
  and eo.owner# = eu.user#
  and c.elem_toid = et.toid
  and c.elem_version# = et.version#
/
comment on table USER_COLL_TYPES is
'Description of the user''s own named collection types'
/
comment on column USER_COLL_TYPES.TYPE_NAME is
'Name of the type'
/
comment on column USER_COLL_TYPES.COLL_TYPE is
'Collection type'
/
comment on column USER_COLL_TYPES.UPPER_BOUND is
'Size of the FIXED ARRAY type or maximum size of the VARYING ARRAY type'
/
comment on column USER_COLL_TYPES.ELEM_TYPE_MOD is
'Type modifier of the element'
/
comment on column USER_COLL_TYPES.ELEM_TYPE_OWNER is
'Owner of the type of the element'
/
comment on column USER_COLL_TYPES.ELEM_TYPE_NAME is
'Name of the type of the element'
/
comment on column USER_COLL_TYPES.LENGTH is
'Length of the CHAR element or maximum length of the VARCHAR
or VARCHAR2 element'
/
comment on column USER_COLL_TYPES.PRECISION is
'Decimal precision of the NUMBER or DECIMAL element or
binary precision of the FLOAT element'
/
comment on column USER_COLL_TYPES.SCALE is
'Scale of the NUMBER or DECIMAL element'
/
comment on column USER_COLL_TYPES.CHARACTER_SET_NAME is
'Character set name of the element'
/
comment on column USER_COLL_TYPES.ELEM_STORAGE is
'Storage optimization specification for VARRAY of numeric elements'
/
comment on column USER_COLL_TYPES.NULLS_STORED is
'Is null information stored with each VARRAY element?'
/
drop public synonym USER_COLL_TYPES
/
create public synonym USER_COLL_TYPES for USER_COLL_TYPES
/
grant select on USER_COLL_TYPES to PUBLIC with grant option
/
create or replace view ALL_COLL_TYPES
    (OWNER, TYPE_NAME, COLL_TYPE, UPPER_BOUND,
     ELEM_TYPE_MOD, ELEM_TYPE_OWNER, ELEM_TYPE_NAME,
     LENGTH, PRECISION, SCALE, CHARACTER_SET_NAME, ELEM_STORAGE, 
     NULLS_STORED)
as
select u.name, o.name, co.name, c.upper_bound,
       decode(bitand(c.properties, 32768), 32768, 'REF',
              decode(bitand(c.properties, 16384), 16384, 'POINTER')),
       decode(bitand(et.properties, 64), 64, null, eu.name),
       decode(et.typecode,
              52, decode(c.charsetform, 2, 'NVARCHAR2', eo.name),
              53, decode(c.charsetform, 2, 'NCHAR', eo.name),
              54, decode(c.charsetform, 2, 'NCHAR VARYING', eo.name),
              61, 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')
from sys.user$ u, sys.obj$ o, sys.collection$ c, sys.obj$ co,
     sys.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 c.coll_toid = co.oid$
  and c.elem_toid = eo.oid$
  and eo.owner# = eu.user#
  and c.elem_toid = et.toid
  and c.elem_version# = et.version#
  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 */)))
/
comment on table ALL_COLL_TYPES is
'Description of named collection types accessible to the user'
/
comment on column ALL_COLL_TYPES.OWNER is
'Owner of the type'
/
comment on column ALL_COLL_TYPES.TYPE_NAME is
'Name of the type'
/
comment on column ALL_COLL_TYPES.COLL_TYPE is
'Collection type'
/
comment on column ALL_COLL_TYPES.UPPER_BOUND is
'Size of the FIXED ARRAY type or maximum size of the VARYING ARRAY type'
/
comment on column ALL_COLL_TYPES.ELEM_TYPE_MOD is
'Type modifier of the element'
/
comment on column ALL_COLL_TYPES.ELEM_TYPE_OWNER is
'Owner of the type of the element'
/
comment on column ALL_COLL_TYPES.ELEM_TYPE_NAME is
'Name of the type of the element'
/
comment on column ALL_COLL_TYPES.LENGTH is
'Length of the CHAR element or maximum length of the VARCHAR
or VARCHAR2 element'
/
comment on column ALL_COLL_TYPES.PRECISION is
'Decimal precision of the NUMBER or DECIMAL element or
binary precision of the FLOAT element'
/
comment on column ALL_COLL_TYPES.SCALE is
'Scale of the NUMBER or DECIMAL element'
/
comment on column ALL_COLL_TYPES.CHARACTER_SET_NAME is
'Character set name of the element'
/
comment on column ALL_COLL_TYPES.ELEM_STORAGE is
'Storage optimization specification for VARRAY of numeric elements'
/
comment on column ALL_COLL_TYPES.NULLS_STORED is
'Is null information stored with each VARRAY element?'
/
drop public synonym ALL_COLL_TYPES
/
create public synonym ALL_COLL_TYPES for ALL_COLL_TYPES
/
grant select on ALL_COLL_TYPES to PUBLIC with grant option
/
create or replace view DBA_COLL_TYPES
    (OWNER, TYPE_NAME, COLL_TYPE, UPPER_BOUND,
     ELEM_TYPE_MOD, ELEM_TYPE_OWNER, ELEM_TYPE_NAME,
     LENGTH, PRECISION, SCALE, CHARACTER_SET_NAME,ELEM_STORAGE, 
     NULLS_STORED)
as
select u.name, o.name, co.name, c.upper_bound,
       decode(bitand(c.properties, 32768), 32768, 'REF',
              decode(bitand(c.properties, 16384), 16384, 'POINTER')),
       decode(bitand(et.properties, 64), 64, null, eu.name),
       decode(et.typecode,
              52, decode(c.charsetform, 2, 'NVARCHAR2', eo.name),
              53, decode(c.charsetform, 2, 'NCHAR', eo.name),
              54, decode(c.charsetform, 2, 'NCHAR VARYING', eo.name),
              61, 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')
from sys.user$ u, sys.obj$ o, sys.collection$ c, sys.obj$ co,
     sys.obj$ eo, sys.user$ eu, sys.type$ et
where o.owner# = u.user#
  and o.oid$ = c.toid
  and o.type# <> 10 -- must not be invalid
  and c.coll_toid = co.oid$
  and c.elem_toid = eo.oid$
  and eo.owner# = eu.user#
  and c.elem_toid = et.toid
  and c.elem_version# = et.version#
/
comment on table DBA_COLL_TYPES is
'Description of all named collection types in the database'
/
comment on column DBA_COLL_TYPES.OWNER is
'Owner of the type'
/
comment on column DBA_COLL_TYPES.TYPE_NAME is
'Name of the type'
/
comment on column DBA_COLL_TYPES.COLL_TYPE is
'Collection type'
/
comment on column DBA_COLL_TYPES.UPPER_BOUND is
'Size of the FIXED ARRAY type or maximum size of the VARYING ARRAY type'
/
comment on column DBA_COLL_TYPES.ELEM_TYPE_MOD is
'Type modifier of the element'
/
comment on column DBA_COLL_TYPES.ELEM_TYPE_OWNER is
'Owner of the type of the element'
/
comment on column DBA_COLL_TYPES.ELEM_TYPE_NAME is
'Name of the type of the element'
/
comment on column DBA_COLL_TYPES.LENGTH is
'Length of the CHAR element or maximum length of the VARCHAR
or VARCHAR2 element'
/
comment on column DBA_COLL_TYPES.PRECISION is
'Decimal precision of the NUMBER or DECIMAL element or
binary precision of the FLOAT element'
/
comment on column DBA_COLL_TYPES.SCALE is
'Scale of the NUMBER or DECIMAL element'
/
comment on column DBA_COLL_TYPES.CHARACTER_SET_NAME is
'Character set name of the element'
/
comment on column DBA_COLL_TYPES.ELEM_STORAGE is
'Storage optimization specification for VARRAY of numeric elements'
/
comment on column DBA_COLL_TYPES.NULLS_STORED is
'Is null information stored with each VARRAY element?'
/
drop public synonym DBA_COLL_TYPES
/
create public synonym DBA_COLL_TYPES for DBA_COLL_TYPES
/
grant select on DBA_COLL_TYPES to select_catalog_role
/
remark
remark  FAMILY "TYPE_ATTRS"
remark
remark  Views for showing attribute information of object types:
remark  USER_TYPE_ATTRS, ALL_TYPE_ATTRS, and DBA_TYPE_ATTRS
remark
create or replace view USER_TYPE_ATTRS
    (TYPE_NAME, ATTR_NAME,
     ATTR_TYPE_MOD, ATTR_TYPE_OWNER, ATTR_TYPE_NAME,
     LENGTH, PRECISION, SCALE, CHARACTER_SET_NAME, ATTR_NO)
as
select o.name, a.name,
       decode(bitand(a.properties, 32768), 32768, 'REF',
              decode(bitand(a.properties, 16384), 16384, 'POINTER')),
       decode(bitand(at.properties, 64), 64, null, au.name),
       decode(at.typecode,
              52, decode(a.charsetform, 2, 'NVARCHAR2', ao.name),
              53, decode(a.charsetform, 2, 'NCHAR', ao.name),
              54, decode(a.charsetform, 2, 'NCHAR VARYING', ao.name),
              61, decode(a.charsetform, 2, 'NCLOB', ao.name),
              ao.name),
       a.length, a.precision#, a.scale,
       decode(a.charsetform, 1, 'CHAR_CS',
                             2, 'NCHAR_CS',
                             3, NLS_CHARSET_NAME(a.charsetid),
                             4, 'ARG:'||a.charsetid),
       a.attribute#
from sys.obj$ o, sys.type$ t, sys.attribute$ a, 
     sys.obj$ ao, sys.user$ au, sys.type$ at
where o.owner# = userenv('SCHEMAID')
  and o.oid$ = t.toid
  and o.type# <> 10 -- must not be invalid
  and bitand(t.properties, 2048) = 0 -- not system-generated
  and t.toid = a.toid
  and t.version# = a.version#
  and a.attr_toid = ao.oid$
  and ao.owner# = au.user#
  and a.attr_toid = at.toid
  and a.attr_version# = at.version#
/
comment on table USER_TYPE_ATTRS is
'Description of attributes of the user''s own types'
/
comment on column USER_TYPE_ATTRS.TYPE_NAME is
'Name of the type'
/
comment on column USER_TYPE_ATTRS.ATTR_NAME is
'Name of the attribute'
/
comment on column USER_TYPE_ATTRS.ATTR_TYPE_MOD is
'Type modifier of the attribute'
/
comment on column USER_TYPE_ATTRS.ATTR_TYPE_OWNER is
'Owner of the type of the attribute'
/
comment on column USER_TYPE_ATTRS.ATTR_TYPE_NAME is
'Name of the type of the attribute'
/
comment on column USER_TYPE_ATTRS.LENGTH is
'Length of the CHAR attribute or maximum length of the VARCHAR
or VARCHAR2 attribute'
/
comment on column USER_TYPE_ATTRS.PRECISION is
'Decimal precision of the NUMBER or DECIMAL attribute or
binary precision of the FLOAT attribute'
/
comment on column USER_TYPE_ATTRS.SCALE is
'Scale of the NUMBER or DECIMAL attribute'
/
comment on column USER_TYPE_ATTRS.CHARACTER_SET_NAME is
'Character set name of the attribute'
/
comment on column USER_TYPE_ATTRS.ATTR_NO is
'Syntactical order number or position of the attribute as specified in the
type specification or CREATE TYPE statement (not to be used as ID number)'
/
drop public synonym USER_TYPE_ATTRS
/
create public synonym USER_TYPE_ATTRS for USER_TYPE_ATTRS
/
grant select on USER_TYPE_ATTRS to PUBLIC with grant option
/
create or replace view ALL_TYPE_ATTRS
    (OWNER, TYPE_NAME, ATTR_NAME,
     ATTR_TYPE_MOD, ATTR_TYPE_OWNER, ATTR_TYPE_NAME,
     LENGTH, PRECISION, SCALE, CHARACTER_SET_NAME, ATTR_NO)
as
select decode(bitand(t.properties, 64), 64, null, u.name), o.name, a.name,
       decode(bitand(a.properties, 32768), 32768, 'REF',
              decode(bitand(a.properties, 16384), 16384, 'POINTER')),
       decode(bitand(at.properties, 64), 64, null, au.name),
       decode(at.typecode,
              52, decode(a.charsetform, 2, 'NVARCHAR2', ao.name),
              53, decode(a.charsetform, 2, 'NCHAR', ao.name),
              54, decode(a.charsetform, 2, 'NCHAR VARYING', ao.name),
              61, decode(a.charsetform, 2, 'NCLOB', ao.name),
              ao.name),
       a.length, a.precision#, a.scale,
       decode(a.charsetform, 1, 'CHAR_CS',
                             2, 'NCHAR_CS',
                             3, NLS_CHARSET_NAME(a.charsetid),
                             4, 'ARG:'||a.charsetid),
       a.attribute#
from sys.user$ u, sys.obj$ o, sys.type$ t, sys.attribute$ a,
     sys.obj$ ao, sys.user$ au, sys.type$ at
where o.owner# = u.user#
  and o.oid$ = t.toid
  and o.type# <> 10 -- must not be invalid
  and bitand(t.properties, 2048) = 0 -- not system-generated
  and t.toid = a.toid
  and t.version# = a.version#
  and a.attr_toid = ao.oid$
  and ao.owner# = au.user#
  and a.attr_toid = at.toid
  and a.attr_version# = at.version#
  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 */)))
/
comment on table ALL_TYPE_ATTRS is
'Description of attributes of types accessible to the user'
/
comment on column ALL_TYPE_ATTRS.OWNER is
'Owner of the type'
/
comment on column ALL_TYPE_ATTRS.TYPE_NAME is
'Name of the type'
/
comment on column ALL_TYPE_ATTRS.ATTR_NAME is
'Name of the attribute'
/
comment on column ALL_TYPE_ATTRS.ATTR_TYPE_MOD is
'Type modifier of the attribute'
/
comment on column ALL_TYPE_ATTRS.ATTR_TYPE_OWNER is
'Owner of the type of the attribute'
/
comment on column ALL_TYPE_ATTRS.ATTR_TYPE_NAME is
'Name of the type of the attribute'
/
comment on column ALL_TYPE_ATTRS.LENGTH is
'Length of the CHAR attribute or maximum length of the VARCHAR
or VARCHAR2 attribute'
/
comment on column ALL_TYPE_ATTRS.PRECISION is
'Decimal precision of the NUMBER or DECIMAL attribute or
binary precision of the FLOAT attribute'
/
comment on column ALL_TYPE_ATTRS.SCALE is
'Scale of the NUMBER or DECIMAL attribute'
/
comment on column ALL_TYPE_ATTRS.CHARACTER_SET_NAME is
'Character set name of the attribute'
/
comment on column ALL_TYPE_ATTRS.ATTR_NO is
'Syntactical order number or position of the attribute as specified in the
type specification or CREATE TYPE statement (not to be used as ID number)'
/
drop public synonym ALL_TYPE_ATTRS
/
create public synonym ALL_TYPE_ATTRS for ALL_TYPE_ATTRS
/
grant select on ALL_TYPE_ATTRS to PUBLIC with grant option
/
create or replace view DBA_TYPE_ATTRS
    (OWNER, TYPE_NAME, ATTR_NAME,
     ATTR_TYPE_MOD, ATTR_TYPE_OWNER, ATTR_TYPE_NAME,
     LENGTH, PRECISION, SCALE, CHARACTER_SET_NAME, ATTR_NO)
as
select decode(bitand(t.properties, 64), 64, null, u.name), o.name, a.name,
       decode(bitand(a.properties, 32768), 32768, 'REF',
              decode(bitand(a.properties, 16384), 16384, 'POINTER')),
       decode(bitand(at.properties, 64), 64, null, au.name),
       decode(at.typecode,
              52, decode(a.charsetform, 2, 'NVARCHAR2', ao.name),
              53, decode(a.charsetform, 2, 'NCHAR', ao.name),
              54, decode(a.charsetform, 2, 'NCHAR VARYING', ao.name),
              61, decode(a.charsetform, 2, 'NCLOB', ao.name),
              ao.name),
       a.length, a.precision#, a.scale,
       decode(a.charsetform, 1, 'CHAR_CS',
                             2, 'NCHAR_CS',
                             3, NLS_CHARSET_NAME(a.charsetid),
                             4, 'ARG:'||a.charsetid),
       a.attribute#
from sys.user$ u, sys.obj$ o, sys.type$ t, sys.attribute$ a,
     sys.obj$ ao, sys.user$ au, sys.type$ at
where o.owner# = u.user#
  and o.oid$ = t.toid
  and o.type# <> 10 -- must not be invalid
  and bitand(t.properties, 2048) = 0 -- not system-generated
  and t.toid = a.toid
  and t.version# = a.version#
  and a.attr_toid = ao.oid$
  and ao.owner# = au.user#
  and a.attr_toid = at.toid
  and a.attr_version# = at.version#
/
comment on table DBA_TYPE_ATTRS is
'Description of attributes of all types in the database'
/
comment on column DBA_TYPE_ATTRS.OWNER is
'Owner of the type'
/
comment on column DBA_TYPE_ATTRS.TYPE_NAME is
'Name of the type'
/
comment on column DBA_TYPE_ATTRS.ATTR_NAME is
'Name of the attribute'
/
comment on column DBA_TYPE_ATTRS.ATTR_TYPE_MOD is
'Type modifier of the attribute'
/
comment on column DBA_TYPE_ATTRS.ATTR_TYPE_OWNER is
'Owner of the type of the attribute'
/
comment on column DBA_TYPE_ATTRS.ATTR_TYPE_NAME is
'Name of the type of the attribute'
/
comment on column DBA_TYPE_ATTRS.LENGTH is
'Length of the CHAR attribute or maximum length of the VARCHAR
or VARCHAR2 attribute'
/
comment on column DBA_TYPE_ATTRS.PRECISION is
'Decimal precision of the NUMBER or DECIMAL attribute or
binary precision of the FLOAT attribute'
/
comment on column DBA_TYPE_ATTRS.SCALE is
'Scale of the NUMBER or DECIMAL attribute'
/
comment on column DBA_TYPE_ATTRS.CHARACTER_SET_NAME is
'Character set name of the attribute'
/
comment on column DBA_TYPE_ATTRS.ATTR_NO is
'Syntactical order number or position of the attribute as specified in the
type specification or CREATE TYPE statement (not to be used as ID number)'
/
drop public synonym DBA_TYPE_ATTRS
/
create public synonym DBA_TYPE_ATTRS for DBA_TYPE_ATTRS
/
grant select on DBA_TYPE_ATTRS to select_catalog_role
/
remark
remark  FAMILY "TYPE_METHODS"
remark
remark  Views for showing method information of object types:
remark  USER_TYPE_METHODS, ALL_TYPE_METHODS, and DBA_TYPE_METHODS
remark
create or replace view USER_TYPE_METHODS
    (TYPE_NAME, METHOD_NAME, METHOD_NO, METHOD_TYPE,
     PARAMETERS, RESULTS)
as
select o.name, m.name, m.method#,
       decode(bitand(m.properties, 512), 512, 'MAP',
              decode(bitand(m.properties, 2048), 2048, 'ORDER', 'PUBLIC')),
       m.parameters#, m.results
from sys.obj$ o, sys.method$ m
where o.owner# = userenv('SCHEMAID')
  and o.type# <> 10 -- must not be invalid
  and o.oid$ = m.toid
/
comment on table USER_TYPE_METHODS is
'Description of methods of the user''s own types'
/
comment on column USER_TYPE_METHODS.TYPE_NAME is
'Name of the type'
/
comment on column USER_TYPE_METHODS.METHOD_NAME is
'Name of the method'
/
comment on column USER_TYPE_METHODS.METHOD_NO is
'Method number for distinguishing overloaded method (not to be used as ID number)'
/
comment on column USER_TYPE_METHODS.METHOD_TYPE is
'Type of the method'
/
comment on column USER_TYPE_METHODS.PARAMETERS is
'Number of parameters to the method'
/
comment on column USER_TYPE_METHODS.RESULTS is
'Number of results returned by the method'
/
drop public synonym USER_TYPE_METHODS
/
create public synonym USER_TYPE_METHODS for USER_TYPE_METHODS
/
grant select on USER_TYPE_METHODS to PUBLIC with grant option
/
create or replace view ALL_TYPE_METHODS
    (OWNER, TYPE_NAME, METHOD_NAME, METHOD_NO, METHOD_TYPE,
     PARAMETERS, RESULTS)
as
select u.name, o.name, m.name, m.method#,
       decode(bitand(m.properties, 512), 512, 'MAP',
              decode(bitand(m.properties, 2048), 2048, 'ORDER', 'PUBLIC')),
       m.parameters#, m.results
from sys.user$ u, sys.obj$ o, sys.method$ m
where o.owner# = u.user#
  and o.type# <> 10 -- must not be invalid
  and o.oid$ = m.toid
  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 */)))
/
comment on table ALL_TYPE_METHODS is
'Description of methods of types accessible to the user'
/
comment on column ALL_TYPE_METHODS.OWNER is
'Owner of the type'
/
comment on column ALL_TYPE_METHODS.TYPE_NAME is
'Name of the type'
/
comment on column ALL_TYPE_METHODS.METHOD_NAME is
'Name of the method'
/
comment on column ALL_TYPE_METHODS.METHOD_NO is
'Method number for distinguishing overloaded method (not to be used as ID number)'
/
comment on column ALL_TYPE_METHODS.METHOD_TYPE is
'Type of the method'
/
comment on column ALL_TYPE_METHODS.PARAMETERS is
'Number of parameters to the method'
/
comment on column ALL_TYPE_METHODS.RESULTS is
'Number of results returned by the method'
/
drop public synonym ALL_TYPE_METHODS
/
create public synonym ALL_TYPE_METHODS for ALL_TYPE_METHODS
/
grant select on ALL_TYPE_METHODS to PUBLIC with grant option
/
create or replace view DBA_TYPE_METHODS
    (OWNER, TYPE_NAME, METHOD_NAME, METHOD_NO, METHOD_TYPE,
     PARAMETERS, RESULTS)
as
select u.name, o.name, m.name, m.method#,
       decode(bitand(m.properties, 512), 512, 'MAP',
              decode(bitand(m.properties, 2048), 2048, 'ORDER', 'PUBLIC')),
       m.parameters#, m.results
from sys.user$ u, sys.obj$ o, sys.method$ m
where o.owner# = u.user#
  and o.type# <> 10 -- must not be invalid
  and o.oid$ = m.toid
/
comment on table DBA_TYPE_METHODS is
'Description of methods of all types in the database'
/
comment on column DBA_TYPE_METHODS.OWNER is
'Owner of the type'
/
comment on column DBA_TYPE_METHODS.TYPE_NAME is
'Name of the type'
/
comment on column DBA_TYPE_METHODS.METHOD_NAME is
'Name of the method'
/
comment on column DBA_TYPE_METHODS.METHOD_NO is
'Method number for distinguishing overloaded method (not to be used as ID number)'
/
comment on column DBA_TYPE_METHODS.METHOD_TYPE is
'Type of the method'
/
comment on column DBA_TYPE_METHODS.PARAMETERS is
'Number of parameters to the method'
/
comment on column DBA_TYPE_METHODS.RESULTS is
'Number of results returned by the method'
/
drop public synonym DBA_TYPE_METHODS
/
create public synonym DBA_TYPE_METHODS for DBA_TYPE_METHODS
/
grant select on DBA_TYPE_METHODS to select_catalog_role
/
remark
remark  FAMILY "METHOD_PARAMS"
remark
remark  Views for showing method parameter information of object types:
remark  USER_METHOD_PARAMS, ALL_METHOD_PARAMS, and
remark  DBA_METHOD_PARAMS
remark
create or replace view USER_METHOD_PARAMS
    (TYPE_NAME, METHOD_NAME, METHOD_NO,
     PARAM_NAME, PARAM_NO, PARAM_MODE, PARAM_TYPE_MOD,
     PARAM_TYPE_OWNER, PARAM_TYPE_NAME, CHARACTER_SET_NAME)
as
select o.name, m.name, m.method#,
       p.name, p.parameter#,
       decode(bitand(p.properties, 768), 768, 'IN OUT',
              decode(bitand(p.properties, 256), 256, 'IN',
                     decode(bitand(p.properties, 512), 512, 'OUT'))),
       decode(bitand(p.properties, 32768), 32768, 'REF',
              decode(bitand(p.properties, 16384), 16384, 'POINTER')),
       decode(bitand(pt.properties, 64), 64, null, pu.name),
       decode(pt.typecode,
              52, decode(p.charsetform, 2, 'NVARCHAR2', po.name),
              53, decode(p.charsetform, 2, 'NCHAR', po.name),
              54, decode(p.charsetform, 2, 'NCHAR VARYING', po.name),
              61, decode(p.charsetform, 2, 'NCLOB', po.name),
              po.name),
       decode(p.charsetform, 1, 'CHAR_CS',         
                             2, 'NCHAR_CS',
                             3, NLS_CHARSET_NAME(p.charsetid),
                             4, 'ARG:'||p.charsetid)
from sys.obj$ o, sys.method$ m, sys.parameter$ p,
     sys.obj$ po, sys.user$ pu, sys.type$ pt
where o.owner# = userenv('SCHEMAID')
  and o.type# <> 10 -- must not be invalid
  and o.oid$ = m.toid
  and m.toid = p.toid
  and m.version# = p.version#
  and m.method# = p.method#
  and p.param_toid = po.oid$
  and po.owner# = pu.user#
  and p.param_toid = pt.toid
  and p.param_version# = pt.version#
/
comment on table USER_METHOD_PARAMS is
'Description of method parameters of the user''s own types'
/
comment on column USER_METHOD_PARAMS.TYPE_NAME is
'Name of the type'
/
comment on column USER_METHOD_PARAMS.METHOD_NAME is
'Name of the method'
/
comment on column USER_METHOD_PARAMS.METHOD_NO is
'Method number for distinguishing overloaded method (not to be used as ID number)'
/
comment on column USER_METHOD_PARAMS.PARAM_NAME is
'Name of the parameter'
/
comment on column USER_METHOD_PARAMS.PARAM_NO is
'Parameter number or position'
/
comment on column USER_METHOD_PARAMS.PARAM_MODE is
'Mode of the parameter'
/
comment on column USER_METHOD_PARAMS.PARAM_TYPE_MOD is
'Type modifier of the parameter'
/
comment on column USER_METHOD_PARAMS.PARAM_TYPE_OWNER is
'Owner of the type of the parameter'
/
comment on column USER_METHOD_PARAMS.PARAM_TYPE_NAME is
'Name of the type of the parameter'
/
comment on column USER_METHOD_PARAMS.CHARACTER_SET_NAME is
'Character set name of the parameter'
/
drop public synonym USER_METHOD_PARAMS
/
create public synonym USER_METHOD_PARAMS for USER_METHOD_PARAMS
/
grant select on USER_METHOD_PARAMS to PUBLIC with grant option
/
create or replace view ALL_METHOD_PARAMS
    (OWNER, TYPE_NAME, METHOD_NAME, METHOD_NO,
     PARAM_NAME, PARAM_NO, PARAM_MODE, PARAM_TYPE_MOD,
     PARAM_TYPE_OWNER, PARAM_TYPE_NAME, CHARACTER_SET_NAME)
as
select u.name, o.name, m.name, m.method#,
       p.name, p.parameter#,
       decode(bitand(p.properties, 768), 768, 'IN OUT',
              decode(bitand(p.properties, 256), 256, 'IN',
                     decode(bitand(p.properties, 512), 512, 'OUT'))),
       decode(bitand(p.properties, 32768), 32768, 'REF',
              decode(bitand(p.properties, 16384), 16384, 'POINTER')),
       decode(bitand(pt.properties, 64), 64, null, pu.name),
       decode(pt.typecode,
              52, decode(p.charsetform, 2, 'NVARCHAR2', po.name),
              53, decode(p.charsetform, 2, 'NCHAR', po.name),
              54, decode(p.charsetform, 2, 'NCHAR VARYING', po.name),
              61, decode(p.charsetform, 2, 'NCLOB', po.name),
              po.name),
       decode(p.charsetform, 1, 'CHAR_CS',
                             2, 'NCHAR_CS',
                             3, NLS_CHARSET_NAME(p.charsetid),
                             4, 'ARG:'||p.charsetid)
from sys.user$ u, sys.obj$ o, sys.method$ m, sys.parameter$ p,
     sys.obj$ po, sys.user$ pu, sys.type$ pt
where o.owner# = u.user#
  and o.type# <> 10 -- must not be invalid
  and o.oid$ = m.toid
  and m.toid = p.toid
  and m.version# = p.version#
  and m.method# = p.method#
  and p.param_toid = po.oid$
  and po.owner# = pu.user#
  and p.param_toid = pt.toid
  and p.param_version# = pt.version#
  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 */)))
/
comment on table ALL_METHOD_PARAMS is
'Description of method parameters of types accessible
to the user'
/
comment on column ALL_METHOD_PARAMS.OWNER is
'Onwer of the type'
/
comment on column ALL_METHOD_PARAMS.TYPE_NAME is
'Name of the type'
/
comment on column ALL_METHOD_PARAMS.METHOD_NAME is
'Name of the method'
/
comment on column ALL_METHOD_PARAMS.METHOD_NO is
'Method number for distinguishing overloaded method (not to be used as ID number)'
/
comment on column ALL_METHOD_PARAMS.PARAM_NAME is
'Name of the parameter'
/
comment on column ALL_METHOD_PARAMS.PARAM_NO is
'Parameter number or position'
/
comment on column ALL_METHOD_PARAMS.PARAM_MODE is
'Mode of the parameter'
/
comment on column ALL_METHOD_PARAMS.PARAM_TYPE_MOD is
'Type modifier of the parameter'
/
comment on column ALL_METHOD_PARAMS.PARAM_TYPE_OWNER is
'Owner of the type of the parameter'
/
comment on column ALL_METHOD_PARAMS.PARAM_TYPE_NAME is
'Name of the type of the parameter'
/
comment on column ALL_METHOD_PARAMS.CHARACTER_SET_NAME is
'Character set name of the parameter'
/
drop public synonym ALL_METHOD_PARAMS
/
create public synonym ALL_METHOD_PARAMS for ALL_METHOD_PARAMS
/
grant select on ALL_METHOD_PARAMS to PUBLIC with grant option
/
create or replace view DBA_METHOD_PARAMS
    (OWNER, TYPE_NAME, METHOD_NAME, METHOD_NO,
     PARAM_NAME, PARAM_NO, PARAM_MODE, PARAM_TYPE_MOD,
     PARAM_TYPE_OWNER, PARAM_TYPE_NAME, CHARACTER_SET_NAME)
as
select u.name, o.name, m.name, m.method#,
       p.name, p.parameter#,
       decode(bitand(p.properties, 768), 768, 'IN OUT',
              decode(bitand(p.properties, 256), 256, 'IN',
                     decode(bitand(p.properties, 512), 512, 'OUT'))),
       decode(bitand(p.properties, 32768), 32768, 'REF',
              decode(bitand(p.properties, 16384), 16384, 'POINTER')),
       decode(bitand(pt.properties, 64), 64, null, pu.name),
       decode(pt.typecode,
              52, decode(p.charsetform, 2, 'NVARCHAR2', po.name),
              53, decode(p.charsetform, 2, 'NCHAR', po.name),
              54, decode(p.charsetform, 2, 'NCHAR VARYING', po.name),
              61, decode(p.charsetform, 2, 'NCLOB', po.name),
              po.name),
       decode(p.charsetform, 1, 'CHAR_CS',
                             2, 'NCHAR_CS',
                             3, NLS_CHARSET_NAME(p.charsetid),
                             4, 'ARG:'||p.charsetid)
from sys.user$ u, sys.obj$ o, sys.method$ m, sys.parameter$ p,
     sys.obj$ po, sys.user$ pu, sys.type$ pt
where o.owner# = u.user#
  and o.type# <> 10 -- must not be invalid
  and o.oid$ = m.toid
  and m.toid = p.toid
  and m.version# = p.version#
  and m.method# = p.method#
  and p.param_toid = po.oid$
  and po.owner# = pu.user#
  and p.param_toid = pt.toid
  and p.param_version# = pt.version#
/
comment on table DBA_METHOD_PARAMS is
'Description of method parameters of all types in the database'
/
comment on column DBA_METHOD_PARAMS.OWNER is
'Onwer of the type'
/
comment on column DBA_METHOD_PARAMS.TYPE_NAME is
'Name of the type'
/
comment on column DBA_METHOD_PARAMS.METHOD_NAME is
'Name of the method'
/
comment on column DBA_METHOD_PARAMS.METHOD_NO is
'Method number for distinguishing overloaded method (not to be used as ID number)'
/
comment on column DBA_METHOD_PARAMS.PARAM_NAME is
'Name of the parameter'
/
comment on column DBA_METHOD_PARAMS.PARAM_NO is
'Parameter number or position'
/
comment on column DBA_METHOD_PARAMS.PARAM_MODE is
'Mode of the parameter'
/
comment on column DBA_METHOD_PARAMS.PARAM_TYPE_MOD is
'Type modifier of the parameter'
/
comment on column DBA_METHOD_PARAMS.PARAM_TYPE_OWNER is
'Owner of the type of the parameter'
/
comment on column DBA_METHOD_PARAMS.PARAM_TYPE_NAME is
'Name of the type of the parameter'
/
comment on column DBA_METHOD_PARAMS.CHARACTER_SET_NAME is
'Character set name of the parameter'
/
drop public synonym DBA_METHOD_PARAMS
/
create public synonym DBA_METHOD_PARAMS for DBA_METHOD_PARAMS
/
grant select on DBA_METHOD_PARAMS to select_catalog_role
/
remark
remark  FAMILY "METHOD_RESULTS"
remark
remark  Views for showing method result information of object types:
remark  USER_METHOD_RESULTS, ALL_METHOD_RESULTS, and
remark  DBA_METHOD_RESULTS
remark
create or replace view USER_METHOD_RESULTS
    (TYPE_NAME, METHOD_NAME, METHOD_NO,
     RESULT_TYPE_MOD,
     RESULT_TYPE_OWNER, RESULT_TYPE_NAME, CHARACTER_SET_NAME)
as
select o.name, m.name, m.method#,
       decode(bitand(r.properties, 32768), 32768, 'REF',
              decode(bitand(r.properties, 16384), 16384, 'POINTER')),
       decode(bitand(rt.properties, 64), 64, null, ru.name),
       decode(rt.typecode,
              52, decode(r.charsetform, 2, 'NVARCHAR2', ro.name),
              53, decode(r.charsetform, 2, 'NCHAR', ro.name),
              54, decode(r.charsetform, 2, 'NCHAR VARYING', ro.name),
              61, decode(r.charsetform, 2, 'NCLOB', ro.name),
              ro.name),
       decode(r.charsetform, 1, 'CHAR_CS',
                             2, 'NCHAR_CS',
                             3, NLS_CHARSET_NAME(r.charsetid),
                             4, 'ARG:'||r.charsetid)
from sys.obj$ o, sys.method$ m, sys.result$ r,
     sys.obj$ ro, sys.user$ ru, sys.type$ rt
where o.owner# = userenv('SCHEMAID')
  and o.type# <> 10 -- must not be invalid
  and o.oid$ = m.toid
  and m.toid = r.toid
  and m.version# = r.version#
  and m.method# = r.method#
  and r.result_toid = ro.oid$
  and ro.owner# = ru.user#
  and r.result_toid = rt.toid
  and r.result_version# = rt.version#
/
comment on table USER_METHOD_RESULTS is
'Description of method results of the user''s own types'
/
comment on column USER_METHOD_RESULTS.TYPE_NAME is
'Name of the type'
/
comment on column USER_METHOD_RESULTS.METHOD_NAME is
'Name of the method'
/
comment on column USER_METHOD_RESULTS.METHOD_NO is
'Method number for distinguishing overloaded method (not to be used as ID number)'
/
comment on column USER_METHOD_RESULTS.RESULT_TYPE_MOD is
'Type modifier of the result'
/
comment on column USER_METHOD_RESULTS.RESULT_TYPE_OWNER is
'Owner of the type of the result'
/
comment on column USER_METHOD_RESULTS.RESULT_TYPE_NAME is
'Name of the type of the result'
/
comment on column USER_METHOD_RESULTS.CHARACTER_SET_NAME is
'Character set name of the result'
/
drop public synonym USER_METHOD_RESULTS
/
create public synonym USER_METHOD_RESULTS for USER_METHOD_RESULTS
/
grant select on USER_METHOD_RESULTS to PUBLIC with grant option
/
create or replace view ALL_METHOD_RESULTS
    (OWNER, TYPE_NAME, METHOD_NAME, METHOD_NO,
     RESULT_TYPE_MOD,
     RESULT_TYPE_OWNER, RESULT_TYPE_NAME, CHARACTER_SET_NAME)
as
select u.name, o.name, m.name, m.method#,
       decode(bitand(r.properties, 32768), 32768, 'REF',
              decode(bitand(r.properties, 16384), 16384, 'POINTER')),
       decode(bitand(rt.properties, 64), 64, null, ru.name),
       decode(rt.typecode,
              52, decode(r.charsetform, 2, 'NVARCHAR2', ro.name),
              53, decode(r.charsetform, 2, 'NCHAR', ro.name),
              54, decode(r.charsetform, 2, 'NCHAR VARYING', ro.name),
              61, decode(r.charsetform, 2, 'NCLOB', ro.name),
              ro.name),
       decode(r.charsetform, 1, 'CHAR_CS',
                             2, 'NCHAR_CS',
                             3, NLS_CHARSET_NAME(r.charsetid),
                             4, 'ARG:'||r.charsetid)
from sys.user$ u, sys.obj$ o, sys.method$ m, sys.result$ r,
     sys.obj$ ro, sys.user$ ru, sys.type$ rt
where o.owner# = u.user#
  and o.type# <> 10 -- must not be invalid
  and o.oid$ = m.toid
  and m.toid = r.toid
  and m.version# = r.version#
  and m.method# = r.method#
  and r.result_toid = ro.oid$
  and ro.owner# = ru.user#
  and r.result_toid = rt.toid
  and r.result_version# = rt.version#
  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 */)))
/
comment on table ALL_METHOD_RESULTS is
'Description of method results of types accessible
to the user'
/
comment on column ALL_METHOD_RESULTS.OWNER is
'Onwer of the type'
/
comment on column ALL_METHOD_RESULTS.TYPE_NAME is
'Name of the type'
/
comment on column ALL_METHOD_RESULTS.METHOD_NAME is
'Name of the method'
/
comment on column ALL_METHOD_RESULTS.METHOD_NO is
'Method number for distinguishing overloaded method (not to be used as ID number)'
/
comment on column ALL_METHOD_RESULTS.RESULT_TYPE_MOD is
'Type modifier of the result'
/
comment on column ALL_METHOD_RESULTS.RESULT_TYPE_OWNER is
'Owner of the type of the result'
/
comment on column ALL_METHOD_RESULTS.RESULT_TYPE_NAME is
'Name of the type of the result'
/
comment on column ALL_METHOD_RESULTS.CHARACTER_SET_NAME is
'Character set name of the result'
/
drop public synonym ALL_METHOD_RESULTS
/
create public synonym ALL_METHOD_RESULTS for ALL_METHOD_RESULTS
/
grant select on ALL_METHOD_RESULTS to PUBLIC with grant option
/
create or replace view DBA_METHOD_RESULTS
    (OWNER, TYPE_NAME, METHOD_NAME, METHOD_NO,
     RESULT_TYPE_MOD,
     RESULT_TYPE_OWNER, RESULT_TYPE_NAME, CHARACTER_SET_NAME)
as
select u.name, o.name, m.name, m.method#,
       decode(bitand(r.properties, 32768), 32768, 'REF',
              decode(bitand(r.properties, 16384), 16384, 'POINTER')),
       decode(bitand(rt.properties, 64), 64, null, ru.name),
       decode(rt.typecode,
              52, decode(r.charsetform, 2, 'NVARCHAR2', ro.name),
              53, decode(r.charsetform, 2, 'NCHAR', ro.name),
              54, decode(r.charsetform, 2, 'NCHAR VARYING', ro.name),
              61, decode(r.charsetform, 2, 'NCLOB', ro.name),
              ro.name),
       decode(r.charsetform, 1, 'CHAR_CS',
                             2, 'NCHAR_CS',
                             3, NLS_CHARSET_NAME(r.charsetid),
                             4, 'ARG:'||r.charsetid)
from sys.user$ u, sys.obj$ o, sys.method$ m, sys.result$ r,
     sys.obj$ ro, sys.user$ ru, sys.type$ rt
where o.owner# = u.user#
  and o.type# <> 10 -- must not be invalid
  and o.oid$ = m.toid
  and m.toid = r.toid
  and m.version# = r.version#
  and m.method# = r.method#
  and r.result_toid = ro.oid$
  and ro.owner# = ru.user#
  and r.result_toid = rt.toid
  and r.result_version# = rt.version#
/
comment on table DBA_METHOD_RESULTS is
'Description of method results of all types in the database'
/
comment on column DBA_METHOD_RESULTS.OWNER is
'Onwer of the type'
/
comment on column DBA_METHOD_RESULTS.TYPE_NAME is
'Name of the type'
/
comment on column DBA_METHOD_RESULTS.METHOD_NAME is
'Name of the method'
/
comment on column DBA_METHOD_RESULTS.METHOD_NO is
'Method number for distinguishing overloaded method (not to be used as ID number)'
/
comment on column DBA_METHOD_RESULTS.RESULT_TYPE_MOD is
'Type modifier of the result'
/
comment on column DBA_METHOD_RESULTS.RESULT_TYPE_OWNER is
'Owner of the type of the result'
/
comment on column DBA_METHOD_RESULTS.RESULT_TYPE_NAME is
'Name of the type of the result'
/
comment on column DBA_METHOD_RESULTS.CHARACTER_SET_NAME is
'Character set name of the result'
/
drop public synonym DBA_METHOD_RESULTS
/
create public synonym DBA_METHOD_RESULTS for DBA_METHOD_RESULTS
/
grant select on DBA_METHOD_RESULTS to select_catalog_role
/
