This discussion is archived
11 Replies Latest reply: Dec 4, 2012 2:55 PM by 275191 RSS

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

840690 Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    Sorry, you are right. I mixed up stuff.
  • 6. Re: Empty Element Causes OR Storage Issue (OCI-21500 qmxiAddToXob3)
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points