4 Replies Latest reply on Apr 17, 2014 12:15 PM by odie_63

    problems using insertChildXmlAfter() with DOM Fidelity disabled in 11gR2

    alexjingsi

      I'm using an Object-Relation xmltype table to store all the xml data(nearly 50M) I need in my project. I've annotated all the complex element with "xdb:maintainDOM="false" in the schema to disable the DOM Fidelity, as I need to do fast DML operations with more than 50M data in my table. Even further more, I use optimized SQL/XML function insertchildxmlafter()(several milliseconds) instead of inserxmlafter()(several seconds) to insert a node into the xmltype table, but some serious errors occur when using insertchildxmlafter() in this situation. When I try to insert some nodes after a certain node, it succeed at the first seven, but failed at the 8th time with error "ORA-00001: unique constraint (CS.SYS_C0011263) violated", and what's worse, the database will hung when trying to insert some nodes after the last node specified by "[last()]".

      So I wonder I've made some mistakes in my schema or codes. Any advice will be appreciate. Thanks!

       

      Here is my database version:

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

       

      Here is a sample schema I use to reproduce the problem:

      purchaseOrder.xsd:

      <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" elementFormDefault="qualified" attributeFormDefault="unqualified" version="1.0" xdb:storeVarrayAsTable="true">
        <xs:element name="PurchaseOrder" type="PurchaseOrderType"/>
        <xs:complexType name="PurchaseOrderType" xdb:maintainDOM="false">
        <xs:sequence>
        <xs:element name="LineItems" type="LineItemsType" maxOccurs="unbounded"/>
        </xs:sequence>
        </xs:complexType>
        <xs:complexType name="LineItemsType" xdb:maintainDOM="false">
        <xs:sequence>
        <xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded"/>
        </xs:sequence>
        </xs:complexType>
        <xs:complexType name="LineItemType" xdb:maintainDOM="false">
        <xs:sequence>
        <xs:element name="Part" type="PartType"/>
        <xs:element name="Quantity" type="quantityType"/>
        </xs:sequence>
        <xs:attribute name="ItemNumber" type="xs:integer"/>
        </xs:complexType>
        <xs:complexType name="PartType" xdb:maintainDOM="false">
        <xs:simpleContent>
        <xs:extension base="UPCCodeType">
        <xs:attribute name="Description" type="DescriptionType" use="required"/>
        <xs:attribute name="UnitPrice" type="moneyType" use="required"/>
        </xs:extension>
        </xs:simpleContent>
        </xs:complexType>
        <xs:simpleType name="moneyType">
        <xs:restriction base="xs:decimal">
        <xs:fractionDigits value="2"/>
        <xs:totalDigits value="12"/>
        </xs:restriction>
        </xs:simpleType>
        <xs:simpleType name="quantityType">
        <xs:restriction base="xs:decimal">
        <xs:fractionDigits value="4"/>
        <xs:totalDigits value="8"/>
        </xs:restriction>
        </xs:simpleType>
        <xs:simpleType name="UserType">
        <xs:restriction base="xs:string">
        <xs:minLength value="1"/>
        <xs:maxLength value="10"/>
        </xs:restriction>
        </xs:simpleType>
        <xs:simpleType name="PurchaseOrderNumberType">
        <xs:restriction base="xs:integer"/>
        </xs:simpleType>
        <xs:simpleType name="DescriptionType">
        <xs:restriction base="xs:string">
        <xs:minLength value="1"/>
        <xs:maxLength value="256"/>
        </xs:restriction>
        </xs:simpleType>
        <xs:simpleType name="UPCCodeType">
        <xs:restriction base="xs:string">
        <xs:minLength value="11"/>
        <xs:maxLength value="14"/>
        <xs:pattern value="\d{11}"/>
        <xs:pattern value="\d{12}"/>
        <xs:pattern value="\d{13}"/>
        <xs:pattern value="\d{14}"/>
        </xs:restriction>
        </xs:simpleType>
      </xs:schema>
      
      

      Here is a sample xml instance:

      purchaseOrder.xml:

      <?xml version="1.0" encoding="UTF-8"?>
      <PurchaseOrder>
         <LineItems>
            <LineItem ItemNumber="1">
               <Part Description="Goodbye Lover" UnitPrice="19.95">85391509226</Part>
               <Quantity>2.0</Quantity>
            </LineItem>
            <LineItem ItemNumber="2">
               <Part Description="Most Wanted" UnitPrice="19.95">794043463525</Part>
               <Quantity>6.0</Quantity>
            </LineItem>
            <LineItem ItemNumber="3">
               <Part Description="Short 1: Invention" UnitPrice="19.95">85393684525</Part>
               <Quantity>8.0</Quantity>
            </LineItem>
            <LineItem ItemNumber="4">
               <Part Description="Picnic" UnitPrice="19.95">43396828797</Part>
               <Quantity>1.0</Quantity>
            </LineItem>
         </LineItems>
      </PurchaseOrder>
      
      

       

      Here is the whole reproduce:

       

      SQL> set long 1000 pages 0 line 256 timing on

      SQL> select * from tab;

      no rows selected

      Elapsed: 00:00:00.01

      SQL> begin

        2  dbms_xmlschema.registerSchema

        3  (

        4   schemaurl => 'purchaseOrder.xsd',

        5   schemadoc => xmltype('<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" elementFormDefault="qualified" attributeFormDefault="unqualified" version="1.0" xdb:storeVarrayAsTable="true">

        6                       <xs:element name="PurchaseOrder" type="PurchaseOrderType"/>

        7                       <xs:complexType name="PurchaseOrderType" xdb:maintainDOM="false">

        8                       <xs:sequence>

        9                       <xs:element name="LineItems" type="LineItemsType" maxOccurs="unbounded"/>

      10                       </xs:sequence>

      11                       </xs:complexType>

      12                       <xs:complexType name="LineItemsType" xdb:maintainDOM="false">

      13                       <xs:sequence>

      14                       <xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded"/>

      15                       </xs:sequence>

      16                       </xs:complexType>

      17                       <xs:complexType name="LineItemType" xdb:maintainDOM="false">

      18                       <xs:sequence>

      19                       <xs:element name="Part" type="PartType"/>

      20                       <xs:element name="Quantity" type="quantityType"/>

      21                       </xs:sequence>

      22                       <xs:attribute name="ItemNumber" type="xs:integer"/>

      23                       </xs:complexType>

      24                       <xs:complexType name="PartType" xdb:maintainDOM="false">

      25                       <xs:simpleContent>

      26                       <xs:extension base="UPCCodeType">

      27                       <xs:attribute name="Description" type="DescriptionType" use="required"/>

      28                       <xs:attribute name="UnitPrice" type="moneyType" use="required"/>

      29                       </xs:extension>

      30                       </xs:simpleContent>

      31                       </xs:complexType>

      32                       <xs:simpleType name="moneyType">

      33                       <xs:restriction base="xs:decimal">

      34                       <xs:fractionDigits value="2"/>

      35                       <xs:totalDigits value="12"/>

      36                       </xs:restriction>

      37                       </xs:simpleType>

      38                       <xs:simpleType name="quantityType">

      39                       <xs:restriction base="xs:decimal">

      40                       <xs:fractionDigits value="4"/>

      41                       <xs:totalDigits value="8"/>

      42                       </xs:restriction>

      43                       </xs:simpleType>

      44                       <xs:simpleType name="UserType">

      45                       <xs:restriction base="xs:string">

      46                       <xs:minLength value="1"/>

      47                       <xs:maxLength value="10"/>

      48                       </xs:restriction>

      49                       </xs:simpleType>

      50                       <xs:simpleType name="PurchaseOrderNumberType">

      51                       <xs:restriction base="xs:integer"/>

      52                       </xs:simpleType>

      53                       <xs:simpleType name="DescriptionType">

      54                       <xs:restriction base="xs:string">

      55                       <xs:minLength value="1"/>

      56                       <xs:maxLength value="256"/>

      57                       </xs:restriction>

      58                       </xs:simpleType>

      59                       <xs:simpleType name="UPCCodeType">

      60                       <xs:restriction base="xs:string">

      61                       <xs:minLength value="11"/>

      62                       <xs:maxLength value="14"/>

      63                       <xs:pattern value="\d{11}"/>

      64                       <xs:pattern value="\d{12}"/>

      65                       <xs:pattern value="\d{13}"/>

      66                       <xs:pattern value="\d{14}"/>

      67                       </xs:restriction>

      68                       </xs:simpleType>

      69                      </xs:schema>'),

      70   local => TRUE,

      71   genTypes  => TRUE,

      72   genBean   => FALSE,

      73   genTables => FALSE,

      74   ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE,

      75   FORCE => TRUE

      76  );

      77  end;

      78  /

       

       

      PL/SQL procedure successfully completed.

       

       

      Elapsed: 00:00:00.40

      SQL> create table purchaseorder of xmltype xmlschema "purchaseOrder.xsd" element "PurchaseOrder";

       

       

      Table created.

       

       

      Elapsed: 00:00:00.13

      SQL>

      SQL> insert into purchaseorder values (XMLType('<PurchaseOrder>

        2     <LineItems>

        3        <LineItem ItemNumber="1">

        4           <Part Description="Goodbye Lover" UnitPrice="19.95">85391509226</Part>

        5           <Quantity>2.0</Quantity>

        6        </LineItem>

        7        <LineItem ItemNumber="2">

        8           <Part Description="Most Wanted" UnitPrice="19.95">794043463525</Part>

        9           <Quantity>6.0</Quantity>

      10        </LineItem>

      11        <LineItem ItemNumber="3">

      12           <Part Description="Short 1: Invention" UnitPrice="19.95">85393684525</Part>

      13           <Quantity>8.0</Quantity>

      14        </LineItem>

      15        <LineItem ItemNumber="4">

      16           <Part Description="Picnic" UnitPrice="19.95">43396828797</Part>

      17           <Quantity>1.0</Quantity>

      18        </LineItem>

      19     </LineItems>

      20  </PurchaseOrder>'));

       

       

      1 row created.

       

       

      Elapsed: 00:00:00.01

      SQL> select * from tab;

      PURCHASEORDER                  TABLE                                                                                                                                                                                                                          

      SYS_NTSk8sl9FNTVSCUEE3POKUmQ== TABLE                                                                                                                                                                                                                          

      SYS_NTWVuYzGJVSVKs0zkehId49g== TABLE                                                                                                                                                                                                                          

       

       

      Elapsed: 00:00:00.01

      SQL> select xmlquery('count(PurchaseOrder/LineItems/LineItem)' passing object_value returning content) from purchaseorder;

      4                                                                                                                                                                                                                                                             

       

       

      Elapsed: 00:00:00.04

      // The first 7 insertions succeed.

      SQL> update purchaseorder set object_value = insertchildxmlafter(object_value, '/PurchaseOrder/LineItems', 'LineItem[1]', xmltype('<LineItem ItemNumber="0">

        2    <Part Description="Goodbye Lover" UnitPrice="19.95">85391509226</Part>

        3    <Quantity>2</Quantity>

        4  </LineItem>'));

       

       

      1 row updated.

       

       

      Elapsed: 00:00:00.03

      SQL> select xmlquery('count(PurchaseOrder/LineItems/LineItem)' passing object_value returning content) from purchaseorder;

      5                                                                                                                                                                                                                                                             

       

       

      Elapsed: 00:00:00.01

       

      // Repeat the upon sql for 6 times, also succeed.

      .......

       

      Elapsed: 00:00:00.00

      // The 8th insertion failed.

      SQL> update purchaseorder set object_value = insertchildxmlafter(object_value, '/PurchaseOrder/LineItems', 'LineItem[1]', xmltype('<LineItem ItemNumber="0">

        2    <Part Description="Goodbye Lover" UnitPrice="19.95">85391509226</Part>

        3    <Quantity>2</Quantity>

        4  </LineItem>'));

      update purchaseorder set object_value = insertchildxmlafter(object_value, '/PurchaseOrder/LineItems', 'LineItem[1]', xmltype('<LineItem ItemNumber="0">

      *

      ERROR at line 1:

      ORA-00604: error occurred at recursive SQL level 1

      ORA-00001: unique constraint (CS.SYS_C0011263) violated

       

       

       

       

      Elapsed: 00:00:00.04

      SQL> select xmlquery('count(PurchaseOrder/LineItems/LineItem)' passing object_value returning content) from purchaseorder;

      11                                                                                                                                                                                                                                                            

       

       

      Elapsed: 00:00:00.00

      // inserting node after the last node,  repeat it some times, you will find the database hung.

      SQL> update purchaseorder set object_value = insertchildxmlafter(object_value, '/PurchaseOrder/LineItems', 'LineItem[last()]', xmltype('<LineItem ItemNumber="0">

        2    <Part Description="Goodbye Lover" UnitPrice="19.95">85391509226</Part>

        3    <Quantity>2</Quantity>

        4  </LineItem>'));

      // Repeat the upon sql several times.

      ......

      // The database hung at the inserting operation.