VIEW: SYS.REPCAT_REPFLAVOR_COLUMNS

Source

View Text - Preformatted

SELECT f.fname, fo.gname, rc.sname, rc.oname, rc.lcname,
         decode(rc.ctype,
           1, decode(rc.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
           2, 'NUMBER',
          12, 'DATE',
          23, decode(utl_raw.bit_and(utl_raw.substr(rc.property, 1, 1), '04'),
                  '04', rc.ctype_name,
               -- for soidref_fk_attr we want to display the real column type
               decode(utl_raw.bit_and(utl_raw.substr(rc.property, 2, 1), '02'),
                   '02', rc.ctype_name,
                   'RAW')),
          58, rc.ctype_name,
          69, 'ROWID',
          96, decode(rc.charsetform, 2, 'NCHAR', 'CHAR'),
         112, NVL(rc.ctype_name, decode(rc.charsetform, 2, 'NCLOB', 'CLOB')),
         113, 'BLOB',
         111, rc.ctype_name,
         121, rc.ctype_name,
         122, rc.ctype_name,
         123, rc.ctype_name,
             'UNDEFINED'),
         rc.pos, fo.gowner,
         rc.toid,
         rc.ctype_owner,
         RAWTOHEX(rc.hashcode),
         DECODE(rc.ctype,
                111, 'REF',
                23,
                decode(utl_raw.bit_and(utl_raw.substr(rc.property,2,1),'02'),
                       '02', 'REF')
                ),
         decode(rc.ctype, 23,
               -- nested table column SETID (in the parent table)
               decode(utl_raw.bit_and(utl_raw.substr(rc.property, 1, 1), '08'),
                      '08',decode(rc.top, rc.lcname, NULL, rc.top), rc.top),
               -- for XMLType storage column
               112, decode(rc.top, rc.lcname, NULL, rc.top),
              rc.top),
         rc.cname, rc.property
    FROM system.repcat$_repcolumn rc, system.repcat$_flavor_objects fo,
         system.repcat$_flavors f, system.repcat$_repobject ro
    WHERE f.flavor_id = fo.flavor_id
      AND f.gname     = fo.gname
      AND f.gowner    = fo.gowner
      AND rc.sname    = fo.sname AND rc.oname = fo.oname
      AND fo.type     in (2, -1)
      AND ro.sname    = rc.sname
      AND ro.oname    = rc.oname
      AND ro.type     = rc.type
      AND ro.gname    = fo.gname
      AND ro.gowner   = fo.gowner
      AND rc.pos IS NOT NULL
      AND
        ((mod(rc.pos-1,8) < 4
         AND fo.columns_present IS NOT NULL
         -- the following AND clauses are necessary to avoid
         -- invoking utl_raw.substr with fo.columns_present from one
         -- object while rc.pos is from another object.
         AND fo.sname = rc.sname
         AND fo.oname = rc.oname
         AND fo.type = rc.type
         AND utl_raw.bit_and(utl_raw.substr(fo.columns_present,
                                             floor((rc.pos-1)/8)+1, 1),
                              to_char(power(2, mod(rc.pos-1,8))))
                             != '00')
         OR
         (mod(rc.pos-1,8) >= 4
         AND fo.columns_present IS NOT NULL
         -- the following AND clauses are necessary to avoid
         -- invoking utl_raw.substr with fo.columns_present from one
         -- object while rc.pos is from another object.
         AND fo.sname = rc.sname
         AND fo.oname = rc.oname
         AND fo.type = rc.type
         AND utl_raw.bit_and(utl_raw.substr(fo.columns_present,
                                             floor((rc.pos-1)/8)+1, 1),
                              to_char(10*power(2, mod(rc.pos-1,8)-4)))
                             != '00'))

View Text - HTML Formatted

SELECT F.FNAME
, FO.GNAME
, RC.SNAME
, RC.ONAME
, RC.LCNAME
, DECODE(RC.CTYPE
, 1
, DECODE(RC.CHARSETFORM
, 2
, 'NVARCHAR2'
, 'VARCHAR2')
, 2
, 'NUMBER'
, 12
, 'DATE'
, 23
, DECODE(UTL_RAW.BIT_AND(UTL_RAW.SUBSTR(RC.PROPERTY
, 1
, 1)
, '04')
, '04'
, RC.CTYPE_NAME
, -- FOR SOIDREF_FK_ATTR WE WANT TO DISPLAY THE REAL COLUMN TYPE DECODE(UTL_RAW.BIT_AND(UTL_RAW.SUBSTR(RC.PROPERTY
, 2
, 1)
, '02')
, '02'
, RC.CTYPE_NAME
, 'RAW'))
, 58
, RC.CTYPE_NAME
, 69
, 'ROWID'
, 96
, DECODE(RC.CHARSETFORM
, 2
, 'NCHAR'
, 'CHAR')
, 112
, NVL(RC.CTYPE_NAME
, DECODE(RC.CHARSETFORM
, 2
, 'NCLOB'
, 'CLOB'))
, 113
, 'BLOB'
, 111
, RC.CTYPE_NAME
, 121
, RC.CTYPE_NAME
, 122
, RC.CTYPE_NAME
, 123
, RC.CTYPE_NAME
, 'UNDEFINED')
, RC.POS
, FO.GOWNER
, RC.TOID
, RC.CTYPE_OWNER
, RAWTOHEX(RC.HASHCODE)
, DECODE(RC.CTYPE
, 111
, 'REF'
, 23
, DECODE(UTL_RAW.BIT_AND(UTL_RAW.SUBSTR(RC.PROPERTY
, 2
, 1)
, '02')
, '02'
, 'REF') )
, DECODE(RC.CTYPE
, 23
, -- NESTED TABLE COLUMN SETID (IN THE PARENT TABLE) DECODE(UTL_RAW.BIT_AND(UTL_RAW.SUBSTR(RC.PROPERTY
, 1
, 1)
, '08')
, '08'
, DECODE(RC.TOP
, RC.LCNAME
, NULL
, RC.TOP)
, RC.TOP)
, -- FOR XMLTYPE STORAGE COLUMN 112
, DECODE(RC.TOP
, RC.LCNAME
, NULL
, RC.TOP)
, RC.TOP)
, RC.CNAME
, RC.PROPERTY
FROM SYSTEM.REPCAT$_REPCOLUMN RC
, SYSTEM.REPCAT$_FLAVOR_OBJECTS FO
, SYSTEM.REPCAT$_FLAVORS F
, SYSTEM.REPCAT$_REPOBJECT RO
WHERE F.FLAVOR_ID = FO.FLAVOR_ID
AND F.GNAME = FO.GNAME
AND F.GOWNER = FO.GOWNER
AND RC.SNAME = FO.SNAME
AND RC.ONAME = FO.ONAME
AND FO.TYPE IN (2
, -1)
AND RO.SNAME = RC.SNAME
AND RO.ONAME = RC.ONAME
AND RO.TYPE = RC.TYPE
AND RO.GNAME = FO.GNAME
AND RO.GOWNER = FO.GOWNER
AND RC.POS IS NOT NULL AND ((MOD(RC.POS-1
, 8) < 4
AND FO.COLUMNS_PRESENT IS NOT NULL -- THE FOLLOWING
AND CLAUSES ARE NECESSARY TO AVOID -- INVOKING UTL_RAW.SUBSTR WITH FO.COLUMNS_PRESENT
FROM ONE -- OBJECT WHILE RC.POS IS
FROM ANOTHER OBJECT.
AND FO.SNAME = RC.SNAME
AND FO.ONAME = RC.ONAME
AND FO.TYPE = RC.TYPE
AND UTL_RAW.BIT_AND(UTL_RAW.SUBSTR(FO.COLUMNS_PRESENT
, FLOOR((RC.POS-1)/8)+1
, 1)
, TO_CHAR(POWER(2
, MOD(RC.POS-1
, 8)))) != '00') OR (MOD(RC.POS-1
, 8) >= 4
AND FO.COLUMNS_PRESENT IS NOT NULL -- THE FOLLOWING
AND CLAUSES ARE NECESSARY TO AVOID -- INVOKING UTL_RAW.SUBSTR WITH FO.COLUMNS_PRESENT
FROM ONE -- OBJECT WHILE RC.POS IS
FROM ANOTHER OBJECT.
AND FO.SNAME = RC.SNAME
AND FO.ONAME = RC.ONAME
AND FO.TYPE = RC.TYPE
AND UTL_RAW.BIT_AND(UTL_RAW.SUBSTR(FO.COLUMNS_PRESENT
, FLOOR((RC.POS-1)/8)+1
, 1)
, TO_CHAR(10*POWER(2
, MOD(RC.POS-1
, 8)-4))) != '00'))