Oracle's SQLloader won't load xml table contents into xmltype table

Johann2
Johann2 used Ask the Experts™
on
Hello.

I have a problem concerning Oracle's SQLloader when trying to check out Oracle's often-used purchaseorder example on a installation of Oracle 11g Express Edition.

After having created an xmltype table 'purchaseorder' based on the homonymous xml schema,
I succeed in inserting data using SQL.
However, when I try to import the contents of the same xml files purchaseOrderPart1.xml and purchaseOrderPart2.xml
using SQLloader, I get an error "Record 1: Rejected - Error on table MYSELF.PURCHASEORDER.
ORA-02290: check constraint (MYSELF.VALIDATE_PURCHASEORDER) violated".

An intensive web search didn't yield any matching results.

Can anybody help me solving the issue?

Thank you,
Johann.


Here are the details...

...connected via User MYSELF...


CREATE TABLE purchaseorder OF XMLType
  XMLSCHEMA "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd"
  ELEMENT "PurchaseOrder"
  VARRAY "XMLDATA"."Actions"."Action"
    STORE AS TABLE action_table
                   ((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$))
                   ORGANIZATION INDEX OVERFLOW)
  VARRAY "XMLDATA"."LineItems"."LineItem"
    STORE AS TABLE lineitem_table
                   ((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$))
                   ORGANIZATION INDEX OVERFLOW);


ALTER TABLE purchaseorder
  ADD CONSTRAINT validate_purchaseorder
  CHECK (XMLIsValid(OBJECT_VALUE) = 1);
 
 
   
INSERT INTO purchaseorder
  VALUES (XMLType(bfilename('UTL_FILE_DIR', 'purchaseOrder.xml'),
                  nls_charset_id('AL32UTF8')));

works!
                 
INSERT INTO purchaseorder
  VALUES (XMLType(bfilename('UTL_FILE_DIR', 'purchaseOrderPart1.xml'),
                  nls_charset_id('AL32UTF8')));
                 
works!

INSERT INTO purchaseorder
  VALUES (XMLType(bfilename('UTL_FILE_DIR', 'purchaseOrderPart2.xml'),
                  nls_charset_id('AL32UTF8')));

works!

commit;                  

works!
                 

------
The following DOES NOT WORK....
------

SQLloader is called via an SQLplus script...(where &9 is a system environment variable pointing to a valid directory path)...
$sqlldr   system/<pwd> CONTROL=&9\load_data.ctl;
---

....contents of load_data.ctl...

LOAD DATA characterset AL32UTF8
infile 'D:\users\x\mydir\sqlloader\filelist.dat'
INTO TABLE myself.purchaseorder
append
xmltype(xmldata)
FIELDS
(
      ext_fname filler char(200),
      xmldata LOBFILE (ext_fname) TERMINATED BY EOF
)

.... and the contents of the filelist.dat....
D:\users\x\mydir\sqlloader\a\purchaseorderPart1.xml

.... and an excerpt from the logfile...

SQL*Loader: Release 11.2.0.2.0 - Beta on Fr Aug 5 07:03:25 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Control File:   D:\users\x\mydir\sqlloader\load_data.ctl
Character Set AL32UTF8 specified for all input.

Data File:      D:\users\x\mydir\sqlloader\filelist.dat
  Bad File:     D:\users\x\mydir\sqlloader\filelist.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table MYSELF.PURCHASEORDER, loaded from every logical record.
Insert option in effect for this table: APPEND

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EXT_FNAME                           FIRST   200           CHARACTER            
  (FILLER FIELD)
XMLDATA                           DERIVED     *  EOF      CHARACTER            
    Dynamic LOBFILE.  Filename in field EXT_FNAME
    Character Set AL32UTF8 specified for all input.

Record 1: Rejected - Error on table MYSELF.PURCHASEORDER.
ORA-02290: check constraint (MYSELF.VALIDATE_PURCHASEORDER) violated


Table MYSELF.PURCHASEORDER:
  0 Rows successfully loaded.
  1 Row not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  13184 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             1
Total logical records rejected:         1
Total logical records discarded:        0
...
---------------------

THE XML SCHEMA   purchaseorder.xsd...

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" version="1.0">
      <xs:element name="PurchaseOrder" type="PurchaseOrderType"/>
      <xs:complexType name="PurchaseOrderType">
            <xs:sequence>
                  <xs:element name="Reference" type="ReferenceType"/>
                  <xs:element name="Actions" type="ActionsType"/>
                  <xs:element name="Reject" type="RejectionType" minOccurs="0"/>
                  <xs:element name="Requestor" type="RequestorType"/>
                  <xs:element name="User" type="UserType"/>
                  <xs:element name="CostCenter" type="CostCenterType"/>
                  <xs:element name="ShippingInstructions" type="ShippingInstructionsType"/>
                  <xs:element name="SpecialInstructions" type="SpecialInstructionsType"/>
                  <xs:element name="LineItems" type="LineItemsType"/>
            </xs:sequence>
      </xs:complexType>
      <xs:complexType name="LineItemsType">
            <xs:sequence>
                  <xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded"/>
            </xs:sequence>
      </xs:complexType>
      <xs:complexType name="LineItemType">
            <xs:sequence>
                  <xs:element name="Description" type="DescriptionType"/>
                  <xs:element name="Part" type="PartType"/>
            </xs:sequence>
            <xs:attribute name="ItemNumber" type="xs:integer"/>
      </xs:complexType>
      <xs:complexType name="PartType">
            <xs:attribute name="Id">
                  <xs:simpleType>
                        <xs:restriction base="xs:string">
                              <xs:minLength value="10"/>
                              <xs:maxLength value="14"/>
                        </xs:restriction>
                  </xs:simpleType>
            </xs:attribute>
            <xs:attribute name="Quantity" type="moneyType"/>
            <xs:attribute name="UnitPrice" type="quantityType"/>
      </xs:complexType>
      <xs:simpleType name="ReferenceType">
            <xs:restriction base="xs:string">
                  <xs:minLength value="18"/>
                  <xs:maxLength value="30"/>
            </xs:restriction>
      </xs:simpleType>
      <xs:complexType name="ActionsType">
            <xs:sequence>
                  <xs:element name="Action" maxOccurs="4">
                        <xs:complexType>
                              <xs:sequence>
                                    <xs:element name="User" type="UserType"/>
                                    <xs:element name="Date" type="DateType" minOccurs="0"/>
                              </xs:sequence>
                        </xs:complexType>
                  </xs:element>
            </xs:sequence>
      </xs:complexType>
      <xs:complexType name="RejectionType">
            <xs:all>
                  <xs:element name="User" type="UserType" minOccurs="0"/>
                  <xs:element name="Date" type="DateType" minOccurs="0"/>
                  <xs:element name="Comments" type="CommentsType" minOccurs="0"/>
            </xs:all>
      </xs:complexType>
      <xs:complexType name="ShippingInstructionsType">
            <xs:sequence>
                  <xs:element name="name" type="NameType" minOccurs="0"/>
                  <xs:element name="address" type="AddressType" minOccurs="0"/>
                  <xs:element name="telephone" type="TelephoneType" minOccurs="0"/>
            </xs:sequence>
      </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="0"/>
                  <xs:maxLength value="10"/>
            </xs:restriction>
      </xs:simpleType>
      <xs:simpleType name="RequestorType">
            <xs:restriction base="xs:string">
                  <xs:minLength value="0"/>
                  <xs:maxLength value="128"/>
            </xs:restriction>
      </xs:simpleType>
      <xs:simpleType name="CostCenterType">
            <xs:restriction base="xs:string">
                  <xs:minLength value="1"/>
                  <xs:maxLength value="4"/>
            </xs:restriction>
      </xs:simpleType>
      <xs:simpleType name="VendorType">
            <xs:restriction base="xs:string">
                  <xs:minLength value="0"/>
                  <xs:maxLength value="20"/>
            </xs:restriction>
      </xs:simpleType>
      <xs:simpleType name="PurchaseOrderNumberType">
            <xs:restriction base="xs:integer"/>
      </xs:simpleType>
      <xs:simpleType name="SpecialInstructionsType">
            <xs:restriction base="xs:string">
                  <xs:minLength value="0"/>
                  <xs:maxLength value="2048"/>
            </xs:restriction>
      </xs:simpleType>
      <xs:simpleType name="NameType">
            <xs:restriction base="xs:string">
                  <xs:minLength value="1"/>
                  <xs:maxLength value="20"/>
            </xs:restriction>
      </xs:simpleType>
      <xs:simpleType name="AddressType">
            <xs:restriction base="xs:string">
                  <xs:minLength value="1"/>
                  <xs:maxLength value="256"/>
            </xs:restriction>
      </xs:simpleType>
      <xs:simpleType name="TelephoneType">
            <xs:restriction base="xs:string">
                  <xs:minLength value="1"/>
                  <xs:maxLength value="24"/>
            </xs:restriction>
      </xs:simpleType>
      <xs:simpleType name="DateType">
            <xs:restriction base="xs:date"/>
      </xs:simpleType>
      <xs:simpleType name="CommentsType">
            <xs:restriction base="xs:string">
                  <xs:minLength value="1"/>
                  <xs:maxLength value="2048"/>
            </xs:restriction>
      </xs:simpleType>
      <xs:simpleType name="DescriptionType">
            <xs:restriction base="xs:string">
                  <xs:minLength value="1"/>
                  <xs:maxLength value="256"/>
            </xs:restriction>
      </xs:simpleType>
</xs:schema>
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
What about this error
....Record 1: Rejected - Error on table MYSELF.PURCHASEORDER.
ORA-02290: check constraint (MYSELF.VALIDATE_PURCHASEORDER) violated


 have you checked?

XMLISVALID checks that the XMLType_instance is according to your XML schema.

Author

Commented:
I validated the document inside of XMLspy. -> ok.
The database validates the document in the scenario where I insert it manually using SQL. -> ok.

Author

Commented:
....I also tried to used or omit namespaces or use the wrong namespace, respectively.
In the latter case, SQLloader complains about this fact, so I suppose, the namespaces are ok.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Can you upload/post D:\users\x\mydir\sqlloader\a\purchaseorderPart1.xml?

Author

Commented:
...sure, I should have done it right with my question. purchaseorderPart1.xml

Author

Commented:
Thank you for this idea.
The tool, says, the xml file is well-formed. I haven't validated it against the schema because the tool doesn't seem to support this feature. However,
- SQLloader behaves the same even if I ommit the schema reference.
- the database - which does schema validation - accepts the file if I insert it via SQL.
- XMLspy says, the file is schema-conform.

However: it seems to me as if it is the database complaining about the non-valid xml data once they are sent by sqlloader. The check constraint is specified in the database and not somewhere within sqlloader.

The purchaseorder example is one of Oracle's examples. It seems to work fine there.

Author

Commented:
In the meantime I kept experimenting and finally found, that sqlloader does load the given xml file into the database if I drop the check constraint and use a "before insert"-trigger as shown in Oracle Document.
However: why is this the case?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
That last post confuses me.  Can you explain what you are doing by dropping the constraint, etc...?


Author

Commented:
Sure. I'll try to make things a bit clearer:
During the generation of the purchaseorder table the check constraint is created that makes us trouble here. The statements are given in my first post...
ALTER TABLE purchaseorder
  ADD CONSTRAINT validate_purchaseorder
  CHECK (XMLIsValid(OBJECT_VALUE) = 1);

In order to get rid of the check constraint I just drop it, so it won't exist anymore. But now, I don't have a validation of the xml data.
So, as the oracle site suggests, I do the following...
CREATE OR REPLACE TRIGGER validate_purchaseorder
   BEFORE INSERT ON purchaseorder
   FOR EACH ROW
BEGIN
  IF (:new.OBJECT_VALUE IS NOT NULL) THEN :new.OBJECT_VALUE.schemavalidate();
  END IF;
END;
/

The only difference SHOULD be, that this new solution yields better messages for error tracing, in that it informs you about what is wrong with an XML document to be inserted into the database.

But - surprise - having this done, things are as they should be, as the database won't throw any error message on insertion of the respective xml data, as they are - in fact - valid.
So, the remaining question is, why the check constraint throws an error on the insertion of valid xml data via SQL*loader. As already stated, the check constraint does not cause any trouble if xml data are inserted using SQL statements and NOT SQL*loader.

I hope this is clear enough - please don't hesitate to ask again. Thank you.




Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Thanks for that.  It cleared it up for me.

Now for the bad news.  Your code runs for me as you have it on 10.2.0.3 and 11.2.0.2 Enterprise Edition.

I moved the data for the controlfile inline but I don't think that would cause any problem.

I didn't post back the XML and XSD files since they were not altered.

You might be hitting an XE bug or XE doesn't allow something you are trying to do.  I would probably open an SR with Support on this one.
create or replace directory XML_DIR as 'c:\';
exec DBMS_XMLSCHEMA.DELETESCHEMA('http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd');
exec DBMS_XMLSCHEMA.REGISTERSCHEMA('http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd',bfilename('XML_DIR','purchaseOrder.xsd')); 


CREATE TABLE purchaseorder OF XMLType
  XMLSCHEMA "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd"
  ELEMENT "PurchaseOrder"
  VARRAY "XMLDATA"."Actions"."Action"
    STORE AS TABLE action_table 
                   ((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$))
                   ORGANIZATION INDEX OVERFLOW)
  VARRAY "XMLDATA"."LineItems"."LineItem"
    STORE AS TABLE lineitem_table 
                   ((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$))
                   ORGANIZATION INDEX OVERFLOW);


ALTER TABLE purchaseorder ADD CONSTRAINT validate_purchaseorder CHECK (XMLIsValid(OBJECT_VALUE) = 1);
  

INSERT INTO purchaseorder VALUES (XMLType(bfilename('XML_DIR', 'purchaseOrder.xml'), nls_charset_id('AL32UTF8')));

Open in new window

LOAD DATA characterset AL32UTF8 
infile *
INTO TABLE myself.purchaseorder 
append
xmltype(xmldata)
FIELDS
(
      ext_fname filler char(200),
      xmldata LOBFILE (ext_fname) TERMINATED BY EOF
)
begindata
c:\purchaseorder.xml

Open in new window

Author

Commented:
ok, so let me thank you and georgeKl for your quick response and your help in clearing up the problem.

Regards,
Johann
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Glad to help.  As soon as I posted I wanted to verify something and see 11g XE is still in Beta.

My guess is on a Bug.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial