For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
SQL> select text from all_source where owner = 'SYS' and name = 'DBMS_TYPES' and type = 'PACKAGE' / TEXT -------------------------------------------------------------------------------- PACKAGE dbms_types AS TYPECODE_DATE PLS_INTEGER := 12; TYPECODE_NUMBER PLS_INTEGER := 2; TYPECODE_RAW PLS_INTEGER := 95; TYPECODE_CHAR PLS_INTEGER := 96; TYPECODE_VARCHAR2 PLS_INTEGER := 9; TYPECODE_VARCHAR PLS_INTEGER := 1; TYPECODE_MLSLABEL PLS_INTEGER := 105; TYPECODE_BLOB PLS_INTEGER := 113; TYPECODE_BFILE PLS_INTEGER := 114; TYPECODE_CLOB PLS_INTEGER := 112; TYPECODE_CFILE PLS_INTEGER := 115; TYPECODE_TIMESTAMP PLS_INTEGER := 187; TYPECODE_TIMESTAMP_TZ PLS_INTEGER := 188; TYPECODE_TIMESTAMP_LTZ PLS_INTEGER := 232; TYPECODE_INTERVAL_YM PLS_INTEGER := 189; TYPECODE_INTERVAL_DS PLS_INTEGER := 190; TYPECODE_REF PLS_INTEGER := 110; TYPECODE_OBJECT PLS_INTEGER := 108; TYPECODE_VARRAY PLS_INTEGER := 247; /* COLLECTION TYPE */ TYPECODE_TABLE PLS_INTEGER := 248; /* COLLECTION TYPE */ TYPECODE_NAMEDCOLLECTION PLS_INTEGER := 122; TYPECODE_OPAQUE PLS_INTEGER := 58; /* OPAQUE TYPE */ /* NOTE: These typecodes are for use in AnyData api only and are short forms for the corresponding char typecodes with a charset form of SQLCS_NCHAR. */ TYPECODE_NCHAR PLS_INTEGER := 286; TYPECODE_NVARCHAR2 PLS_INTEGER := 287; TYPECODE_NCLOB PLS_INTEGER := 288; /* Typecodes for Binary Float, Binary Double and Urowid. */ TYPECODE_BFLOAT PLS_INTEGER := 100; TYPECODE_BDOUBLE PLS_INTEGER := 101; TYPECODE_UROWID PLS_INTEGER := 104; SUCCESS PLS_INTEGER := 0; NO_DATA PLS_INTEGER := 100; /* Exceptions */ invalid_parameters EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_parameters, -22369); incorrect_usage EXCEPTION; PRAGMA EXCEPTION_INIT(incorrect_usage, -22370); type_mismatch EXCEPTION; PRAGMA EXCEPTION_INIT(type_mismatch, -22626); END dbms_types; 51 rows selected.
SQL> set long 10000 SQL> set head off SQL> select text from dba_views where view_name = 'DBA_TAB_COLS' 2 / select u.name, o.name, c.name, decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'), 2, decode(c.scale, null, decode(c.precision#, null, 'NUMBER', 'FLOAT'), 'NUMBER'), 8, 'LONG', 9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'), 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 58, nvl2(ac.synobj#, (select o.name from obj$ o where o.obj#=ac.synobj#), ot.name), 69, 'ROWID', 96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'), 100, 'BINARY_FLOAT', 101, 'BINARY_DOUBLE', 105, 'MLSLABEL', 106, 'MLSLABEL', 111, nvl2(ac.synobj#, (select o.name from obj$ o where o.obj#=ac.synobj#), ot.name), 112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'), 113, 'BLOB', 114, 'BFILE', 115, 'CFILE', 121, nvl2(ac.synobj#, (select o.name from obj$ o where o.obj#=ac.synobj#), ot.name), 122, nvl2(ac.synobj#, (select o.name from obj$ o where o.obj#=ac.synobj#), ot.name), 123, nvl2(ac.synobj#, (select o.name from obj$ o where o.obj#=ac.synobj#), ot.name), 178, 'TIME(' ||c.scale|| ')', 179, 'TIME(' ||c.scale|| ')' || ' WITH TIME ZONE', 180, 'TIMESTAMP(' ||c.scale|| ')', 181, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH TIME ZONE', 231, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH LOCAL TIME ZONE', 182, 'INTERVAL YEAR(' ||c.precision#||') TO MONTH', 183, 'INTERVAL DAY(' ||c.precision#||') TO SECOND(' || c.scale || ')', 208, 'UROWID', 'UNDEFINED'), decode(c.type#, 111, 'REF'), nvl2(ac.synobj#, (select u.name from user$ u, obj$ o where o.owner#=u.user# and o.obj#=ac.synobj#), ut.name), c.length, c.precision#, c.scale, decode(sign(c.null$),-1,'D', 0, 'Y', 'N'), decode(c.col#, 0, to_number(null), c.col#), c.deflength, c.default$, h.distcnt, h.lowval, h.hival, h.density, h.null_cnt, case when nvl(h.distcnt,0) = 0 then h.distcnt when h.row_cnt = 0 then 1 when (h.bucket_cnt > 255 or (h.bucket_cnt > h.distcnt and h.row_cnt = h.distcnt and h.density*h.bucket_cnt <= 1)) then h.row_cnt else h.bucket_cnt end, h.timestamp#, h.sample_size, decode(c.charsetform, 1, 'CHAR_CS', 2, 'NCHAR_CS', 3, NLS_CHARSET_NAME(c.charsetid), 4, 'ARG:'||c.charsetid), decode(c.charsetid, 0, to_number(NULL), nls_charset_decl_len(c.length, c.charsetid)), decode(bitand(h.spare2, 2), 2, 'YES', 'NO'), decode(bitand(h.spare2, 1), 1, 'YES', 'NO'), h.avgcln, c.spare3, decode(c.type#, 1, decode(bitand(c.property, 8388608), 0, 'B', 'C'), 96, decode(bitand(c.property, 8388608), 0, 'B', 'C'), null), decode(bitand(ac.flags, 128), 128, 'YES', 'NO'), decode(o.status, 1, decode(bitand(ac.flags, 256), 256, 'NO', 'YES'), decode(bitand(ac.flags, 2), 2, 'NO', decode(bitand(ac.flags, 4), 4, 'NO', decode(bitand(ac.flags, 8), 8, 'NO', 'N/A')))), decode(c.property, 0, 'NO', decode(bitand(c.property, 32), 32, 'YES', 'NO')), decode(c.property, 0, 'NO', decode(bitand(c.property, 8), 8, 'YES', 'NO')), decode(c.segcol#, 0, to_number(null), c.segcol#), c.intcol#, case when nvl(h.row_cnt,0) = 0 then 'NONE' when (h.bucket_cnt > 255 or (h.bucket_cnt > h.distcnt and h.row_cnt = h.distcnt and h.density*h.bucket_cnt <= 1)) then 'FREQUENCY' else 'HEIGHT BALANCED' end, decode(bitand(c.property, 1024), 1024, (select decode(bitand(cl.property, 1), 1, rc.name, cl.name) from sys.col$ cl, attrcol$ rc where cl.intcol# = c.intcol#-1 and cl.obj# = c.obj# and c.obj# = rc.obj#(+) and cl.intcol# = rc.intcol#(+)), decode(bitand(c.property, 1), 0, c.name, (select tc.name from sys.attrcol$ tc where c.obj# = tc.obj# and c.intcol# = tc.intcol#))) from sys.col$ c, sys.obj$ o, sys.hist_head$ h, sys.user$ u, sys.coltype$ ac, sys.obj$ ot, sys.user$ ut where o.obj# = c.obj# and o.owner# = u.user# and c.obj# = h.obj#(+) and c.intcol# = h.intcol#(+) and c.obj# = ac.obj#(+) and c.intcol# = ac.intcol#(+) and ac.toid = ot.oid$(+) and ot.type#(+) = 13 and ot.owner# = ut.user#(+) and (o.type# in (3, 4) /* cluster, view */ or (o.type# = 2 /* tables, excluding iot - overflow and nested tables */ and not exists (select null from sys.tab$ t where t.obj# = o.obj# and (bitand(t.property, 512) = 512 or bitand(t.property, 8192) = 8192)))) SQL>