VIEW: SYS.COL

Source

View Text - Preformatted

select t.name, c.col#, 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',
                         69, 'ROWID',
                         96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
                         100, 'BINARY_FLOAT',
                         101, 'BINARY_DOUBLE',
                         105, 'MLSLABEL',
                         106, 'MLSLABEL',
                         111, 'REF '||'"'||ut.name||'"'||'.'||'"'||ot.name||'"',
                         112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
                         113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
                         121, '"'||ut.name||'"'||'.'||'"'||ot.name||'"',
                         122, '"'||ut.name||'"'||'.'||'"'||ot.name||'"',
                         123, '"'||ut.name||'"'||'.'||'"'||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'),
         c.length, c.scale, c.precision#,
         decode(sign(c.null$),-1,'NOT NULL - DISABLED', 0, 'NULL',
        'NOT NULL'), c.default$,
         decode(c.charsetform, 1, 'CHAR_CS',
                               2, 'NCHAR_CS',
                               3, NLS_CHARSET_NAME(c.charsetid),
                               4, 'ARG:'||c.charsetid)
  from  sys.col$ c, sys."_CURRENT_EDITION_OBJ" t, sys.coltype$ ac,
        sys.obj$ ot, sys."_BASE_USER" ut
  where t.obj# = c.obj#
  and   t.type# in (2, 3, 4)
  and   t.owner# = userenv('SCHEMAID')
  and   bitand(c.property, 32) = 0 /* not hidden column */
  and   c.obj# = ac.obj#(+)
  and   c.intcol# = ac.intcol#(+)
  and   ac.toid = ot.oid$(+)
  and   ot.owner# = ut.user#(+)

View Text - HTML Formatted

SELECT T.NAME
, C.COL#
, 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'
, 69
, 'ROWID'
, 96
, DECODE(C.CHARSETFORM
, 2
, 'NCHAR'
, 'CHAR')
, 100
, 'BINARY_FLOAT'
, 101
, 'BINARY_DOUBLE'
, 105
, 'MLSLABEL'
, 106
, 'MLSLABEL'
, 111
, 'REF '||'"'||UT.NAME||'"'||'.'||'"'||OT.NAME||'"'
, 112
, DECODE(C.CHARSETFORM
, 2
, 'NCLOB'
, 'CLOB')
, 113
, 'BLOB'
, 114
, 'BFILE'
, 115
, 'CFILE'
, 121
, '"'||UT.NAME||'"'||'.'||'"'||OT.NAME||'"'
, 122
, '"'||UT.NAME||'"'||'.'||'"'||OT.NAME||'"'
, 123
, '"'||UT.NAME||'"'||'.'||'"'||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')
, C.LENGTH
, C.SCALE
, C.PRECISION#
, DECODE(SIGN(C.NULL$)
, -1
, 'NOT NULL - DISABLED'
, 0
, 'NULL'
, 'NOT NULL')
, C.DEFAULT$
, DECODE(C.CHARSETFORM
, 1
, 'CHAR_CS'
, 2
, 'NCHAR_CS'
, 3
, NLS_CHARSET_NAME(C.CHARSETID)
, 4
, 'ARG:'||C.CHARSETID)
FROM SYS.COL$ C
, SYS."_CURRENT_EDITION_OBJ" T
, SYS.COLTYPE$ AC
, SYS.OBJ$ OT
, SYS."_BASE_USER" UT
WHERE T.OBJ# = C.OBJ#
AND T.TYPE# IN (2
, 3
, 4)
AND T.OWNER# = USERENV('SCHEMAID')
AND BITAND(C.PROPERTY
, 32) = 0 /* NOT HIDDEN COLUMN */
AND C.OBJ# = AC.OBJ#(+)
AND C.INTCOL# = AC.INTCOL#(+)
AND AC.TOID = OT.OID$(+)
AND OT.OWNER# = UT.USER#(+)