VIEW: SYS.KU$_STRMCOL_VIEW

Source

View Text - Preformatted

select c.obj#, c.col#, c.intcol#, c.segcol#,
         -- Column sortkey: in principle we want to sort by segcol#, but
         -- segcol# for xmltype is 0 so replace it with the segcol# of its
         -- underlying lob or object rel column that contains the actual
         -- data.  This query needs to be identical to the one for the
         -- col_sorkey column in ku$_prim_column_view, ku$_pcolumn_view and
         -- ku$_column_view in order to ensure that lob columns are ordered
         -- identically when writing to and reading from dump files
         -- (bug# 12998987, 17627666).
         case when (c.segcol# = 0 and c.type# = 58) then
          NVL((select cc.segcol# from col$ cc, opqtype$ opq
              where opq.obj#=c.obj#
                and opq.intcol#=c.intcol#
                and opq.type=1
                and cc.intcol#=opq.lobcol    -- xmltype stored as lob
                and cc.obj#=c.obj#),
                (NVL((select cc.segcol# from col$ cc, opqtype$ opq
                      where opq.obj#=c.obj#
                        and opq.intcol#=c.intcol#
                        and opq.type=1
                        and cc.intcol#=opq.objcol  -- xmltype stored obj rel
                        and bitand(opq.flags,1)=1
                        and cc.obj#=c.obj#),0)))
          else c.segcol#
         end,
         case c.col# when c.intcol# then c.intcol#
                     when 0 then c.intcol#
          else sys.dbms_metadata_util.get_base_intcol_num(c.obj#,c.col#,
                                                          c.intcol#,c.type#)
         end,
        case c.col# when 0 then 0
          else
            sys.dbms_metadata_util.get_base_col_type(c.obj#,c.col#,
                                                          c.intcol#,c.type#)
         end,
         -- get column  properties
         sys.dbms_metadata_util.get_col_property(c.obj#,c.intcol#),
         trunc(c.property / power(2,32)),
         c.name,
         decode(bitand(c.property,1024),0,
         (select a.name from attrcol$ a where
                        a.obj#=c.obj# and a.intcol#=c.intcol#),
           (select a.name from attrcol$ a where c.intcol#>1 and
                        a.obj#=c.obj# and a.intcol#=c.intcol#-1)),
         c.type#, c.length,
         c.precision#, c.scale, c.null$,
         c.charsetid, c.charsetform, c.spare3,
         -- get lob property if type# = 112 (DTYCLOB)
         decode(c.type#,112,
                 sys.dbms_metadata_util.get_lob_property(c.obj#,c.intcol#),
                 null),
         -- get type metadata if type# = 121 (DTYADT)
         --                              123 (DTYNAR)
         --                               58 (DTYOPQ)
         ( select value(ctv) from ku$_strmcoltype_view ctv
                     where c.obj#  = ctv.obj_num
                     and   c.intcol# = ctv.intcol_num
           and   c.type# in (121,123,58) ),
        case c.col# when c.intcol# then NULL
                    when 0 then NULL
          else
            sys.dbms_metadata_util.get_base_col_name(c.obj#,c.col#,
                                                          c.intcol#,c.type#)
         end,
         -- If column has the properties ((ADT attribute, hidden, system
         -- generated) or (type id, ADT attribute, hidden)), then
         -- this column may be part of an unpacked anydata type.
         case when (bitand(c.property,289) = 289  or
                    bitand(c.property,33554465) = 33554465) then
            sys.dbms_metadata_util.get_attrname2(c.obj#, c.intcol#, c.col#)
          else
            NULL
         end
  from col$ c

View Text - HTML Formatted

SELECT C.OBJ#
, C.COL#
, C.INTCOL#
, C.SEGCOL#
, -- COLUMN SORTKEY: IN PRINCIPLE WE WANT TO SORT BY SEGCOL#
, BUT -- SEGCOL# FOR XMLTYPE IS 0 SO REPLACE IT WITH THE SEGCOL# OF ITS -- UNDERLYING LOB OR OBJECT REL COLUMN THAT CONTAINS THE ACTUAL -- DATA. THIS QUERY NEEDS TO BE IDENTICAL TO THE ONE FOR THE -- COL_SORKEY COLUMN IN KU$_PRIM_COLUMN_VIEW
, KU$_PCOLUMN_VIEW AND -- KU$_COLUMN_VIEW IN ORDER TO ENSURE THAT LOB COLUMNS ARE ORDERED -- IDENTICALLY WHEN WRITING TO
AND READING
FROM DUMP FILES -- (BUG# 12998987
, 17627666). CASE WHEN (C.SEGCOL# = 0
AND C.TYPE# = 58) THEN NVL((SELECT CC.SEGCOL#
FROM COL$ CC
, OPQTYPE$ OPQ
WHERE OPQ.OBJ#=C.OBJ#
AND OPQ.INTCOL#=C.INTCOL#
AND OPQ.TYPE=1
AND CC.INTCOL#=OPQ.LOBCOL -- XMLTYPE STORED AS LOB
AND CC.OBJ#=C.OBJ#)
, (NVL((SELECT CC.SEGCOL#
FROM COL$ CC
, OPQTYPE$ OPQ
WHERE OPQ.OBJ#=C.OBJ#
AND OPQ.INTCOL#=C.INTCOL#
AND OPQ.TYPE=1
AND CC.INTCOL#=OPQ.OBJCOL -- XMLTYPE STORED OBJ REL
AND BITAND(OPQ.FLAGS
, 1)=1
AND CC.OBJ#=C.OBJ#)
, 0))) ELSE C.SEGCOL# END
, CASE C.COL# WHEN C.INTCOL# THEN C.INTCOL# WHEN 0 THEN C.INTCOL# ELSE SYS.DBMS_METADATA_UTIL.GET_BASE_INTCOL_NUM(C.OBJ#
, C.COL#
, C.INTCOL#
, C.TYPE#) END
, CASE C.COL# WHEN 0 THEN 0 ELSE SYS.DBMS_METADATA_UTIL.GET_BASE_COL_TYPE(C.OBJ#
, C.COL#
, C.INTCOL#
, C.TYPE#) END
, -- GET COLUMN PROPERTIES SYS.DBMS_METADATA_UTIL.GET_COL_PROPERTY(C.OBJ#
, C.INTCOL#)
, TRUNC(C.PROPERTY / POWER(2
, 32))
, C.NAME
, DECODE(BITAND(C.PROPERTY
, 1024)
, 0
, (SELECT A.NAME
FROM ATTRCOL$ A WHERE A.OBJ#=C.OBJ#
AND A.INTCOL#=C.INTCOL#)
, (SELECT A.NAME
FROM ATTRCOL$ A
WHERE C.INTCOL#>1 AND A.OBJ#=C.OBJ#
AND A.INTCOL#=C.INTCOL#-1))
, C.TYPE#
, C.LENGTH
, C.PRECISION#
, C.SCALE
, C.NULL$
, C.CHARSETID
, C.CHARSETFORM
, C.SPARE3
, -- GET LOB PROPERTY IF TYPE# = 112 (DTYCLOB) DECODE(C.TYPE#
, 112
, SYS.DBMS_METADATA_UTIL.GET_LOB_PROPERTY(C.OBJ#
, C.INTCOL#)
, NULL)
, -- GET TYPE METADATA IF TYPE# = 121 (DTYADT) -- 123 (DTYNAR) -- 58 (DTYOPQ) ( SELECT VALUE(CTV)
FROM KU$_STRMCOLTYPE_VIEW CTV
WHERE C.OBJ# = CTV.OBJ_NUM
AND C.INTCOL# = CTV.INTCOL_NUM
AND C.TYPE# IN (121
, 123
, 58) )
, CASE C.COL# WHEN C.INTCOL# THEN NULL WHEN 0 THEN NULL ELSE SYS.DBMS_METADATA_UTIL.GET_BASE_COL_NAME(C.OBJ#
, C.COL#
, C.INTCOL#
, C.TYPE#) END
, -- IF COLUMN HAS THE PROPERTIES ((ADT ATTRIBUTE
, HIDDEN
, SYSTEM -- GENERATED) OR (TYPE ID
, ADT ATTRIBUTE
, HIDDEN))
, THEN -- THIS COLUMN MAY BE PART OF AN UNPACKED ANYDATA TYPE. CASE WHEN (BITAND(C.PROPERTY
, 289) = 289 OR BITAND(C.PROPERTY
, 33554465) = 33554465) THEN SYS.DBMS_METADATA_UTIL.GET_ATTRNAME2(C.OBJ#
, C.INTCOL#
, C.COL#) ELSE NULL END
FROM COL$ C