11 Replies Latest reply: Dec 4, 2012 4:55 PM by 275191 RSS

    Empty Element Causes OR Storage Issue (OCI-21500 qmxiAddToXob3)

    840690
      System Info
      >
      SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 18 06:21:00 2012

      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      With the Partitioning, Automatic Storage Management, OLAP, Data Mining
      and Real Application Testing options

      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE 11.2.0.1.0 Production
      TNS for Linux: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production
      >

      Background
      In a previous issue (XMLTable Query Causes VM Stack Overflow it was suggested that I move to an Object-Relational storage model for my XML data to alleviate an XVM Stack Overflow (memory) issue.

      Problem
      I have a table with an XML column which is stored OR:
      SQL> desc nads_x;
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       ID                                        NOT NULL NUMBER
       OWNING_ORI                                NOT NULL VARCHAR2(9)
       DATA_INTERNAL                             NOT NULL SYS.XMLTYPE(XMLSchema "http:
                                                          //fbi.gov/cjis/N-DEx/nsidm/1
                                                          .1" Element "DataItem") STOR
                                                          AGE Object-relational TYPE "
                                                          DataItemType1922_T"
      I have successfully loaded records into this new structure; however, I have found records which load but appear to be corrupted as a subsequent query returns the following error:
      SQL> SELECT DATA_INTERNAL FROM NADS_X WHERE ID=5942955;
      Errors in file :
      OCI-21500: internal error code, arguments: [qmxiAddToXob3], [], [], [], [], [], [], []
      ????????????????ERROR:
      OCI-21500: internal error code, arguments: [qmxiAddToXob3], [], [], [], [], [],
      [], []
      I have extracted the XML instance from the record above (from a binary instance I had previous to importing into the OR structure) and found that there is an empty element (<Narrative/>) which appears to be causing the problem. If I remove this empty element and load the XML instance anew I can query the XML instance as expected.

      The <Narrative/> element is defined as follows in my schema:
           <xsd:element name="Narrative" type="narrativeType" minOccurs="0" maxOccurs="unbounded"/>
           ...
           <xsd:simpleType name="narrativeType" xdb:SQLType="CLOB" >
               <xsd:restriction base="clobType" />
          </xsd:simpleType>
          
          <xsd:simpleType name="clobType" xdb:SQLType="CLOB">
               <xsd:restriction base="xsd:string"></xsd:restriction>
          </xsd:simpleType>
           ...
      and generated within the database as:
      SQL> desc "DataItemType1922_T"
       "DataItemType1922_T" is NOT FINAL
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       SYS_XDBPD$                                         XDB.XDB$RAW_LIST_T
       guid                                               VARCHAR2(4000 CHAR)
       ...
       Narrative                                          CLOB
       ...
      Can anyone explain to me if this is 1) correct behavior (e.g. are empty elements not allowed/supported with OR structure) or 2) is there something wrong with how I've defined my OR structure?

      Edited by: drad on Aug 9, 2012 7:29 AM
        • 1. Re: Empty Element Causes OR Storage Issue (OCI-21500 qmxiAddToXob3)
          Marco Gralike
          I am guessing you are on the frontier of things here... Not sure if in an XMLType OR COLUMN are allowed to use with the HYBRID storage method (xdb:SQLType="CLOB").
          By HYBRID I mean Object Relational storage with within CLOB storage options defined.
          I know this isn't an issue while using XMLType Object Relational XMLType TABLES, but not sure if this also works in XMLType COLUMNS.

          Nethertheless, you encountered a bug so file a SR for this and see what the outcome is.

          Edited by: Marco Gralike on Aug 9, 2012 6:21 PM
          • 2. Re: Empty Element Causes OR Storage Issue (OCI-21500 qmxiAddToXob3)
            odie_63
            Marco Gralike wrote:
            I know this is deprecated (xdb:SQLType="CLOB") or will be. Anyway. By HYBRID I mean Object Relational storage with within CLOB storage options defined.
            Marco,

            I thought "Hybrid Storage" meant OR + islands of complexTypes stored as CLOB?
            It's not the case here, as far as I understand.
            • 3. Re: Empty Element Causes OR Storage Issue (OCI-21500 qmxiAddToXob3)
              mdrake-Oracle
              SQLTYpe="CLOB" will not be depricated. XMLType store as CLOB is being depricated..
              • 4. Re: Empty Element Causes OR Storage Issue (OCI-21500 qmxiAddToXob3)
                840690
                mdrake wrote:
                SQLTYpe="CLOB" will not be depricated. XMLType store as CLOB is being depricated..
                I'm not sure what you mean by XMLType stored as CLOB. My XMLtype data is stored OR; however, I have an element in the XML which is too large for VARCHAR so I've annotated to store it as a CLOB. Is there a better/preferred way to do this? From a performance perspective, I've welcome a different datatype other than clob but I know of no other way given the size of the Narrative element can and definitely will be larger than 4000 characters.
                • 5. Re: Empty Element Causes OR Storage Issue (OCI-21500 qmxiAddToXob3)
                  Marco Gralike
                  Sorry, you are right. I mixed up stuff.
                  • 6. Re: Empty Element Causes OR Storage Issue (OCI-21500 qmxiAddToXob3)
                    Marco Gralike
                    odie_63 wrote:

                    I thought "Hybrid Storage" meant OR + islands of complexTypes stored as CLOB?
                    The first thing that pops in my mind when you use the term "islands" is a reference to Structured XMLIndex.

                    I tried to look up the complexTypes thing. In the index of the XMLDB Developers Guide when looking up "hybrid" ("see semistructured storage") I find references like

                    http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb02rep.htm#sthref207
                    Semistructured storage – Semistructured, or hybrid, storage is a special case of structured storage in which a portion of the XML data is broken up into a structured format and the remainder of the data is stored as a CLOB value.
                    The only way to achieve this, AFAIK, implementing "LOB" within OR structures, is via using xdb:SQLType="CLOB"/"BLOB", References in the manual to this item http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb06stt.htm#sthref1017
                    I would use this construct, for example, to be able to store almost never used/touched data (performance reasons) or to be able to, for example, create an Oracle text index on top of this CLOB bit, as mentioned in the docs via something like "NOTES"
                    <xs:element name="Notes" type="po:NotesType" xdb:SQLType="CLOB"/>
                    Up so far Oracle 10 stuff.

                    I don't know if you would create xdb:SQLType="CLOB" if you also are allowed (at least supported) to create unstructured and structured xmlindexes on top of it. Ideally I would like to be able to use xdb:SQLType="BinaryXML" (securefile) - but this doesn't exist as an xdb:annotation.
                    When searching in the 11g docs regarding xdb:annotation usage (Oracle 11 doc are a bit more specific than the 10.2 doc) xdb:SQLType is allowed on "element" level and "global complexTypes element" level: http://docs.oracle.com/cd/E14072_01/appdev.112/e10492/xdb05sto.htm#g1048403
                    The only exception in both cases is that you are not allowed to use "Any SQL data type except NCHAR, NVARCHAR, and NCLOB". It referes though to a footnote mentioning...http://docs.oracle.com/cd/E14072_01/appdev.112/e10492/xdb05sto.htm#i1030995:
                    Overriding the SQLType Value in an XML Schema when Declaring Elements

                    An element based on a complexType is, by default, mapped to a SQL object type that contains object attributes corresponding to each of the sub-elements and attributes. You can override this mapping by explicitly specifying a value for attribute SQLType in the input XML schema. The following values for SQLType are permitted here:

                    VARCHAR2
                    RAW
                    CLOB
                    BLOB
                    Is this what you where referring to?
                    • 7. Re: Empty Element Causes OR Storage Issue (OCI-21500 qmxiAddToXob3)
                      Marco Gralike
                      Even a bit more specific, I would implement it in a XMLType OR situation (that is SQLType="CLOB", LOB, BLOB, aka "Hybrid")

                      - performance reasons (making it "unattractive" for the CBO) - in case of storing almost never used/touched data --> avoiding I/O
                      - handling unstructured data bits/fragment/pieces via, for example, Oracle text
                      - handling semi-structured data bits/fragment/pieces via, for example, Unstructured XMLIndexes
                      - the data content is not allowed to be changed "as is" (xdb:SQLType="CLOB") due to, for example, legal reasons, that is NOT allowed to change in whatever method (whitespaces, next lines etc)

                      Edited by: Marco Gralike on Aug 9, 2012 8:05 PM
                      • 8. Re: Empty Element Causes OR Storage Issue (OCI-21500 qmxiAddToXob3)
                        mdrake-Oracle
                        Here are the details

                        create table FOO of XMLTYPE
                        XMLTYPE Store as CLOB

                        or the equivilant to create a relational table containing an XMLType column which underneath the covers is stored using a CLOB will be depricated in the near future.

                        The Ability to map a complexType with OR to a clob by using the annotation xdb:SQLType="CLOB" is not (yet) depricated. If at some point in the future we allow a ComplexType within an object-relational mapping to be stored as BinaryXML then we choose to depricate mapping a complexType to a CLOB at that point.

                        The ability to store simple content (eg textNodes, attribute values) as a CLOB using xdb:SQLType="CLOB" will not be depricated. The OP appears to have this use case, and should file a bug
                        • 9. Re: Empty Element Causes OR Storage Issue (OCI-21500 qmxiAddToXob3)
                          odie_63
                          mdrake wrote:
                          The ability to store simple content (eg textNodes, attribute values) as a CLOB using xdb:SQLType="CLOB" will not be depricated. The OP appears to have this use case, and should file a bug
                          That's what I had in mind too.
                          • 10. Re: Empty Element Causes OR Storage Issue (OCI-21500 qmxiAddToXob3)
                            Marco Gralike
                            Marco Gralike wrote:

                            Nethertheless, you encountered a bug so file a SR for this and see what the outcome is.
                            • 11. Re: Empty Element Causes OR Storage Issue (OCI-21500 qmxiAddToXob3)
                              275191
                              I had the same issue with CLOB elements with maxOccurs="unbounded", did not speak to support, just removed maxOccurs="unbounded". The document could be stored but use of XMLQuery on resulted in session crash.