VIEW: SYS.DBA_XML_TAB_COLS

Source

View Text - Preformatted

select u.name, o.name,
   decode(bitand(tc.property, 1), 1, attr.name, tc.name),
   null, null, null,
   case when bitand(opq.flags,69) = 1 then 'OBJECT-RELATIONAL'
        when bitand(opq.flags,69) = 68 then 'BINARY'
   else 'CLOB' end,
   case when bitand(opq.flags,69) = 68 then
       case when bitand(opq.flags,128) = 128 then 'YES' else 'NO' end
   else NULL end,
   case when bitand(opq.flags,69) = 68  then
       case when bitand(opq.flags,256) = 256 then 'NO' else 'YES' end
   else NULL end
from sys.opqtype$ opq, sys.tab$ t, sys.user$ u, sys.obj$ o,
     sys.coltype$ ac, sys.col$ tc, sys.attrcol$ attr
where o.owner# = u.user#
  and o.obj# = t.obj#
  and t.obj# = tc.obj#
  and tc.obj# = ac.obj#
  and tc.intcol# = ac.intcol#
  and tc.obj#    = attr.obj#(+)
  and tc.intcol# = attr.intcol#(+)
  and bitand(tc.property, 512) = 0
  and ac.toid = '00000000000000000000000000020100'
  and tc.intcol# =  opq.intcol#
  and tc.obj# =  opq.obj#
  and bitand(opq.flags,2) = 0
 union all
  select u.name, o.name,
   decode(bitand(tc.property, 1), 1, attr.name, tc.name),
   schm.xmldata.schema_url, schm.xmldata.schema_owner,
decode(xel.xmldata.property.name, null,
        xel.xmldata.property.propref_name.name, xel.xmldata.property.name),
    case when bitand(opq.flags,69) = 1 then 'OBJECT-RELATIONAL'
         when bitand(opq.flags,69) = 68 then 'BINARY'
    else 'CLOB' end,
    case when bitand(opq.flags,69) = 68 then
        case when bitand(opq.flags,128) = 128 then 'YES' else 'NO' end
    else NULL end,
    case when bitand(opq.flags,69) = 68  then
        case when bitand(opq.flags,256) = 256 then 'NO' else 'YES' end
    else NULL end
from xdb.xdb$element xel, xdb.xdb$schema schm, sys.opqtype$ opq,
      sys.tab$ t, sys.user$ u, sys.obj$ o, sys.coltype$ ac, sys.col$ tc,
      sys.attrcol$ attr
where o.owner# = u.user#
  and o.obj# = t.obj#
  and t.obj# = tc.obj#
  and tc.obj# = ac.obj#
  and ac.toid = '00000000000000000000000000020100'
  and tc.intcol# = ac.intcol#
  and tc.obj#    = attr.obj#(+)
  and tc.intcol# = attr.intcol#(+)
  and bitand(tc.property, 512) = 0
  and tc.intcol# =  opq.intcol#
  and tc.obj# =  opq.obj#
  and opq.schemaoid =  schm.sys_nc_oid$
  and bitand(opq.flags,2) = 2
  and opq.elemnum =  xel.xmldata.property.prop_number

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, DECODE(BITAND(TC.PROPERTY
, 1)
, 1
, ATTR.NAME
, TC.NAME)
, NULL
, NULL
, NULL
, CASE WHEN BITAND(OPQ.FLAGS
, 69) = 1 THEN 'OBJECT-RELATIONAL' WHEN BITAND(OPQ.FLAGS
, 69) = 68 THEN 'BINARY' ELSE 'CLOB' END
, CASE WHEN BITAND(OPQ.FLAGS
, 69) = 68 THEN CASE WHEN BITAND(OPQ.FLAGS
, 128) = 128 THEN 'YES' ELSE 'NO' END ELSE NULL END
, CASE WHEN BITAND(OPQ.FLAGS
, 69) = 68 THEN CASE WHEN BITAND(OPQ.FLAGS
, 256) = 256 THEN 'NO' ELSE 'YES' END ELSE NULL END FROM SYS.OPQTYPE$ OPQ
, SYS.TAB$ T
, SYS.USER$ U
, SYS.OBJ$ O
, SYS.COLTYPE$ AC
, SYS.COL$ TC
, SYS.ATTRCOL$ ATTR WHERE O.OWNER# = U.USER#
AND O.OBJ# = T.OBJ#
AND T.OBJ# = TC.OBJ#
AND TC.OBJ# = AC.OBJ#
AND TC.INTCOL# = AC.INTCOL#
AND TC.OBJ# = ATTR.OBJ#(+)
AND TC.INTCOL# = ATTR.INTCOL#(+)
AND BITAND(TC.PROPERTY
, 512) = 0
AND AC.TOID = '00000000000000000000000000020100'
AND TC.INTCOL# = OPQ.INTCOL#
AND TC.OBJ# = OPQ.OBJ#
AND BITAND(OPQ.FLAGS
, 2) = 0 UNION ALL SELECT U.NAME
, O.NAME
, DECODE(BITAND(TC.PROPERTY
, 1)
, 1
, ATTR.NAME
, TC.NAME)
, SCHM.XMLDATA.SCHEMA_URL
, SCHM.XMLDATA.SCHEMA_OWNER
, DECODE(XEL.XMLDATA.PROPERTY.NAME
, NULL
, XEL.XMLDATA.PROPERTY.PROPREF_NAME.NAME
, XEL.XMLDATA.PROPERTY.NAME)
, CASE WHEN BITAND(OPQ.FLAGS
, 69) = 1 THEN 'OBJECT-RELATIONAL' WHEN BITAND(OPQ.FLAGS
, 69) = 68 THEN 'BINARY' ELSE 'CLOB' END
, CASE WHEN BITAND(OPQ.FLAGS
, 69) = 68 THEN CASE WHEN BITAND(OPQ.FLAGS
, 128) = 128 THEN 'YES' ELSE 'NO' END ELSE NULL END
, CASE WHEN BITAND(OPQ.FLAGS
, 69) = 68 THEN CASE WHEN BITAND(OPQ.FLAGS
, 256) = 256 THEN 'NO' ELSE 'YES' END ELSE NULL END FROM XDB.XDB$ELEMENT XEL
, XDB.XDB$SCHEMA SCHM
, SYS.OPQTYPE$ OPQ
, SYS.TAB$ T
, SYS.USER$ U
, SYS.OBJ$ O
, SYS.COLTYPE$ AC
, SYS.COL$ TC
, SYS.ATTRCOL$ ATTR WHERE O.OWNER# = U.USER#
AND O.OBJ# = T.OBJ#
AND T.OBJ# = TC.OBJ#
AND TC.OBJ# = AC.OBJ#
AND AC.TOID = '00000000000000000000000000020100'
AND TC.INTCOL# = AC.INTCOL#
AND TC.OBJ# = ATTR.OBJ#(+)
AND TC.INTCOL# = ATTR.INTCOL#(+)
AND BITAND(TC.PROPERTY
, 512) = 0
AND TC.INTCOL# = OPQ.INTCOL#
AND TC.OBJ# = OPQ.OBJ#
AND OPQ.SCHEMAOID = SCHM.SYS_NC_OID$
AND BITAND(OPQ.FLAGS
, 2) = 2
AND OPQ.ELEMNUM = XEL.XMLDATA.PROPERTY.PROP_NUMBER