asked on

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

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>

no worries :-) no nothing

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

have you checked?

no worries :-) no nothing

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

Johann2

ASKER

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

Johann2

ASKER

....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.

slightwv (䄆 Netminder)

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

Johann2

ASKER

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

no worries :-) no nothing

Try check it with an xml tool here: http://www.pscode.org/xml/xmltools.html#xmlwfc

Johann2

ASKER

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.

Johann2

ASKER

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?

slightwv (䄆 Netminder)

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

Johann2

ASKER

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.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Johann2

ASKER

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

Regards,
Johann

slightwv (䄆 Netminder)

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.