rem
rem $Header:
rem
Rem Copyright (c) 1995, 2007, Oracle. All rights reserved.
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 yifeng 09/26/07 - bug 5987532 add length semantics information to
Rem ALL_COLL_TYPES and ALL_TYPE_ATTRS
Rem atomar 04/19/07 - bug 5984502
Rem skabraha 12/14/06 - add OLDIMAGE_COLUMNS views
Rem achoi 04/14/06 - support application edition
Rem phchang 09/21/04 - lrg 1720097: change UNION ALL to UNION queries
Rem phchang 07/08/04 - #2964278: avoid fts on ALL_TYPES, DBA_TYPES and
Rem DBA_TYPE_ATTRS by making them Union All queries
Rem bbhowmic 11/21/03 - Bug 3258506
Rem cunnitha 02/07/03 - #2782260:filter out older versions
Rem phchang 11/13/02 - #2649563: make ALL_TYPE_ATTRS a UNION ALL query
Rem qyu 11/01/02 - #2621434: fix xxx_coll_types
Rem mmorsi 05/28/02 - Fix for bug 2389429.
Rem mmorsi 12/12/01 - Fix for bug 2147152.
Rem ayoaz 08/20/01 - Support synonym in attributes and collections
Rem gviswana 05/24/01 - CREATE OR REPLACE SYNONYM
Rem allee 07/20/00 - fix SQLJ_TYPE_ATTRS family.
Rem thoang 06/27/00 - Add user_type_versions
Rem allee 06/27/00 - new catalog defined for persistent JAVA object.
Rem mmorsi 06/22/00 - TYPES views should return only the latest version.
Rem rmurthy 06/28/00 - add typeid to xxx_types
Rem allee 05/24/00 - add FAMILY SQLJ_TYPES
Rem rmurthy 04/21/00 - type, attr, method - handle local&inherited
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, FINAL, INSTANTIABLE,
SUPERTYPE_OWNER, SUPERTYPE_NAME, LOCAL_ATTRIBUTES, LOCAL_METHODS, TYPEID)
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'),
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 o.owner# = userenv('SCHEMAID')
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# (+)
/
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?'
/
comment on column USER_TYPES.FINAL is
'Is the type a final type?'
/
comment on column USER_TYPES.INSTANTIABLE is
'Is the type an instantiable type?'
/
comment on column USER_TYPES.SUPERTYPE_OWNER is
'Owner of the supertype (null if type is not a subtype)'
/
comment on column USER_TYPES.SUPERTYPE_NAME is
'Name of the supertype (null if type is not a subtype)'
/
comment on column USER_TYPES.LOCAL_ATTRIBUTES is
'Number of local (not inherited) attributes (if any) in the subtype'
/
comment on column USER_TYPES.LOCAL_METHODS is
'Number of local (not inherited) methods (if any) in the subtype'
/
comment on column USER_TYPES.TYPEID is
'Type id value of the type'
/
create or replace 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, FINAL, INSTANTIABLE,
SUPERTYPE_OWNER, SUPERTYPE_NAME, LOCAL_ATTRIBUTES, LOCAL_METHODS, TYPEID)
as
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 (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.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 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 (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?'
/
comment on column ALL_TYPES.FINAL is
'Is the type a final type?'
/
comment on column ALL_TYPES.INSTANTIABLE is
'Is the type an instantiable type?'
/
comment on column ALL_TYPES.SUPERTYPE_OWNER is
'Owner of the supertype (null if type is not a subtype)'
/
comment on column ALL_TYPES.SUPERTYPE_NAME is
'Name of the supertype (null if type is not a subtype)'
/
comment on column ALL_TYPES.LOCAL_ATTRIBUTES is
'Number of local (not inherited) attributes (if any) in the subtype'
/
comment on column ALL_TYPES.LOCAL_METHODS is
'Number of local (not inherited) methods (if any) in the subtype'
/
comment on column ALL_TYPES.TYPEID is
'Type id value of the type'
/
create or replace 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, FINAL, INSTANTIABLE,
SUPERTYPE_OWNER, SUPERTYPE_NAME, LOCAL_ATTRIBUTES, LOCAL_METHODS, TYPEID)
as
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 latest 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# (+)
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.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 is null
and o.oid$ = t.tvoid
and o.subname IS NULL -- only the latest 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# (+)
/
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?'
/
comment on column DBA_TYPES.FINAL is
'Is the type a final type?'
/
comment on column DBA_TYPES.INSTANTIABLE is
'Is the type an instantiable type?'
/
comment on column DBA_TYPES.SUPERTYPE_OWNER is
'Owner of the supertype (null if type is not a subtype)'
/
comment on column DBA_TYPES.SUPERTYPE_NAME is
'Name of the supertype (null if type is not a subtype)'
/
comment on column DBA_TYPES.LOCAL_ATTRIBUTES is
'Number of local (not inherited) attributes (if any) in the subtype'
/
comment on column DBA_TYPES.LOCAL_METHODS is
'Number of local (not inherited) methods (if any) in the subtype'
/
comment on column DBA_TYPES.TYPEID is
'Type id value of the type'
/
create or replace 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')),
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')
from 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# = userenv('SCHEMAID')
and o.oid$ = c.toid
and o.subname IS NULL -- only the most recent version
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.tvoid
/
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?'
/
create or replace 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, CHAR_USED)
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')),
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 (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?'
/
comment on column ALL_COLL_TYPES.CHAR_USED is
'C if the width was specified in characters, B if in bytes'
/
create or replace 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')),
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')
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.oid$ = c.toid
and o.subname IS NULL -- only the most recent version
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.tvoid
/
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?'
/
create or replace 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, INHERITED)
as
select o.name, a.name,
decode(bitand(a.properties, 32768), 32768, 'REF',
decode(bitand(a.properties, 16384), 16384, 'POINTER')),
nvl2(a.synobj#, (select u.name from user$ u, "_CURRENT_EDITION_OBJ" o
where o.owner#=u.user# and o.obj#=a.synobj#),
decode(bitand(at.properties, 64), 64, null, au.name)),
nvl2(a.synobj#, (select o.name from "_CURRENT_EDITION_OBJ" o where o.obj#=a.synobj#),
decode(at.typecode,
9, decode(a.charsetform, 2, 'NVARCHAR2', ao.name),
96, decode(a.charsetform, 2, 'NCHAR', ao.name),
112, 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#, decode(bitand(nvl(a.xflags,0), 1), 1, 'YES', 'NO')
from sys."_CURRENT_EDITION_OBJ" o, sys.type$ t, sys.attribute$ a,
sys."_CURRENT_EDITION_OBJ" ao, sys.user$ au, sys.type$ at
where o.owner# = userenv('SCHEMAID')
and o.oid$ = t.toid
and o.subname IS NULL -- only the latest version
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.tvoid
/
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)'
/
comment on column USER_TYPE_ATTRS.INHERITED is
'Is the attribute inherited from the supertype ?'
/
create or replace 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, INHERITED, CHAR_USED)
as
select u.name , o.name, a.name,
decode(bitand(a.properties, 32768), 32768, 'REF',
decode(bitand(a.properties, 16384), 16384, 'POINTER')),
nvl2(a.synobj#, (select u.name from user$ u, "_CURRENT_EDITION_OBJ" o
where o.owner#=u.user# and o.obj#=a.synobj#),
decode(bitand(at.properties, 64), 64, null, au.name)),
nvl2(a.synobj#, (select o.name from "_CURRENT_EDITION_OBJ" o
where o.obj#=a.synobj#),
decode(at.typecode,
9, decode(a.charsetform, 2, 'NVARCHAR2', ao.name),
96, decode(a.charsetform, 2, 'NCHAR', ao.name),
112, 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#, decode(bitand(nvl(a.xflags,0), 1), 1, 'YES', 'NO'),
decode(bitand(a.properties, 4096), 4096, 'C', 'B')
from sys.user$ u, sys."_CURRENT_EDITION_OBJ" o, sys.type$ t, sys.attribute$ a,
sys."_CURRENT_EDITION_OBJ" ao, sys.user$ au, sys.type$ at
where bitand(t.properties, 64) != 64 -- u.name
and o.owner# = u.user#
and o.oid$ = t.toid
and o.subname IS NULL -- get the latest version only
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.tvoid
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 */)))
UNION
select null, o.name, a.name,
decode(bitand(a.properties, 32768), 32768, 'REF',
decode(bitand(a.properties, 16384), 16384, 'POINTER')),
nvl2(a.synobj#, (select u.name from user$ u, "_CURRENT_EDITION_OBJ" o
where o.owner#=u.user# and o.obj#=a.synobj#),
decode(bitand(at.properties, 64), 64, null, au.name)),
nvl2(a.synobj#, (select o.name from "_CURRENT_EDITION_OBJ" o
where o.obj#=a.synobj#),
decode(at.typecode,
9, decode(a.charsetform, 2, 'NVARCHAR2', ao.name),
96, decode(a.charsetform, 2, 'NCHAR', ao.name),
112, 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#, decode(bitand(nvl(a.xflags,0), 1), 1, 'YES', 'NO'),
decode(bitand(a.properties, 4096), 0, 'B', 'C')
from sys."_CURRENT_EDITION_OBJ" o, sys.type$ t, sys.attribute$ a,
sys."_CURRENT_EDITION_OBJ" ao, sys.user$ au, sys.type$ at
where bitand(t.properties, 64) = 64 -- u.name is null
and o.oid$ = t.toid
and o.subname IS NULL -- get the latest version only
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.tvoid
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)'
/
comment on column ALL_TYPE_ATTRS.INHERITED is
'Is the attribute inherited from the supertype ?'
/
comment on column ALL_TYPE_ATTRS.CHAR_USED is
'C if the width was specified in characters, B if in bytes'
/
create or replace 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, INHERITED)
as
select u.name, o.name, a.name,
decode(bitand(a.properties, 32768), 32768, 'REF',
decode(bitand(a.properties, 16384), 16384, 'POINTER')),
nvl2(a.synobj#, (select u.name from user$ u, "_CURRENT_EDITION_OBJ" o
where o.owner#=u.user# and o.obj#=a.synobj#),
decode(bitand(at.properties, 64), 64, null, au.name)),
nvl2(a.synobj#, (select o.name from "_CURRENT_EDITION_OBJ" o where o.obj#=a.synobj#),
decode(at.typecode,
9, decode(a.charsetform, 2, 'NVARCHAR2', ao.name),
96, decode(a.charsetform, 2, 'NCHAR', ao.name),
112, 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#, decode(bitand(nvl(a.xflags,0), 1), 1, 'YES', 'NO')
from sys.user$ u, sys."_CURRENT_EDITION_OBJ" o, sys.type$ t, sys.attribute$ a,
sys."_CURRENT_EDITION_OBJ" ao, sys.user$ au, sys.type$ at
where bitand(t.properties, 64) != 64 -- u.name
and o.owner# = u.user#
and o.oid$ = t.toid
and o.subname IS NULL -- get the latest version only
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.tvoid
and a.attr_version# = at.version#
UNION
select null, o.name, a.name,
decode(bitand(a.properties, 32768), 32768, 'REF',
decode(bitand(a.properties, 16384), 16384, 'POINTER')),
nvl2(a.synobj#, (select u.name from user$ u, "_CURRENT_EDITION_OBJ" o
where o.owner#=u.user# and o.obj#=a.synobj#),
decode(bitand(at.properties, 64), 64, null, au.name)),
nvl2(a.synobj#, (select o.name from "_CURRENT_EDITION_OBJ" o where o.obj#=a.synobj#),
decode(at.typecode,
9, decode(a.charsetform, 2, 'NVARCHAR2', ao.name),
96, decode(a.charsetform, 2, 'NCHAR', ao.name),
112, 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#, decode(bitand(nvl(a.xflags,0), 1), 1, 'YES', 'NO')
from sys."_CURRENT_EDITION_OBJ" o, sys.type$ t, sys.attribute$ a,
sys."_CURRENT_EDITION_OBJ" ao, sys.user$ au, sys.type$ at
where bitand(t.properties, 64) = 64 -- u.name is null
and o.oid$ = t.toid
and o.subname IS NULL -- get the latest version only
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.tvoid
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)'
/
comment on column DBA_TYPE_ATTRS.INHERITED is
'Is the attribute inherited from the supertype ?'
/
create or replace 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, FINAL, INSTANTIABLE, OVERRIDING, INHERITED)
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,
decode(bitand(m.properties, 8), 8, 'NO', 'YES'),
decode(bitand(m.properties, 65536), 65536, 'NO', 'YES'),
decode(bitand(m.properties, 131072), 131072, 'YES', 'NO'),
decode(bitand(nvl(m.xflags,0), 1), 1, 'YES', 'NO')
from sys."_CURRENT_EDITION_OBJ" o, sys.method$ m
where o.owner# = userenv('SCHEMAID')
and o.type# <> 10 -- must not be invalid
and o.oid$ = m.toid
and o.subname IS NULL -- get the latest version only
/
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'
/
comment on column USER_TYPE_METHODS.FINAL is
'Is the method final ?'
/
comment on column USER_TYPE_METHODS.INSTANTIABLE is
'Is the method instantiable ?'
/
comment on column USER_TYPE_METHODS.OVERRIDING is
'Is the method overriding a supertype method ?'
/
comment on column USER_TYPE_METHODS.INHERITED is
'Is the method inherited from the supertype ?'
/
create or replace 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, FINAL, INSTANTIABLE, OVERRIDING, INHERITED)
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,
decode(bitand(m.properties, 8), 8, 'NO', 'YES'),
decode(bitand(m.properties, 65536), 65536, 'NO', 'YES'),
decode(bitand(m.properties, 131072), 131072, 'YES', 'NO'),
decode(bitand(nvl(m.xflags,0), 1), 1, 'YES', 'NO')
from sys.user$ u, sys."_CURRENT_EDITION_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.subname IS NULL -- get the latest version only
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'
/
comment on column ALL_TYPE_METHODS.FINAL is
'Is the method final ?'
/
comment on column ALL_TYPE_METHODS.INSTANTIABLE is
'Is the method instantiable ?'
/
comment on column ALL_TYPE_METHODS.OVERRIDING is
'Is the method overriding a supertype method ?'
/
comment on column ALL_TYPE_METHODS.INHERITED is
'Is the method inherited from the supertype ?'
/
create or replace 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, FINAL, INSTANTIABLE, OVERRIDING, INHERITED)
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,
decode(bitand(m.properties, 8), 8, 'NO', 'YES'),
decode(bitand(m.properties, 65536), 65536, 'NO', 'YES'),
decode(bitand(m.properties, 131072), 131072, 'YES', 'NO'),
decode(bitand(nvl(m.xflags,0), 1), 1, 'YES', 'NO')
from sys.user$ u, sys."_CURRENT_EDITION_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.subname IS NULL -- get the latest version only
/
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'
/
comment on column DBA_TYPE_METHODS.FINAL is
'Is the method final ?'
/
comment on column DBA_TYPE_METHODS.INSTANTIABLE is
'Is the method instantiable ?'
/
comment on column DBA_TYPE_METHODS.OVERRIDING is
'Is the method overriding a supertype method ?'
/
comment on column DBA_TYPE_METHODS.INHERITED is
'Is the method inherited from the supertype ?'
/
create or replace 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,
9, decode(p.charsetform, 2, 'NVARCHAR2', po.name),
96, decode(p.charsetform, 2, 'NCHAR', po.name),
112, 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."_CURRENT_EDITION_OBJ" o, sys.method$ m, sys.parameter$ p,
sys."_CURRENT_EDITION_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 o.subname IS NULL -- get the latest version only
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'
/
create or replace 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,
9, decode(p.charsetform, 2, 'NVARCHAR2', po.name),
96, decode(p.charsetform, 2, 'NCHAR', po.name),
112, 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."_CURRENT_EDITION_OBJ" o, sys.method$ m, sys.parameter$ p,
sys."_CURRENT_EDITION_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 o.subname IS NULL -- get the latest version only
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'
/
create or replace 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,
9, decode(p.charsetform, 2, 'NVARCHAR2', po.name),
96, decode(p.charsetform, 2, 'NCHAR', po.name),
112, 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."_CURRENT_EDITION_OBJ" o, sys.method$ m, sys.parameter$ p,
sys."_CURRENT_EDITION_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 o.subname IS NULL -- get the latest version only
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'
/
create or replace 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,
9, decode(r.charsetform, 2, 'NVARCHAR2', ro.name),
96, decode(r.charsetform, 2, 'NCHAR', ro.name),
112, 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."_CURRENT_EDITION_OBJ" o, sys.method$ m, sys.result$ r,
sys."_CURRENT_EDITION_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 o.subname IS NULL -- get the latest version only
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'
/
create or replace 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,
9, decode(r.charsetform, 2, 'NVARCHAR2', ro.name),
96, decode(r.charsetform, 2, 'NCHAR', ro.name),
112, 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."_CURRENT_EDITION_OBJ" o, sys.method$ m, sys.result$ r,
sys."_CURRENT_EDITION_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 o.subname IS NULL -- get the latest version only
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'
/
create or replace 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,
9, decode(r.charsetform, 2, 'NVARCHAR2', ro.name),
96, decode(r.charsetform, 2, 'NCHAR', ro.name),
112, 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."_CURRENT_EDITION_OBJ" o, sys.method$ m, sys.result$ r,
sys."_CURRENT_EDITION_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 o.subname IS NULL -- get the latest version only
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'
/
create or replace public synonym DBA_METHOD_RESULTS for DBA_METHOD_RESULTS
/
grant select on DBA_METHOD_RESULTS to select_catalog_role
/
remark
remark FAMILY "SQLJ_TYPES"
remark
remark Views for showing information about types:
remark USER_SQLJ_TYPES, ALL_SQLJ_TYPES, and DBA_SQLJ_TYPES
remark
create or replace view USER_SQLJ_TYPES
(TYPE_NAME, TYPE_OID, EXTERNAL_NAME, USING,
TYPECODE, ATTRIBUTES, METHODS,
PREDEFINED, INCOMPLETE, FINAL, INSTANTIABLE,
SUPERTYPE_OWNER, SUPERTYPE_NAME, LOCAL_ATTRIBUTES, LOCAL_METHODS)
as
select o.name, t.toid, t.externname,
decode(t.externtype, 1, 'SQLData',
2, 'CustomDatum',
3, 'Serializable',
4, 'Serializable Internal',
5, 'ORAData',
'unknown'),
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
from sys.type$ t, sys."_CURRENT_EDITION_OBJ" o, sys."_CURRENT_EDITION_OBJ" so,
sys.user$ su
where o.owner# = userenv('SCHEMAID')
and o.oid$ = t.tvoid
and o.subname IS NULL -- only latest 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.externtype < 5
/
comment on table USER_SQLJ_TYPES is
'Description of the user''s own types'
/
comment on column USER_SQLJ_TYPES.TYPE_NAME is
'Name of the type'
/
comment on column USER_SQLJ_TYPES.TYPE_OID is
'Object identifier (OID) of the type'
/
comment on column USER_SQLJ_TYPES.EXTERNAL_NAME is
'External class name of the type'
/
comment on column USER_SQLJ_TYPES.USING is
'Representation of the type'
/
comment on column USER_SQLJ_TYPES.TYPECODE is
'Typecode of the type'
/
comment on column USER_SQLJ_TYPES.ATTRIBUTES is
'Number of attributes (if any) in the type'
/
comment on column USER_SQLJ_TYPES.METHODS is
'Number of methods (if any) in the type'
/
comment on column USER_SQLJ_TYPES.PREDEFINED is
'Is the type a predefined type?'
/
comment on column USER_SQLJ_TYPES.INCOMPLETE is
'Is the type an incomplete type?'
/
comment on column USER_SQLJ_TYPES.FINAL is
'Is the type a final type?'
/
comment on column USER_SQLJ_TYPES.INSTANTIABLE is
'Is the type an instantiable type?'
/
comment on column USER_SQLJ_TYPES.SUPERTYPE_OWNER is
'Owner of the supertype (null if type is not a subtype)'
/
comment on column USER_SQLJ_TYPES.SUPERTYPE_NAME is
'Name of the supertype (null if type is not a subtype)'
/
comment on column USER_SQLJ_TYPES.LOCAL_ATTRIBUTES is
'Number of local (not inherited) attributes (if any) in the subtype'
/
comment on column USER_SQLJ_TYPES.LOCAL_METHODS is
'Number of local (not inherited) methods (if any) in the subtype'
/
create or replace public synonym USER_SQLJ_TYPES for USER_SQLJ_TYPES
/
grant select on USER_SQLJ_TYPES to PUBLIC with grant option
/
create or replace view ALL_SQLJ_TYPES
(OWNER, TYPE_NAME, TYPE_OID, EXTERNAL_NAME, USING,
TYPECODE, ATTRIBUTES, METHODS,
PREDEFINED, INCOMPLETE, FINAL, INSTANTIABLE,
SUPERTYPE_OWNER, SUPERTYPE_NAME, LOCAL_ATTRIBUTES, LOCAL_METHODS)
as
select decode(bitand(t.properties, 64), 64, null, u.name), o.name, t.toid,
t.externname,
decode(t.externtype, 1, 'SQLData',
2, 'CustomDatum',
3, 'Serializable',
4, 'Serializable Internal',
5, 'ORAData',
'unknown'),
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
from sys.user$ u, sys.type$ t, sys."_CURRENT_EDITION_OBJ" o,
sys."_CURRENT_EDITION_OBJ" so, sys.user$ su
where o.owner# = u.user#
and o.oid$ = t.tvoid
and o.subname IS NULL -- only latest 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.externtype < 5
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_SQLJ_TYPES is
'Description of types accessible to the user'
/
comment on column ALL_SQLJ_TYPES.OWNER is
'Owner of the type'
/
comment on column ALL_SQLJ_TYPES.TYPE_NAME is
'Name of the type'
/
comment on column ALL_SQLJ_TYPES.TYPE_OID is
'Object identifier (OID) of the type'
/
comment on column ALL_SQLJ_TYPES.EXTERNAL_NAME is
'External class name of the type'
/
comment on column ALL_SQLJ_TYPES.USING is
'Representation of the type'
/
comment on column ALL_SQLJ_TYPES.TYPECODE is
'Typecode of the type'
/
comment on column ALL_SQLJ_TYPES.ATTRIBUTES is
'Number of attributes in the type'
/
comment on column ALL_SQLJ_TYPES.METHODS is
'Number of methods in the type'
/
comment on column ALL_SQLJ_TYPES.PREDEFINED is
'Is the type a predefined type?'
/
comment on column ALL_SQLJ_TYPES.INCOMPLETE is
'Is the type an incomplete type?'
/
comment on column ALL_SQLJ_TYPES.FINAL is
'Is the type a final type?'
/
comment on column ALL_SQLJ_TYPES.INSTANTIABLE is
'Is the type an instantiable type?'
/
comment on column ALL_SQLJ_TYPES.SUPERTYPE_OWNER is
'Owner of the supertype (null if type is not a subtype)'
/
comment on column ALL_SQLJ_TYPES.SUPERTYPE_NAME is
'Name of the supertype (null if type is not a subtype)'
/
comment on column ALL_SQLJ_TYPES.LOCAL_ATTRIBUTES is
'Number of local (not inherited) attributes (if any) in the subtype'
/
comment on column ALL_SQLJ_TYPES.LOCAL_METHODS is
'Number of local (not inherited) methods (if any) in the subtype'
/
create or replace public synonym ALL_SQLJ_TYPES for ALL_SQLJ_TYPES
/
grant select on ALL_SQLJ_TYPES to PUBLIC with grant option
/
create or replace view DBA_SQLJ_TYPES
(OWNER, TYPE_NAME, TYPE_OID, EXTERNAL_NAME, USING,
TYPECODE, ATTRIBUTES, METHODS,
PREDEFINED, INCOMPLETE, FINAL, INSTANTIABLE,
SUPERTYPE_OWNER, SUPERTYPE_NAME, LOCAL_ATTRIBUTES, LOCAL_METHODS)
as
select decode(bitand(t.properties, 64), 64, null, u.name), o.name, t.toid,
t.externname,
decode(t.externtype, 1, 'SQLData',
2, 'CustomDatum',
3, 'Serializable',
4, 'Serializable Internal',
5, 'ORAData',
'unknown'),
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
from sys.user$ u, sys.type$ t, sys."_CURRENT_EDITION_OBJ" o,
sys."_CURRENT_EDITION_OBJ" so, sys.user$ su
where o.owner# = u.user#
and o.oid$ = t.tvoid
and o.subname IS NULL -- only the latest 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.externtype < 5
/
comment on table DBA_SQLJ_TYPES is
'Description of all types in the database'
/
comment on column DBA_SQLJ_TYPES.OWNER is
'Owner of the type'
/
comment on column DBA_SQLJ_TYPES.TYPE_NAME is
'Name of the type'
/
comment on column DBA_SQLJ_TYPES.TYPE_OID is
'Object identifier (OID) of the type'
/
comment on column DBA_SQLJ_TYPES.EXTERNAL_NAME is
'External class name of the type'
/
comment on column DBA_SQLJ_TYPES.USING is
'Representation of the type'
/
comment on column DBA_SQLJ_TYPES.TYPECODE is
'Typecode of the type'
/
comment on column DBA_SQLJ_TYPES.ATTRIBUTES is
'Number of attributes in the type'
/
comment on column DBA_SQLJ_TYPES.METHODS is
'Number of methods in the type'
/
comment on column DBA_SQLJ_TYPES.PREDEFINED is
'Is the type a predefined type?'
/
comment on column DBA_SQLJ_TYPES.INCOMPLETE is
'Is the type an incomplete type?'
/
comment on column DBA_SQLJ_TYPES.FINAL is
'Is the type a final type?'
/
comment on column DBA_SQLJ_TYPES.INSTANTIABLE is
'Is the type an instantiable type?'
/
comment on column DBA_SQLJ_TYPES.SUPERTYPE_OWNER is
'Owner of the supertype (null if type is not a subtype)'
/
comment on column DBA_SQLJ_TYPES.SUPERTYPE_NAME is
'Name of the supertype (null if type is not a subtype)'
/
comment on column DBA_SQLJ_TYPES.LOCAL_ATTRIBUTES is
'Number of local (not inherited) attributes (if any) in the subtype'
/
comment on column DBA_SQLJ_TYPES.LOCAL_METHODS is
'Number of local (not inherited) methods (if any) in the subtype'
/
create or replace public synonym DBA_SQLJ_TYPES for DBA_SQLJ_TYPES
/
grant select on DBA_SQLJ_TYPES to select_catalog_role
/
remark
remark FAMILY "TYPE_VERSIONS"
remark
remark Views for showing information about types:
remark USER_TYPE_VERSIONS, ALL_TYPE_VERSIONS, and DBA_TYPE_VERSIONS
remark
create or replace view USER_TYPE_VERSIONS
(TYPE_NAME, VERSION#, TYPECODE, STATUS, LINE, TEXT, HASHCODE)
as
select o.name, t.version#,
decode(t.typecode, 108, 'OBJECT',
122, 'COLLECTION',
o.name),
decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),
s.line, s.source,
t.hashcode
from sys."_CURRENT_EDITION_OBJ" o, sys.source$ s, sys.type$ t
where o.obj# = s.obj# and o.oid$ = t.tvoid and o.type# = 13
and o.owner# = userenv('SCHEMAID');
comment on table USER_TYPE_VERSIONS is
'Description of each version of the user''s types'
/
comment on column USER_TYPE_VERSIONS.TYPE_NAME is
'Name of the type'
/
comment on column USER_TYPE_VERSIONS.VERSION# is
'Internal version number of the type'
/
comment on column USER_TYPE_VERSIONS.TYPECODE is
'Typecode of the type'
/
comment on column USER_TYPE_VERSIONS.STATUS is
'Status of the type'
/
comment on column USER_TYPE_VERSIONS.LINE is
'Line number of the type''s spec'
/
comment on column USER_TYPE_VERSIONS.TEXT is
'Text of the type''s spec'
/
comment on column USER_TYPE_VERSIONS.HASHCODE is
'Hashcode of the type'
/
create or replace public synonym USER_TYPE_VERSIONS for USER_TYPE_VERSIONS
/
grant select on USER_TYPE_VERSIONS to PUBLIC with grant option
/
create or replace view ALL_TYPE_VERSIONS
(OWNER, TYPE_NAME, VERSION#, TYPECODE, STATUS, LINE, TEXT, HASHCODE)
as
select u.name, o.name, t.version#,
decode(t.typecode, 108, 'OBJECT',
122, 'COLLECTION',
o.name),
decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),
s.line, s.source,
t.hashcode
from sys."_CURRENT_EDITION_OBJ" o, sys.source$ s, sys.type$ t, user$ u
where o.obj# = s.obj# and o.oid$ = t.tvoid and o.type# = 13
and o.owner# = u.user#
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_VERSIONS is
'Description of each type version accessible to the user'
/
comment on column ALL_TYPE_VERSIONS.OWNER is
'Owner of the type'
/
comment on column ALL_TYPE_VERSIONS.TYPE_NAME is
'Name of the type'
/
comment on column ALL_TYPE_VERSIONS.VERSION# is
'Internal version number of the type'
/
comment on column ALL_TYPE_VERSIONS.TYPECODE is
'Typecode of the type'
/
comment on column ALL_TYPE_VERSIONS.STATUS is
'Status of the type'
/
comment on column ALL_TYPE_VERSIONS.LINE is
'Line number of the type''s spec'
/
comment on column ALL_TYPE_VERSIONS.TEXT is
'Text of the type''s spec'
/
comment on column ALL_TYPE_VERSIONS.HASHCODE is
'Hashcode of the type'
/
create or replace public synonym ALL_TYPE_VERSIONS for ALL_TYPE_VERSIONS
/
grant select on ALL_TYPE_VERSIONS to PUBLIC with grant option
/
create or replace view DBA_TYPE_VERSIONS
(OWNER, TYPE_NAME, VERSION#, TYPECODE, STATUS, LINE, TEXT, HASHCODE)
as
select u.name, o.name, t.version#,
decode(t.typecode, 108, 'OBJECT',
122, 'COLLECTION',
o.name),
decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),
s.line, s.source,
t.hashcode
from sys."_CURRENT_EDITION_OBJ" o, sys.source$ s, sys.type$ t, user$ u
where o.obj# = s.obj# and o.oid$ = t.tvoid and o.type# = 13
and o.owner# = u.user#;
comment on table DBA_TYPE_VERSIONS is
'Description of each type version in the database'
/
comment on column DBA_TYPE_VERSIONS.OWNER is
'Owner of the type'
/
comment on column DBA_TYPE_VERSIONS.TYPE_NAME is
'Name of the type'
/
comment on column DBA_TYPE_VERSIONS.VERSION# is
'Internal version number of the type'
/
comment on column DBA_TYPE_VERSIONS.TYPECODE is
'Typecode of the type'
/
comment on column DBA_TYPE_VERSIONS.STATUS is
'Status of the type'
/
comment on column DBA_TYPE_VERSIONS.LINE is
'Line number of the type''s spec'
/
comment on column DBA_TYPE_VERSIONS.TEXT is
'Text of the type''s spec'
/
comment on column DBA_TYPE_VERSIONS.HASHCODE is
'Hashcode of the type'
/
create or replace public synonym DBA_TYPE_VERSIONS for DBA_TYPE_VERSIONS
/
grant select on DBA_TYPE_VERSIONS to select_catalog_role
/
remark
remark FAMILY "PENDING_CONV_TABLES"
remark
remark Views for showing information about types:
remark USER_PENDING_CONV_TABLES, ALL_PENDING_CONV_TABLES, and
remark DBA_PENDING_CONV_TABLES
remark
create or replace view USER_PENDING_CONV_TABLES
(TABLE_NAME)
as
select o.name
from sys.obj$ o
where o.type# = 2 and o.status = 5
and bitand(o.flags, 4096) = 4096 /* type evolved flg */
and o.owner# = userenv('SCHEMAID');
comment on table USER_PENDING_CONV_TABLES is
'All user''s tables which are not upgraded to the latest type version'
/
comment on column USER_PENDING_CONV_TABLES.TABLE_NAME is
'Name of the table'
/
create or replace public synonym USER_PENDING_CONV_TABLES
for USER_PENDING_CONV_TABLES
/
grant select on USER_PENDING_CONV_TABLES to PUBLIC with grant option
/
create or replace view ALL_PENDING_CONV_TABLES
(OWNER, TABLE_NAME)
as
select u.name, o.name
from sys.obj$ o, user$ u
where o.type# = 2 and o.status = 5
and bitand(o.flags, 4096) = 4096 /* type evolved flg */
and o.owner# = u.user#
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 (-45 /* LOCK ANY TABLE */,
-47 /* SELECT ANY TABLE */,
-48 /* INSERT ANY TABLE */,
-49 /* UPDATE ANY TABLE */,
-50 /* DELETE ANY TABLE */)))
/
comment on table ALL_PENDING_CONV_TABLES is
'All tables accessible to the user which are not upgraded to the latest type version'
/
comment on column ALL_PENDING_CONV_TABLES.OWNER is
'Owner of the table'
/
comment on column ALL_PENDING_CONV_TABLES.TABLE_NAME is
'Name of the table'
/
create or replace public synonym ALL_PENDING_CONV_TABLES
for ALL_PENDING_CONV_TABLES
/
grant select on ALL_PENDING_CONV_TABLES to PUBLIC with grant option
/
create or replace view DBA_PENDING_CONV_TABLES
(OWNER, TABLE_NAME)
as
select u.name, o.name
from sys.obj$ o, user$ u
where o.type# = 2 and o.status = 5
and bitand(o.flags, 4096) = 4096 /* type evolved flg */
and o.owner# = u.user#;
comment on table DBA_PENDING_CONV_TABLES is
'All tables which are not upgraded to the latest type version in the database'
/
comment on column DBA_PENDING_CONV_TABLES.OWNER is
'Owner of the table'
/
comment on column DBA_PENDING_CONV_TABLES.TABLE_NAME is
'Name of the table'
/
create or replace public synonym DBA_PENDING_CONV_TABLES
for DBA_PENDING_CONV_TABLES
/
grant select on DBA_PENDING_CONV_TABLES to select_catalog_role
/
remark
remark FAMILY "SQLJ_TYPE_ATTRS"
remark
remark Views for showing attribute information of object types:
remark USER_SQLJ_TYPE_ATTRS, ALL_SQLJ_TYPE_ATTRS, and DBA_SQLJ_TYPE_ATTRS
remark
create or replace view USER_SQLJ_TYPE_ATTRS
(TYPE_NAME, ATTR_NAME, EXTERNAL_ATTR_NAME,
ATTR_TYPE_MOD, ATTR_TYPE_OWNER, ATTR_TYPE_NAME,
LENGTH, PRECISION, SCALE, CHARACTER_SET_NAME, ATTR_NO, INHERITED)
as
select o.name, a.name, a.externname,
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,
9, decode(a.charsetform, 2, 'NVARCHAR2', ao.name),
96, decode(a.charsetform, 2, 'NCHAR', ao.name),
112, 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#, decode(bitand(nvl(a.xflags,0), 1), 1, 'YES', 'NO')
from sys."_CURRENT_EDITION_OBJ" o, sys.type$ t, sys.attribute$ a,
sys."_CURRENT_EDITION_OBJ" ao, sys.user$ au, sys.type$ at
where o.owner# = userenv('SCHEMAID')
and o.oid$ = t.toid
and o.subname IS NULL -- only the latest version
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.tvoid
and a.attr_version# = at.version#
and t.externtype < 5
/
comment on table USER_SQLJ_TYPE_ATTRS is
'Description of attributes of the user''s own types'
/
comment on column USER_SQLJ_TYPE_ATTRS.TYPE_NAME is
'Name of the type'
/
comment on column USER_SQLJ_TYPE_ATTRS.ATTR_NAME is
'Name of the attribute'
/
comment on column USER_SQLJ_TYPE_ATTRS.EXTERNAL_ATTR_NAME is
'External name of the attribute'
/
comment on column USER_SQLJ_TYPE_ATTRS.ATTR_TYPE_MOD is
'Type modifier of the attribute'
/
comment on column USER_SQLJ_TYPE_ATTRS.ATTR_TYPE_OWNER is
'Owner of the type of the attribute'
/
comment on column USER_SQLJ_TYPE_ATTRS.ATTR_TYPE_NAME is
'Name of the type of the attribute'
/
comment on column USER_SQLJ_TYPE_ATTRS.LENGTH is
'Length of the CHAR attribute or maximum length of the VARCHAR
or VARCHAR2 attribute'
/
comment on column USER_SQLJ_TYPE_ATTRS.PRECISION is
'Decimal precision of the NUMBER or DECIMAL attribute or
binary precision of the FLOAT attribute'
/
comment on column USER_SQLJ_TYPE_ATTRS.SCALE is
'Scale of the NUMBER or DECIMAL attribute'
/
comment on column USER_SQLJ_TYPE_ATTRS.CHARACTER_SET_NAME is
'Character set name of the attribute'
/
comment on column USER_SQLJ_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)'
/
comment on column USER_SQLJ_TYPE_ATTRS.INHERITED is
'Is the attribute inherited from the supertype ?'
/
create or replace public synonym USER_SQLJ_TYPE_ATTRS for USER_SQLJ_TYPE_ATTRS
/
grant select on USER_SQLJ_TYPE_ATTRS to PUBLIC with grant option
/
create or replace view ALL_SQLJ_TYPE_ATTRS
(OWNER, TYPE_NAME, ATTR_NAME, EXTERNAL_ATTR_NAME,
ATTR_TYPE_MOD, ATTR_TYPE_OWNER, ATTR_TYPE_NAME,
LENGTH, PRECISION, SCALE, CHARACTER_SET_NAME, ATTR_NO, INHERITED)
as
select decode(bitand(t.properties, 64), 64, null, u.name),
o.name, a.name, a.externname,
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,
9, decode(a.charsetform, 2, 'NVARCHAR2', ao.name),
96, decode(a.charsetform, 2, 'NCHAR', ao.name),
112, 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#, decode(bitand(nvl(a.xflags,0), 1), 1, 'YES', 'NO')
from sys.user$ u, sys."_CURRENT_EDITION_OBJ" o, sys.type$ t, sys.attribute$ a,
sys."_CURRENT_EDITION_OBJ" ao, sys.user$ au, sys.type$ at
where o.owner# = u.user#
and o.oid$ = t.toid
and o.subname IS NULL -- get the latest version only
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.tvoid
and a.attr_version# = at.version#
and t.externtype < 5
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_SQLJ_TYPE_ATTRS is
'Description of attributes of types accessible to the user'
/
comment on column ALL_SQLJ_TYPE_ATTRS.OWNER is
'Owner of the type'
/
comment on column ALL_SQLJ_TYPE_ATTRS.TYPE_NAME is
'Name of the type'
/
comment on column ALL_SQLJ_TYPE_ATTRS.ATTR_NAME is
'Name of the attribute'
/
comment on column ALL_SQLJ_TYPE_ATTRS.EXTERNAL_ATTR_NAME is
'External name of the attribute'
/
comment on column ALL_SQLJ_TYPE_ATTRS.ATTR_TYPE_MOD is
'Type modifier of the attribute'
/
comment on column ALL_SQLJ_TYPE_ATTRS.ATTR_TYPE_OWNER is
'Owner of the type of the attribute'
/
comment on column ALL_SQLJ_TYPE_ATTRS.ATTR_TYPE_NAME is
'Name of the type of the attribute'
/
comment on column ALL_SQLJ_TYPE_ATTRS.LENGTH is
'Length of the CHAR attribute or maximum length of the VARCHAR
or VARCHAR2 attribute'
/
comment on column ALL_SQLJ_TYPE_ATTRS.PRECISION is
'Decimal precision of the NUMBER or DECIMAL attribute or
binary precision of the FLOAT attribute'
/
comment on column ALL_SQLJ_TYPE_ATTRS.SCALE is
'Scale of the NUMBER or DECIMAL attribute'
/
comment on column ALL_SQLJ_TYPE_ATTRS.CHARACTER_SET_NAME is
'Character set name of the attribute'
/
comment on column ALL_SQLJ_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)'
/
comment on column ALL_SQLJ_TYPE_ATTRS.INHERITED is
'Is the attribute inherited from the supertype ?'
/
create or replace public synonym ALL_SQLJ_TYPE_ATTRS for ALL_SQLJ_TYPE_ATTRS
/
grant select on ALL_SQLJ_TYPE_ATTRS to PUBLIC with grant option
/
create or replace view DBA_SQLJ_TYPE_ATTRS
(OWNER, TYPE_NAME, ATTR_NAME, EXTERNAL_ATTR_NAME,
ATTR_TYPE_MOD, ATTR_TYPE_OWNER, ATTR_TYPE_NAME,
LENGTH, PRECISION, SCALE, CHARACTER_SET_NAME, ATTR_NO, INHERITED)
as
select decode(bitand(t.properties, 64), 64, null, u.name),
o.name, a.name, a.externname,
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,
9, decode(a.charsetform, 2, 'NVARCHAR2', ao.name),
96, decode(a.charsetform, 2, 'NCHAR', ao.name),
112, 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#, decode(bitand(nvl(a.xflags,0), 1), 1, 'YES', 'NO')
from sys.user$ u, sys."_CURRENT_EDITION_OBJ" o, sys.type$ t, sys.attribute$ a,
sys."_CURRENT_EDITION_OBJ" ao, sys.user$ au, sys.type$ at
where o.owner# = u.user#
and o.oid$ = t.toid
and o.subname IS NULL -- get the latest version only
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.tvoid
and a.attr_version# = at.version#
and t.externtype < 5
/
comment on table DBA_SQLJ_TYPE_ATTRS is
'Description of attributes of all types in the database'
/
comment on column DBA_SQLJ_TYPE_ATTRS.OWNER is
'Owner of the type'
/
comment on column DBA_SQLJ_TYPE_ATTRS.TYPE_NAME is
'Name of the type'
/
comment on column DBA_SQLJ_TYPE_ATTRS.ATTR_NAME is
'Name of the attribute'
/
comment on column DBA_SQLJ_TYPE_ATTRS.EXTERNAL_ATTR_NAME is
'External name of the attribute'
/
comment on column DBA_SQLJ_TYPE_ATTRS.ATTR_TYPE_MOD is
'Type modifier of the attribute'
/
comment on column DBA_SQLJ_TYPE_ATTRS.ATTR_TYPE_OWNER is
'Owner of the type of the attribute'
/
comment on column DBA_SQLJ_TYPE_ATTRS.ATTR_TYPE_NAME is
'Name of the type of the attribute'
/
comment on column DBA_SQLJ_TYPE_ATTRS.LENGTH is
'Length of the CHAR attribute or maximum length of the VARCHAR
or VARCHAR2 attribute'
/
comment on column DBA_SQLJ_TYPE_ATTRS.PRECISION is
'Decimal precision of the NUMBER or DECIMAL attribute or
binary precision of the FLOAT attribute'
/
comment on column DBA_SQLJ_TYPE_ATTRS.SCALE is
'Scale of the NUMBER or DECIMAL attribute'
/
comment on column DBA_SQLJ_TYPE_ATTRS.CHARACTER_SET_NAME is
'Character set name of the attribute'
/
comment on column DBA_SQLJ_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)'
/
comment on column DBA_SQLJ_TYPE_ATTRS.INHERITED is
'Is the attribute inherited from the supertype ?'
/
create or replace public synonym DBA_SQLJ_TYPE_ATTRS for DBA_SQLJ_TYPE_ATTRS
/
grant select on DBA_SQLJ_TYPE_ATTRS to select_catalog_role
/
remark
remark FAMILY "SQLJ_TYPE_METHODS"
remark
remark Views for showing method information of object types:
remark USER_SQLJ_TYPE_METHODS, ALL_SQLJ_TYPE_METHODS, and DBA_SQLJ_TYPE_METHODS
remark
create or replace view USER_SQLJ_TYPE_METHODS
(TYPE_NAME, METHOD_NAME, EXTERNAL_VAR_NAME, METHOD_NO, METHOD_TYPE,
PARAMETERS, RESULTS, FINAL, INSTANTIABLE, OVERRIDING, INHERITED)
as
select o.name, m.name, m.externVarName, m.method#,
decode(bitand(m.properties, 512), 512, 'MAP',
decode(bitand(m.properties, 2048), 2048, 'ORDER', 'PUBLIC')),
m.parameters#, m.results,
decode(bitand(m.properties, 8), 8, 'NO', 'YES'),
decode(bitand(m.properties, 65536), 65536, 'NO', 'YES'),
decode(bitand(m.properties, 131072), 131072, 'YES', 'NO'),
decode(bitand(nvl(m.xflags,0), 1), 1, 'YES', 'NO')
from sys."_CURRENT_EDITION_OBJ" o, sys.type$ t, sys.method$ m
where o.owner# = userenv('SCHEMAID')
and o.oid$ = m.toid
and o.subname IS NULL -- get the latest version only
and o.type# <> 10 -- must not be invalid
and bitand(t.properties, 2048) = 0 -- not system-generated
and t.toid = m.toid
and t.version# = m.version#
and t.externtype < 5
/
comment on table USER_SQLJ_TYPE_METHODS is
'Description of methods of the user''s own types'
/
comment on column USER_SQLJ_TYPE_METHODS.TYPE_NAME is
'Name of the type'
/
comment on column USER_SQLJ_TYPE_METHODS.METHOD_NAME is
'Name of the method'
/
comment on column USER_SQLJ_TYPE_METHODS.EXTERNAL_VAR_NAME is
'Name of the external variable'
/
comment on column USER_SQLJ_TYPE_METHODS.METHOD_NO is
'Method number for distinguishing overloaded method (not to be used as ID number)'
/
comment on column USER_SQLJ_TYPE_METHODS.METHOD_TYPE is
'Type of the method'
/
comment on column USER_SQLJ_TYPE_METHODS.PARAMETERS is
'Number of parameters to the method'
/
comment on column USER_SQLJ_TYPE_METHODS.RESULTS is
'Number of results returned by the method'
/
comment on column USER_SQLJ_TYPE_METHODS.FINAL is
'Is the method final ?'
/
comment on column USER_SQLJ_TYPE_METHODS.INSTANTIABLE is
'Is the method instantiable ?'
/
comment on column USER_SQLJ_TYPE_METHODS.OVERRIDING is
'Is the method overriding a supertype method ?'
/
comment on column USER_SQLJ_TYPE_METHODS.INHERITED is
'Is the method inherited from the supertype ?'
/
create or replace public synonym USER_SQLJ_TYPE_METHODS
for USER_SQLJ_TYPE_METHODS
/
grant select on USER_SQLJ_TYPE_METHODS to PUBLIC with grant option
/
create or replace view ALL_SQLJ_TYPE_METHODS
(OWNER, TYPE_NAME, METHOD_NAME, EXTERNAL_VAR_NAME, METHOD_NO, METHOD_TYPE,
PARAMETERS, RESULTS, FINAL, INSTANTIABLE, OVERRIDING, INHERITED)
as
select u.name, o.name, m.name, m.externVarName, m.method#,
decode(bitand(m.properties, 512), 512, 'MAP',
decode(bitand(m.properties, 2048), 2048, 'ORDER', 'PUBLIC')),
m.parameters#, m.results,
decode(bitand(m.properties, 8), 8, 'NO', 'YES'),
decode(bitand(m.properties, 65536), 65536, 'NO', 'YES'),
decode(bitand(m.properties, 131072), 131072, 'YES', 'NO'),
decode(bitand(nvl(m.xflags,0), 1), 1, 'YES', 'NO')
from sys.user$ u, sys."_CURRENT_EDITION_OBJ" o, sys.type$ t, sys.method$ m
where o.owner# = u.user#
and o.oid$ = m.toid
and o.subname IS NULL -- get the latest version only
and o.type# <> 10 -- must not be invalid
and bitand(t.properties, 2048) = 0 -- not system-generated
and t.toid = m.toid
and t.version# = m.version#
and t.externtype < 5
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_SQLJ_TYPE_METHODS is
'Description of methods of types accessible to the user'
/
comment on column ALL_SQLJ_TYPE_METHODS.OWNER is
'Owner of the type'
/
comment on column ALL_SQLJ_TYPE_METHODS.TYPE_NAME is
'Name of the type'
/
comment on column ALL_SQLJ_TYPE_METHODS.METHOD_NAME is
'Name of the method'
/
comment on column ALL_SQLJ_TYPE_METHODS.EXTERNAL_VAR_NAME is
'Name of the external variable'
/
comment on column ALL_SQLJ_TYPE_METHODS.METHOD_NO is
'Method number for distinguishing overloaded method (not to be used as ID number)'
/
comment on column ALL_SQLJ_TYPE_METHODS.METHOD_TYPE is
'Type of the method'
/
comment on column ALL_SQLJ_TYPE_METHODS.PARAMETERS is
'Number of parameters to the method'
/
comment on column ALL_SQLJ_TYPE_METHODS.RESULTS is
'Number of results returned by the method'
/
comment on column ALL_SQLJ_TYPE_METHODS.FINAL is
'Is the method final ?'
/
comment on column ALL_SQLJ_TYPE_METHODS.INSTANTIABLE is
'Is the method instantiable ?'
/
comment on column ALL_SQLJ_TYPE_METHODS.OVERRIDING is
'Is the method overriding a supertype method ?'
/
comment on column ALL_SQLJ_TYPE_METHODS.INHERITED is
'Is the method inherited from the supertype ?'
/
create or replace public synonym ALL_SQLJ_TYPE_METHODS
for ALL_SQLJ_TYPE_METHODS
/
grant select on ALL_SQLJ_TYPE_METHODS to PUBLIC with grant option
/
create or replace view DBA_SQLJ_TYPE_METHODS
(OWNER, TYPE_NAME, METHOD_NAME, EXTERNAL_VAR_NAME, METHOD_NO, METHOD_TYPE,
PARAMETERS, RESULTS, FINAL, INSTANTIABLE, OVERRIDING, INHERITED)
as
select u.name, o.name, m.name, m.externVarName, m.method#,
decode(bitand(m.properties, 512), 512, 'MAP',
decode(bitand(m.properties, 2048), 2048, 'ORDER', 'PUBLIC')),
m.parameters#, m.results,
decode(bitand(m.properties, 8), 8, 'NO', 'YES'),
decode(bitand(m.properties, 65536), 65536, 'NO', 'YES'),
decode(bitand(m.properties, 131072), 131072, 'YES', 'NO'),
decode(bitand(nvl(m.xflags,0), 1), 1, 'YES', 'NO')
from sys.user$ u, sys."_CURRENT_EDITION_OBJ" o, sys.type$ t, sys.method$ m
where o.owner# = u.user#
and o.oid$ = m.toid
and o.subname IS NULL -- get the latest version only
and o.type# <> 10 -- must not be invalid
and bitand(t.properties, 2048) = 0 -- not system-generated
and t.toid = m.toid
and t.version# = m.version#
and t.externtype < 5
/
comment on table DBA_SQLJ_TYPE_METHODS is
'Description of methods of all types in the database'
/
comment on column DBA_SQLJ_TYPE_METHODS.OWNER is
'Owner of the type'
/
comment on column DBA_SQLJ_TYPE_METHODS.TYPE_NAME is
'Name of the type'
/
comment on column DBA_SQLJ_TYPE_METHODS.METHOD_NAME is
'Name of the method'
/
comment on column DBA_SQLJ_TYPE_METHODS.EXTERNAL_VAR_NAME is
'Name of the external variable'
/
comment on column DBA_SQLJ_TYPE_METHODS.METHOD_NO is
'Method number for distinguishing overloaded method (not to be used as ID number)'
/
comment on column DBA_SQLJ_TYPE_METHODS.METHOD_TYPE is
'Type of the method'
/
comment on column DBA_SQLJ_TYPE_METHODS.PARAMETERS is
'Number of parameters to the method'
/
comment on column DBA_SQLJ_TYPE_METHODS.RESULTS is
'Number of results returned by the method'
/
comment on column DBA_SQLJ_TYPE_METHODS.FINAL is
'Is the method final ?'
/
comment on column DBA_SQLJ_TYPE_METHODS.INSTANTIABLE is
'Is the method instantiable ?'
/
comment on column DBA_SQLJ_TYPE_METHODS.OVERRIDING is
'Is the method overriding a supertype method ?'
/
comment on column DBA_SQLJ_TYPE_METHODS.INHERITED is
'Is the method inherited from the supertype ?'
/
create or replace public synonym DBA_SQLJ_TYPE_METHODS
for DBA_SQLJ_TYPE_METHODS
/
grant select on DBA_SQLJ_TYPE_METHODS to select_catalog_role
/
-- Gives all object tables and columns in 8.0 image format.
create or replace view DBA_OLDIMAGE_COLUMNS
(OWNER, TABLE_NAME, COLUMN_NAME)
as
select u.name, o.name,
decode(c.name, 'SYS_NC_ROWINFO$', 'OBJECT TABLE', c.name)
from sys.user$ u, sys."_CURRENT_EDITION_OBJ" o, sys.col$ c, sys.coltype$ t
where o.type# = 2 and /* show only tables */
o.owner# = u.user# and
o.obj# = c.obj# and
o.obj# = t.obj# and
c.intcol# = t.intcol# and
/* do not show attribute columns. If the attribute is in 8.0 image, that
means the whole column is in 8.0 image. Now, this will still show
top level ADT columns in an object table, which is redundant. */
bitand(c.property, 1) = 0 and
bitand(t.flags, 128) <> 0;
comment on table DBA_OLDIMAGE_COLUMNS is
'Gives all object tables and columns in old (8.0) image format'
/
comment on column DBA_OLDIMAGE_COLUMNS.OWNER is
'Owner of the table'
/
comment on column DBA_OLDIMAGE_COLUMNS.TABLE_NAME is
'Name of the table'
/
comment on column DBA_OLDIMAGE_COLUMNS.COLUMN_NAME is
'Name of the top-level column'
/
create or replace public synonym DBA_OLDIMAGE_COLUMNS for
DBA_OLDIMAGE_COLUMNS
/
grant select on DBA_OLDIMAGE_COLUMNS to select_catalog_role
/
-- user version
create or replace view USER_OLDIMAGE_COLUMNS
(OWNER, TABLE_NAME, COLUMN_NAME)
as
select u.name, o.name,
decode(c.name, 'SYS_NC_ROWINFO$', 'OBJECT TABLE', c.name)
from sys.user$ u, sys."_CURRENT_EDITION_OBJ" o, sys.col$ c, sys.coltype$ t
where o.type# = 2 and /* show only tables */
o.owner# = userenv('SCHEMAID') and
o.owner# = u.user# and
o.obj# = c.obj# and
o.obj# = t.obj# and
c.intcol# = t.intcol# and
/* do not show attribute columns. If the attribute is in 8.0 image, that
means the whole column is in 8.0 image. Now, this will still show
top level ADT columns in an object table, which is redundant. */
bitand(c.property, 1) = 0 and
bitand(t.flags, 128) <> 0;
comment on table USER_OLDIMAGE_COLUMNS is
'Gives all object tables and columns in old (8.0) image format'
/
comment on column USER_OLDIMAGE_COLUMNS.OWNER is
'Owner of the table'
/
comment on column USER_OLDIMAGE_COLUMNS.TABLE_NAME is
'Name of the table'
/
comment on column USER_OLDIMAGE_COLUMNS.COLUMN_NAME is
'Name of the top-level column'
/
create or replace public synonym USER_OLDIMAGE_COLUMNS for
USER_OLDIMAGE_COLUMNS
/
grant select on USER_OLDIMAGE_COLUMNS to public with grant option
/