Oracle SQL*Loader not honoring skip_index_maintenance parameter

Question

It appears as if Oracle SQL*Loader is not honoring skip_index_maintenance parameter.

My control file:

unrecoverable
load data
infile 'filelist2.dat'
append
into table XML_TABLE
xmltype(XMLDATA)
(
 filename filler char(120),
 XMLDATA  lobfile(filename) terminated by eof
)

Invoking SQL*Loader with:

sqlldr control=load_xml.ctl direct=true parallel=true skip_index_maintenance=true

Results in:

ORA-26002: Table <MYUSERNAME>."SYS_NTEPJTox0TQcLgU+XYHawPLg==" has index defined upon it.

Huh? I thought the indexes should have been been disabled/marked as unusable? Could this be because the table in question was generated automatically when the XSD was registered with Oracle?

Also if I remove the "parallel" argument:

sqlldr control=load_xml.ctl direct=true skip_index_maintenance=true

Then I get these errors:

OCI-21500: internal error code, arguments: [qmxdpStoreColl:1], [], [], [], [], [], [], []
ÌïçUundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ6ØÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿXÍbUÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿXÍbUÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿXÍbUErrors in file :
OCI-21500: internal error code, arguments: [kgepop: no error frame to pop to], [], [], [], [], [], [], []
OCI-21500: internal error code, arguments: [qmxdpStoreColl:1], [], [], [], [], [], [], []
undefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ@6ØÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿXÍbUÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿXÍbUÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿXÍbUErrors in file :
OCI-21500: internal error code, arguments: [kgepop: no error frame to pop to], [], [], [], [], [], [], []
OCI-21500: internal error code, arguments: [qmxdpStoreColl:1], [], [], [], [], [], [], []
)9Ø)9Ø)9Ø)9Øundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿp6ØÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿXÍbUÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿXÍbUÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿXÍbU

Surely that's not correct?

Note this is a "clean" Oracle 11G database with one set of tables generated based upon an XML Schema.

Conventional (non-direct), SQL*Loader loads work fine. All records are inserted

EDIT: UPDATED WITH REQUESTED INFO

First of all thanks for your help, its appreciated.

I reverted to the Oracle examples to try and simplify. So XSD is:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:xdb="http://xmlns.oracle.com/xdb" version="1.0"
    xdb:storeVarrayAsTable="true">
    <xs:element name="PurchaseOrder" type="PurchaseOrderType"
        xdb:defaultTable="PURCHASEORDER" />
    <xs:complexType name="PurchaseOrderType" xdb:SQLType="PURCHASEORDER_T">
        <xs:sequence>
            <xs:element name="Reference" type="ReferenceType"
                minOccurs="1" xdb:SQLName="REFERENCE" />
            <xs:element name="Actions" type="ActionsType" xdb:SQLName="ACTIONS" />
            <xs:element name="Reject" type="RejectionType" minOccurs="0"
                xdb:SQLName="REJECTION" />
            <xs:element name="Requestor" type="RequestorType"
                xdb:SQLName="REQUESTOR" />
            <xs:element name="User" type="UserType" minOccurs="1"
                xdb:SQLName="USERID" />
            <xs:element name="CostCenter" type="CostCenterType"
                xdb:SQLName="COST_CENTER" />
            <xs:element name="ShippingInstructions" type="ShippingInstructionsType"
                xdb:SQLName="SHIPPING_INSTRUCTIONS" />
            <xs:element name="SpecialInstructions" type="SpecialInstructionsType"
                xdb:SQLName="SPECIAL_INSTRUCTIONS" />
            <xs:element name="LineItems" type="LineItemsType"
                xdb:SQLName="LINEITEMS" />
        </xs:sequence>
    </xs:complexType>
    <xs:complexType name="LineItemsType" xdb:SQLType="LINEITEMS_T">
        <xs:sequence>
            <xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded"
                xdb:SQLName="LINEITEM" xdb:SQLCollType="LINEITEM_V" />
        </xs:sequence>
    </xs:complexType>
    <xs:complexType name="LineItemType" xdb:SQLType="LINEITEM_T">
        <xs:sequence>
            <xs:element name="Description" type="DescriptionType"
                xdb:SQLName="DESCRIPTION" />
            <xs:element name="Part" type="PartType" xdb:SQLName="PART" />
        </xs:sequence>
        <xs:attribute name="ItemNumber" type="xs:integer"
            xdb:SQLName="ITEMNUMBER" xdb:SQLType="NUMBER" />
    </xs:complexType>
    <xs:complexType name="PartType" xdb:SQLType="PART_T">
        <xs:attribute name="Id" xdb:SQLName="PART_NUMBER"
            xdb:SQLType="VARCHAR2">
            <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"
            xdb:SQLName="QUANTITY" />
        <xs:attribute name="UnitPrice" type="quantityType"
            xdb:SQLName="UNITPRICE" />
    </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" xdb:SQLType="ACTIONS_T">
        <xs:sequence>
            <xs:element name="Action" maxOccurs="4" xdb:SQLName="ACTION"
                xdb:SQLCollType="ACTION_V">
                <xs:complexType xdb:SQLType="ACTION_T">
                    <xs:sequence>
                        <xs:element name="User" type="UserType" xdb:SQLName="ACTIONED_BY" />
                        <xs:element name="Date" type="DateType" minOccurs="0"
                            xdb:SQLName="DATE_ACTIONED" />
                    </xs:sequence>
                </xs:complexType>
            </xs:element>
        </xs:sequence>
    </xs:complexType>
    <xs:complexType name="RejectionType" xdb:SQLType="REJECTION_T">
        <xs:all>
            <xs:element name="User" type="UserType" minOccurs="0"
                xdb:SQLName="REJECTED_BY" />
            <xs:element name="Date" type="DateType" minOccurs="0"
                xdb:SQLName="DATE_REJECTED" />
            <xs:element name="Comments" type="CommentsType" minOccurs="0"
                xdb:SQLName="REASON_REJECTED" />
        </xs:all>
    </xs:complexType>
    <xs:complexType name="ShippingInstructionsType"
        xdb:SQLType="SHIPPING_INSTRUCTIONS_T">
        <xs:sequence>
            <xs:element name="name" type="NameType" minOccurs="0"
                xdb:SQLName="SHIP_TO_NAME" />
            <xs:element name="address" type="AddressType" minOccurs="0"
                xdb:SQLName="SHIP_TO_ADDRESS" />
            <xs:element name="telephone" type="TelephoneType"
                minOccurs="0" xdb:SQLName="SHIP_TO_PHONE" />
        </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>

Registering schema and creating DB objects:

create or replace directory XSD as '/path/to/xsd'

BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    'http://www.oracle.com/PurchaseOrder.xsd',
    bfilename('XSD','PurchaseOrder.xsd'),
    TRUE,
    TRUE,
    FALSE,
    TRUE);
END;

This creates the DB table and objects.

When I try to SQL*Loader in direct mode (conventional works fine) with:

sqlldr control=load_po.ctl direct=true

I get:

ORA-26086: direct path does not support triggers

As I mentioned the Schema registration process appears to have create the following trigger:

create or replace trigger "SUKLTI"."PURCHASEORDER$xd" after delete or update on "SUKLTI"."PURCHASEORDER" for each row BEGIN  IF (deleting) THEN xdb.xdb_pitrig_pkg.pitrig_del('SUKLTI','PURCHASEORDER', :old.sys_nc_oid$, '1100C2917B7B2ADEE053E5D81DAC36D4' ); END IF;   IF (updating) THEN xdb.xdb_pitrig_pkg.pitrig_upd('SUKLTI','PURCHASEORDER', :old.sys_nc_oid$, '1100C2917B7B2ADEE053E5D81DAC36D4', user ); END IF; END;

I can't find any docs on "xdb_pitrig_pkg" to understand what its doing.

Even if I drop the trigger I still get:

ORA-26086: direct path does not support triggers

When running the loader in direct mode

EDIT 16/03/2015

After much thought I've decided NOT to load the initial data into Schema backed tables. The main reason being that the performance was not very good.

I have instead decided to perform my initial bulk XML load into XMLType tables that ARE NOT Schema backed. Even though not based upon a schema the XML will be checked for "well formedness". Loading this way is an order of magnitude faster as I am able to use SQL*Loader in direct mode with parallel e.g.

sqlldr control=control.ctl parallel=true direct=true

Now that I have my data loaded into these "staging" tables I'm using "extract()"

and

sys_XMLGen

to extract only the required XML from the staging tables and copy into reporting tables. I might produce views to sit on top of the reporting tables for ease of use.

Anyway guys, thanks for the help :)

What patch level are you on? In 11.2.0.3 I get "ORA-31172: cannot load XMLType column using direct path". What is the DDL for the table creation? You seem to have a nested table, from the generated table name? — Alex Poole, Mar 10 '15 at 17:37
Using: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production. I have an XMLType table not column. I've added XDB schema attributes to my Schema so that I can better control what is generated. This has *seemed* to work but I'm getting a "ORA-26086: direct path does not support triggers". When I look I see that the when Oracle has generated the objects based on the schema it created a trigger?!? — Lawrence Tierney, Mar 11 '15 at 10:04
This is the trigger (no idea what its for): create or replace trigger "SUKLTI"."XML_TABLE$xd" after delete or update on "SUKLTI"."XML_TABLE" for each row BEGIN IF (deleting) THEN xdb.xdb_pitrig_pkg.pitrig_del('SUKLTI','XML_TABLE', :old.sys_nc_oid$, '1100C2917AF12ADEE053E5D81DAC36D4' ); END IF; IF (updating) THEN xdb.xdb_pitrig_pkg.pitrig_upd('SUKLTI','XML_TABLE', :old.sys_nc_oid$, '1100C2917AF12ADEE053E5D81DAC36D4', user ); END IF; END; — Lawrence Tierney, Mar 11 '15 at 10:09
I couldn't get anything like this with a table of XMLType either, with an XMLSchema. Can you add the info, including the table creation statement and any other DDL, to the question as an edit (not as comments where they're hard to read)? — Alex Poole, Mar 11 '15 at 10:22
With that XSD and registration, I get a 39 objects created, including the SYS_NT... and the PURCHASEORDER$xd trigger. SQL\*Loader gets "SQL*Loader-937: parallel load requested and PURCHASEORDER has enabled triggers or constraints" with parallel=true or "ORA-26086: direct path does not support triggers" without; oddly when I go back to parallel it sticks with ORA-26086, as the trigger is now disabled. I have not yet seen ORA-26002 though. (Also on 11.2.0.3). — Alex Poole, Mar 13 '15 at 16:12

score 1 · Answer 1 · answered Mar 13 '15 at 16:19

As an alternative approach, you could use an external staging table instead of SQL*Loader; something like:

create table xml_stage (xmldata_clob clob)
organization external (
  type oracle_loader
  default directory XML_DIR
  access parameters (
    fields (filename char(120))
    column transforms (xmldata_clob from lobfile(filename))
  )
  location ('filelist2.dat')
);

insert into purchaseorder select xmltype(xmldata_clob) from xml_stage;

But that would mean having the file list and the files on the server, in an Oracle-visible directory.

Oracle SQL*Loader not honoring skip_index_maintenance parameter

1 Answers1