eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

VIEW: SYS.REPCAT_REPCOLUMN_BASE

Source

View Text - Preformatted


select r.sname,
       r.oname,
       decode(r.type,
         -1, 'SNAPSHOT',
          1, 'INDEX',
          2, 'TABLE',
          4, 'VIEW',
          5, 'SYNONYM'),
       r.lcname, -- cname, long column name
       r.id,
       -- we want to leave the pos as NULL for virtual columns
       r.pos,
       -- we want the send and compare bits from the 'real' column
       decode(nvl(r.pos,r2.pos), NULL, NULL,
         decode(utl_raw.bit_and(utl_raw.substr(nvl(r2.flag,
                r.flag), 1, 1), '04'), '00', 'Y','N')),
       decode(nvl(r.pos,r2.pos), NULL, NULL,
         decode(utl_raw.bit_and(utl_raw.substr(nvl(r2.flag,
                r.flag), 1, 1), '08'), '00', 'Y','N')),
       decode(nvl(r.pos,r2.pos), NULL, NULL,
         decode(utl_raw.bit_and(utl_raw.substr(nvl(r2.flag,
                r.flag), 1, 1), '01'), '00', 'Y','N')),
       decode(nvl(r.pos,r2.pos), NULL, NULL,
         decode(utl_raw.bit_and(utl_raw.substr(nvl(r2.flag,
                r.flag), 1, 1), '02'), '00', 'Y','N')),
       decode(r.ctype,
         1, decode(r.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
         2, 'NUMBER',
         12, 'DATE',
         23, decode(utl_raw.bit_and(utl_raw.substr(r.property, 1, 1), '08'),
                    '08', r.ctype_name, 'RAW'),
         58, r.ctype_name,
         69, 'ROWID',
         96, decode(r.charsetform, 2, 'NCHAR', 'CHAR'),
         -- system provided type may be stored as clob, e.g., XMLType
         112, NVL(r.ctype_name, decode(r.charsetform, 2, 'NCLOB', 'CLOB')),
         113, 'BLOB',
         178, 'TIME(' ||r.scale|| ')',
         179, 'TIME(' ||r.scale|| ')' || ' WITH TIME ZONE',
         180, 'TIMESTAMP(' ||r.scale|| ')',
         181, 'TIMESTAMP(' ||r.scale|| ')' || ' WITH TIME ZONE',
         182, 'INTERVAL YEAR(' ||r.precision#||') TO MONTH',
         183, 'INTERVAL DAY(' ||r.precision#||') TO SECOND(' ||r.scale|| ')',
         111, r.ctype_name,
         121, r.ctype_name,
         122, r.ctype_name,
         123, r.ctype_name,
         231, 'TIMESTAMP(' ||r.scale|| ')' || ' WITH LOCAL TIME ZONE',
         'UNDEFINED'),
       r.toid,
       r.ctype_owner,
       RAWTOHEX(r.hashcode),
       r.version#,
       r.ctype,
       decode(r.ctype, 111, 'REF'),                       -- CTYPE_MOD
       decode(nvl(r.pos, r2.pos), NULL, NULL, r.length),
       decode(nvl(r.pos, r2.pos), NULL, NULL, r.precision#),
       decode(nvl(r.pos, r2.pos), NULL, NULL, r.scale),
       decode(nvl(r.pos, r2.pos), NULL, NULL, decode(sign(r.null$),-1,'D',
                                                     0, 'Y', 'N')),
       decode(r.charsetform, 1, 'CHAR_CS',
                             2, 'NCHAR_CS',
                             3, NLS_CHARSET_NAME(r.charsetid),
                             4, 'ARG:'||r.charsetid),
       decode(r.ctype, 23,
               -- nested table column SETID (in the parent table)
               decode(utl_raw.bit_and(utl_raw.substr(r.property, 1, 1), '08'),
                      '08',decode(r.top, r.lcname, NULL, r.top), r.top),
               -- for XMLType storage column
               112, decode(r.top, r.lcname, NULL, r.top),
              r.top),
       r.clength,
       decode(r.ctype,
        1,
        decode(utl_raw.bit_and(utl_raw.substr(r.flag,1,1),'10'),'00','B','C'),
        96,
        decode(utl_raw.bit_and(utl_raw.substr(r.flag,1,1),'10'),'00','B','C'),
        ''),
        r.property,
        -- for soidref_fk we need to select the pos of the real column
        -- in the lpos field so that in the definition of dba_repcolumn we
        -- can say nvl(pos, lpos) showing the pos of the real column rather
        -- than null.
        decode(utl_raw.bit_and(utl_raw.substr(r.property,2,1),'01'),'01',
               r2.pos, null)
from system.repcat$_repcolumn r, system.repcat$_repcolumn r2
where r2.sname (+) = r.sname
and   r2.oname (+) = r.oname
and   r2.lcname (+) = r.lcname
and   r2.id (+) <> r.id
-- we select r2 only for soidref_fk_attr
and utl_raw.bit_and(utl_raw.substr(nvl(r2.property (+),'0000'),2,1),'02')='02'
-- filter out nested table column
and utl_raw.bit_and(utl_raw.substr(r.property, 1, 1), '01') != '01'
-- filter out special opaque type referenced in table opqtype$
and utl_raw.bit_and(utl_raw.substr(r.property, 2, 1), '08') != '08'
-- filter out soidref_fk_attr column
and utl_raw.bit_and(utl_raw.substr(r.property, 2, 1), '02') != '02'

View Text - HTML Formatted

SELECT R.SNAME
, R.ONAME
, DECODE(R.TYPE
, -1
, 'SNAPSHOT'
, 1
, 'INDEX'
, 2
, 'TABLE'
, 4
, 'VIEW'
, 5
, 'SYNONYM')
, R.LCNAME
, -- CNAME
, LONG COLUMN NAME R.ID
, -- WE WANT TO LEAVE THE POS AS NULL FOR VIRTUAL COLUMNS R.POS
, -- WE WANT THE SEND
AND COMPARE BITS
FROM THE 'REAL' COLUMN DECODE(NVL(R.POS
, R2.POS)
, NULL
, NULL
, DECODE(UTL_RAW.BIT_AND(UTL_RAW.SUBSTR(NVL(R2.FLAG
, R.FLAG)
, 1
, 1)
, '04')
, '00'
, 'Y'
, 'N'))
, DECODE(NVL(R.POS
, R2.POS)
, NULL
, NULL
, DECODE(UTL_RAW.BIT_AND(UTL_RAW.SUBSTR(NVL(R2.FLAG
, R.FLAG)
, 1
, 1)
, '08')
, '00'
, 'Y'
, 'N'))
, DECODE(NVL(R.POS
, R2.POS)
, NULL
, NULL
, DECODE(UTL_RAW.BIT_AND(UTL_RAW.SUBSTR(NVL(R2.FLAG
, R.FLAG)
, 1
, 1)
, '01')
, '00'
, 'Y'
, 'N'))
, DECODE(NVL(R.POS
, R2.POS)
, NULL
, NULL
, DECODE(UTL_RAW.BIT_AND(UTL_RAW.SUBSTR(NVL(R2.FLAG
, R.FLAG)
, 1
, 1)
, '02')
, '00'
, 'Y'
, 'N'))
, DECODE(R.CTYPE
, 1
, DECODE(R.CHARSETFORM
, 2
, 'NVARCHAR2'
, 'VARCHAR2')
, 2
, 'NUMBER'
, 12
, 'DATE'
, 23
, DECODE(UTL_RAW.BIT_AND(UTL_RAW.SUBSTR(R.PROPERTY
, 1
, 1)
, '08')
, '08'
, R.CTYPE_NAME
, 'RAW')
, 58
, R.CTYPE_NAME
, 69
, 'ROWID'
, 96
, DECODE(R.CHARSETFORM
, 2
, 'NCHAR'
, 'CHAR')
, -- SYSTEM PROVIDED TYPE MAY BE STORED AS CLOB
, E.G.
, XMLTYPE 112
, NVL(R.CTYPE_NAME
, DECODE(R.CHARSETFORM
, 2
, 'NCLOB'
, 'CLOB'))
, 113
, 'BLOB'
, 178
, 'TIME(' ||R.SCALE|| ')'
, 179
, 'TIME(' ||R.SCALE|| ')' || ' WITH TIME ZONE'
, 180
, 'TIMESTAMP(' ||R.SCALE|| ')'
, 181
, 'TIMESTAMP(' ||R.SCALE|| ')' || ' WITH TIME ZONE'
, 182
, 'INTERVAL YEAR(' ||R.PRECISION#||') TO MONTH'
, 183
, 'INTERVAL DAY(' ||R.PRECISION#||') TO SECOND(' ||R.SCALE|| ')'
, 111
, R.CTYPE_NAME
, 121
, R.CTYPE_NAME
, 122
, R.CTYPE_NAME
, 123
, R.CTYPE_NAME
, 231
, 'TIMESTAMP(' ||R.SCALE|| ')' || ' WITH LOCAL TIME ZONE'
, 'UNDEFINED')
, R.TOID
, R.CTYPE_OWNER
, RAWTOHEX(R.HASHCODE)
, R.VERSION#
, R.CTYPE
, DECODE(R.CTYPE
, 111
, 'REF')
, -- CTYPE_MOD DECODE(NVL(R.POS
, R2.POS)
, NULL
, NULL
, R.LENGTH)
, DECODE(NVL(R.POS
, R2.POS)
, NULL
, NULL
, R.PRECISION#)
, DECODE(NVL(R.POS
, R2.POS)
, NULL
, NULL
, R.SCALE)
, DECODE(NVL(R.POS
, R2.POS)
, NULL
, NULL
, DECODE(SIGN(R.NULL$)
, -1
, 'D'
, 0
, 'Y'
, 'N'))
, DECODE(R.CHARSETFORM
, 1
, 'CHAR_CS'
, 2
, 'NCHAR_CS'
, 3
, NLS_CHARSET_NAME(R.CHARSETID)
, 4
, 'ARG:'||R.CHARSETID)
, DECODE(R.CTYPE
, 23
, -- NESTED TABLE COLUMN SETID (IN THE PARENT TABLE) DECODE(UTL_RAW.BIT_AND(UTL_RAW.SUBSTR(R.PROPERTY
, 1
, 1)
, '08')
, '08'
, DECODE(R.TOP
, R.LCNAME
, NULL
, R.TOP)
, R.TOP)
, -- FOR XMLTYPE STORAGE COLUMN 112
, DECODE(R.TOP
, R.LCNAME
, NULL
, R.TOP)
, R.TOP)
, R.CLENGTH
, DECODE(R.CTYPE
, 1
, DECODE(UTL_RAW.BIT_AND(UTL_RAW.SUBSTR(R.FLAG
, 1
, 1)
, '10')
, '00'
, 'B'
, 'C')
, 96
, DECODE(UTL_RAW.BIT_AND(UTL_RAW.SUBSTR(R.FLAG
, 1
, 1)
, '10')
, '00'
, 'B'
, 'C')
, '')
, R.PROPERTY
, -- FOR SOIDREF_FK WE NEED TO SELECT THE POS OF THE REAL COLUMN -- IN THE LPOS FIELD SO THAT IN THE DEFINITION OF DBA_REPCOLUMN WE -- CAN SAY NVL(POS
, LPOS) SHOWING THE POS OF THE REAL COLUMN RATHER -- THAN NULL. DECODE(UTL_RAW.BIT_AND(UTL_RAW.SUBSTR(R.PROPERTY
, 2
, 1)
, '01')
, '01'
, R2.POS
, NULL) FROM SYSTEM.REPCAT$_REPCOLUMN R
, SYSTEM.REPCAT$_REPCOLUMN R2 WHERE R2.SNAME (+) = R.SNAME AND R2.ONAME (+) = R.ONAME AND R2.LCNAME (+) = R.LCNAME AND R2.ID (+) <> R.ID -- WE SELECT R2 ONLY FOR SOIDREF_FK_ATTR AND UTL_RAW.BIT_AND(UTL_RAW.SUBSTR(NVL(R2.PROPERTY (+)
, '0000')
, 2
, 1)
, '02')='02' -- FILTER OUT NESTED TABLE COLUMN AND UTL_RAW.BIT_AND(UTL_RAW.SUBSTR(R.PROPERTY
, 1
, 1)
, '01') != '01' -- FILTER OUT SPECIAL OPAQUE TYPE REFERENCED IN TABLE OPQTYPE$ AND UTL_RAW.BIT_AND(UTL_RAW.SUBSTR(R.PROPERTY
, 2
, 1)
, '08') != '08' -- FILTER OUT SOIDREF_FK_ATTR COLUMN AND UTL_RAW.BIT_AND(UTL_RAW.SUBSTR(R.PROPERTY
, 2
, 1)
, '02') != '02'