7 Replies Latest reply: Aug 27, 2012 6:27 PM by jmcnaug2 RSS

    InPlace Schema Evolution Error: ORA-31166: internal error code to etc...

    jmcnaug2
      Hello there,

      I have a table in which I'm going to be storing an XMLTYPE column based on an XML Schema. I am using the following version of the database:
      SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 23 18:20:12 2012
      
      Copyright (c) 1982, 2010, Oracle.  All rights reserved.
      
      
      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      Now, when I run my InPlace Schema Evolution test, I am getting the following error message:

      <font color="red">
      ORA-31166: internal error code to handle ignorable attributes
      </font>

      I have built a re-producable test case for the purpose of this forum post. To begin, I have a database schema granted the following privileges:
      DIRECTLY GRANTED SYSTEM PRIVS
      =============================
      CREATE TRIGGER
      ALTER SESSION
      CREATE TABLE
      
      EXECUTE PRIVS (SYS-OWNED PACKAGES)
      =============
      EXECUTE ON DBMS_XMLSCHEMA
      
      ROLES GRANTED
      =============
      CONNECT
      DATA_ROLE
      
      DATA_ROLE DDL
      =============
      create role DATA_ROLE not identified;
      grant create database link to DATA_ROLE;
      GRANT CREATE MATERIALIZED VIEW TO DATA_ROLE;
      grant create procedure to DATA_ROLE;
      grant create sequence to DATA_ROLE;
      GRANT CREATE SYNONYM TO DATA_ROLE;
      grant create table to DATA_ROLE with admin option;
      grant create trigger to DATA_ROLE;
      grant create view to DATA_ROLE;
      grant select any dictionary to DATA_ROLE;
      All further scripts are executed as this database schema. For the purpose of clarity, let's call it the {*}TEST_DB_SCHEMA{*}. I am performing the following steps in this schema:

      h3. 1. Run the this PL/SQL script to register Version 1 of my XML Schema:
      PROMPT -------------------------------;
      PROMPT INITIAL XML SCHEMA REGISTRATION;
      PROMPT -------------------------------;
      
      SET SERVEROUTPUT ON
      SET FEEDBACK OFF
      
      DECLARE
         l_xsd_url_con CONSTANT VARCHAR2(60) := 'http://home/xsd/test_binXML_v1.xsd';
         
         l_xml_schema VARCHAR2(32767);
         
         E_XSD_ALREADY_REGISTERED EXCEPTION;
         PRAGMA EXCEPTION_INIT(E_XSD_ALREADY_REGISTERED, -31085);
      BEGIN
         l_xml_schema :=
            '<xs:schema'||CHR(10)||
            'xmlns:xs="http://www.w3.org/2001/XMLSchema"'||CHR(10)||
            'xmlns:xdb="http://xmlns.oracle.com/xdb"'||CHR(10)||
            'version="1.0"'||CHR(10)||
            'xdb:storeVarrayAsTable = "true">'||CHR(10)||
            ' <xs:element name="TransactionBatch" type="TransactionBatchType" xdb:defaultTable="XSD_TEST_BATCH_BINXML1"/>'||CHR(10)||
            ' <xs:complexType name="TransactionBatchType">'||CHR(10)||
            '  <xs:sequence>'||CHR(10)||
            '   <xs:element name="Transaction" type="TransactionType" minOccurs="1" maxOccurs="unbounded" xdb:defaultTable="XSD_TEST_TRANSACTION_BINXML1"/>'||CHR(10)||
            '  </xs:sequence>'||CHR(10)||
            ' </xs:complexType>'||CHR(10)||
            ' <xs:complexType name="TransactionType">'||CHR(10)||
            '  <xs:sequence>'||CHR(10)||
            '   <xs:element name="Portal" type="PortalType" minOccurs="1" maxOccurs="1" />'||CHR(10)||
            '   <xs:element name="CustomerId" type="CustomerIdType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
            '   <xs:element name="TransactionQuantity" type="TransactionQuantityType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
            '   <xs:element name="AppTransactionId" type="AppTransactionIdType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
            '   <xs:element name="TransactionDetails" type="TransactionDetailType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
            '   <xs:element name="Username" type="UsernameType" minOccurs="1" maxOccurs="1" />'||CHR(10)||
            '  </xs:sequence>'||CHR(10)||
            ' </xs:complexType>'||CHR(10)||
            ' <xs:simpleType name="PortalType">'||CHR(10)||
            '  <xs:restriction base="xs:string">'||CHR(10)||
            '   <xs:minLength value="1"/>'||CHR(10)||
            '   <xs:maxLength value="30"/>'||CHR(10)||
            '   <xs:enumeration value="TEST1"/>'||CHR(10)||
            '   <xs:enumeration value="TEST2"/>'||CHR(10)||
            '  </xs:restriction>'||CHR(10)||
            ' </xs:simpleType>'||CHR(10)||
            ' <xs:simpleType name="CustomerIdType">'||CHR(10)||
            '  <xs:restriction base="xs:string"/>'||CHR(10)||
            ' </xs:simpleType>'||CHR(10)||
            ' <xs:simpleType name="TransactionQuantityType">'||CHR(10)||
            '  <xs:restriction base="xs:integer">'||CHR(10)||
            '   <xs:minExclusive value="0"/>'||CHR(10)||
            '  </xs:restriction>'||CHR(10)||
            ' </xs:simpleType>'||CHR(10)||
            ' <xs:simpleType name="AppTransactionIdType">'||CHR(10)||
            '  <xs:restriction base="xs:string">'||CHR(10)||
            '   <xs:minLength value="1"/>'||CHR(10)||
            '   <xs:maxLength value="20"/>'||CHR(10)||
            '  </xs:restriction>'||CHR(10)||
            ' </xs:simpleType>'||CHR(10)||
            ' <xs:simpleType name="TransactionDetailType">'||CHR(10)||
            '  <xs:restriction base="xs:string">'||CHR(10)||
            '   <xs:minLength value="1"/>'||CHR(10)||
            '  </xs:restriction>'||CHR(10)||
            ' </xs:simpleType>'||CHR(10)||
            ' <xs:simpleType name="UsernameType">'||CHR(10)||
            '  <xs:restriction base="xs:string">'||CHR(10)||
            '   <xs:minLength value="1"/>'||CHR(10)||
            '   <xs:maxLength value="30"/>'||CHR(10)||
            '  </xs:restriction>'||CHR(10)||
            ' </xs:simpleType>'||CHR(10)||
            '</xs:schema>';
         
         DBMS_XMLSCHEMA.REGISTERSCHEMA(
            schemaurl       => l_xsd_url_con,
            schemadoc       => l_xml_schema,
            local           => TRUE,                             -- XSD must only be visible to the Owner DB Schema in which this script is run.
            genTypes        => FALSE,                            -- Don't want to generate object types, as we're using Binary XML.
            force           => FALSE,                            -- Raise all errors on creation
            owner           => USER,                             -- XML Schema will belong to the currently executing user.
            options         => DBMS_XMLSCHEMA.REGISTER_BINARYXML -- We wish to register the XSD as Binary XML.
         );
         
      EXCEPTION
         WHEN E_XSD_ALREADY_REGISTERED
         THEN
            DBMS_OUTPUT.PUT_LINE('XML Schema '||l_xsd_url_con||' is already registered.');
            
      END;
      /
      
      SET FEEDBACK ON
      h3. 2. Create a Heap Table with an XMLTYPE column based on version 1 of my XML Schema:
      PROMPT ----------------------;
      PROMPT ONE-OFF DDL STATEMENTS;
      PROMPT ----------------------;
      
      DROP TABLE test_batch;
      DROP SEQUENCE seq_test_batch_id;
      
      CREATE TABLE test_batch
      (
         batch_id        NUMBER(20),
         batch_timestamp TIMESTAMP   DEFAULT SYSTIMESTAMP,
         batch_xml       XMLTYPE,
         CONSTRAINT tbh_batch_id_nn        CHECK(batch_id IS NOT NULL),
         CONSTRAINT tbh_batch_timestamp_nn CHECK(batch_timestamp IS NOT NULL),
         CONSTRAINT tbh_batch_xml_nn       CHECK(batch_xml IS NOT NULL)
      )
      XMLTYPE COLUMN batch_xml STORE AS SECUREFILE BINARY XML
      XMLSCHEMA "http://home/xsd/test_binXML_v1.xsd"
      ELEMENT "TransactionBatch"
      PARTITION BY RANGE(batch_timestamp)
      INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
      (
         PARTITION P_TEST_BATCH_201208 VALUES LESS THAN (TO_DATE('01-SEP-2012', 'DD-MON-YYYY')) TABLESPACE &&TABLSPACE_OF_YOUR_CHOICE
      );
      
      CREATE UNIQUE INDEX idx_tbh_batch_id ON test_batch(batch_id)
      TABLESPACE TXTREME_IDX_XLG;
      
      ALTER TABLE test_batch
         ADD CONSTRAINT tbh_batch_id_pk PRIMARY KEY (batch_id) USING INDEX idx_tbh_batch_id;
      
      CREATE SEQUENCE seq_test_batch_id
      START WITH 1
      INCREMENT BY 1
      MAXVALUE 99999999999999999999
      CYCLE
      NOCACHE
      NOORDER;
      h3. 3. Populate this table with test data.
      PROMPT --------------------------;
      PROMPT CREATING INITIAL TEST DATA;
      PROMPT --------------------------;
      
      TRUNCATE TABLE TEST_BATCH;
      
      SET TIMING ON
      DECLARE
         l_batch_count_con CONSTANT NUMBER(4) := 200;
         
         l_transaction_count   PLS_INTEGER;
         l_total_transactions  PLS_INTEGER DEFAULT 0;
         l_transaction_xmlclob CLOB;
         l_transaction_xmldoc  XMLTYPE;
      BEGIN
         FOR idx IN 1..l_batch_count_con
         LOOP
            l_transaction_count := TRUNC(DBMS_RANDOM.VALUE(1,100));
            
            -- Initialise XML CLOB String. 
            l_transaction_xmlclob := '<?xml version="1.0"?>'||CHR(10)||'<TransactionBatch>';
            
            -- Insert the contents of the XML CLOB String.
            FOR idx IN 1 .. l_transaction_count
            LOOP
               l_transaction_xmlclob := l_transaction_xmlclob||CHR(10)||
                                        '  <Transaction>'||CHR(10)||
                                        '    <Portal>TEST1</Portal>'||CHR(10)||
                                        '    <CustomerId>1234567891</CustomerId>'||CHR(10)||
                                        '    <TransactionQuantity>4</TransactionQuantity>'||CHR(10)||
                                        '    <AppTransactionId>1232K89C</AppTransactionId>'||CHR(10)||
                                        '    <TransactionDetails>This is another long free text field</TransactionDetails>'||
                                        '    <Username>Jimbo2</Username>'||CHR(10)||
                                        '  </Transaction>';
            END LOOP;
            
            --Terminate XML CLOB String.
            l_transaction_xmlclob := l_transaction_xmlclob||CHR(10)||'</TransactionBatch>';
            
            -- Create XMLTYPE XML Document.
            l_transaction_xmldoc := XMLTYPE.createXML(l_transaction_xmlclob);
            
            -- Populate the TEST_BATCH Table.
            INSERT INTO test_batch
              ( batch_id,
                batch_timestamp,
                batch_xml
              )
            VALUES
              ( SEQ_TEST_BATCH_ID.NEXTVAL,
                SYSTIMESTAMP,
                l_transaction_xmldoc
              );
              
            l_total_transactions := l_total_transactions + l_transaction_count;
         END LOOP;
         
         DBMS_OUTPUT.PUT_LINE('Inserted a total of '||l_total_transactions||' transaction XML pieces across '||l_batch_count_con||' Test Batches.');
         COMMIT;
         DBMS_OUTPUT.PUT_LINE('Commit complete.');
      END;
      /
      
      SET TIMING OFF
      h3. 4. Register Version 2 of my XML Schema
      PROMPT -----------------------------------------------------------------------------------;
      PROMPT NEW XML SCHEMA REGISTRATION - IDENTICAL TO OLD SCHEMA EXCEPT FOR NEW <Test> ELEMENT;
      PROMPT -----------------------------------------------------------------------------------;
      
      SET FEEDBACK OFF
      
      DECLARE
         l_xsd_url_con CONSTANT VARCHAR2(60) := 'http://home/xsd/test_binXML_v2.xsd';
         
         l_xml_schema VARCHAR2(32767);
         
         E_XSD_ALREADY_REGISTERED EXCEPTION;
         PRAGMA EXCEPTION_INIT(E_XSD_ALREADY_REGISTERED, -31085);
      BEGIN
         l_xml_schema :=
            '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" version="1.0" xdb:storeVarrayAsTable="true">'||CHR(10)||
            '<xs:element name="TransactionBatch" type="TransactionBatchType" xdb:defaultTable="XSD_TEST_BATCH_BINXML2"/>'||CHR(10)||
            '<xs:complexType name="TransactionBatchType">'||CHR(10)||
            ' <xs:sequence>'||CHR(10)||
            '  <xs:element name="Transaction" type="TransactionType" minOccurs="1" maxOccurs="unbounded" xdb:defaultTable="XSD_TEST_TRANSACTION_BINXML2"/>'||CHR(10)||
            ' </xs:sequence>'||CHR(10)||
            '</xs:complexType>'||CHR(10)||
            '<xs:complexType name="TransactionType">'||CHR(10)||
            ' <xs:sequence>'||CHR(10)||
            '  <xs:element name="Portal" type="PortalType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
            '  <xs:element name="CustomerId" type="CustomerIdType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
            '  <xs:element name="TransactionQuantity" type="TransactionQuantityType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
            '  <xs:element name="AppTransactionId" type="AppTransactionIdType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
            '  <xs:element name="TransactionDetails" type="TransactionDetailType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
            '  <xs:element name="Username" type="UsernameType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
            '  <xs:element name="Test" type="TestType" minOccurs="0" maxOccurs="1"/>'||CHR(10)||
            ' </xs:sequence>'||CHR(10)||
            '</xs:complexType>'||CHR(10)||
            '<xs:simpleType name="PortalType">'||CHR(10)||
            ' <xs:restriction base="xs:string">'||CHR(10)||
            '  <xs:minLength value="1"/>'||CHR(10)||
            '  <xs:maxLength value="30"/>'||CHR(10)||
            '  <xs:enumeration value="TEST1"/>'||CHR(10)||
            '  <xs:enumeration value="TEST2"/>'||CHR(10)||
            ' </xs:restriction>'||CHR(10)||
            '</xs:simpleType>'||CHR(10)||
            '<xs:simpleType name="CustomerIdType">'||CHR(10)||
            ' <xs:restriction base="xs:string"/>'||CHR(10)||
            '</xs:simpleType>'||CHR(10)||
            '<xs:simpleType name="TransactionQuantityType">'||CHR(10)||
            ' <xs:restriction base="xs:integer">'||CHR(10)||
            '  <xs:minExclusive value="0"/>'||CHR(10)||
            ' </xs:restriction>'||CHR(10)||
            '</xs:simpleType>'||CHR(10)||
            '<xs:simpleType name="AppTransactionIdType">'||CHR(10)||
            ' <xs:restriction base="xs:string">'||CHR(10)||
            '  <xs:minLength value="1"/>'||CHR(10)||
            '  <xs:maxLength value="20"/>'||CHR(10)||
            ' </xs:restriction>'||CHR(10)||
            '</xs:simpleType>'||CHR(10)||
            '<xs:simpleType name="TransactionDetailType">'||CHR(10)||
            ' <xs:restriction base="xs:string">'||CHR(10)||
            '  <xs:minLength value="1"/>'||CHR(10)||
            ' </xs:restriction>'||CHR(10)||
            '</xs:simpleType>'||CHR(10)||
            '<xs:simpleType name="UsernameType">'||CHR(10)||
            ' <xs:restriction base="xs:string">'||CHR(10)||
            '  <xs:minLength value="1"/>'||CHR(10)||
            '  <xs:maxLength value="30"/>'||CHR(10)||
            ' </xs:restriction>'||CHR(10)||
            '</xs:simpleType>'||CHR(10)||
            '<xs:simpleType name="TestType">'||CHR(10)||
            ' <xs:restriction base="xs:string">'||CHR(10)||
            '  <xs:minLength value="1"/>'||CHR(10)||
            '  <xs:maxLength value="30"/>'||CHR(10)||
            ' </xs:restriction>'||CHR(10)||
            '</xs:simpleType>'||CHR(10)||
            '</xs:schema>';
         
         DBMS_XMLSCHEMA.REGISTERSCHEMA(
            schemaurl       => l_xsd_url_con,
            schemadoc       => l_xml_schema,
            local           => TRUE,                             -- XSD must only be visible to the Owner DB Schema in which this script is run.
            genTypes        => FALSE,                            -- Don't want to generate object types, as we're using Binary XML.
            force           => FALSE,                            -- Raise all errors on creation
            owner           => USER,                             -- XML Schema will belong to the currently executing user.
            options         => DBMS_XMLSCHEMA.REGISTER_BINARYXML -- We wish to register the XSD as Binary XML.
         );
         
      EXCEPTION
         WHEN E_XSD_ALREADY_REGISTERED
         THEN
            DBMS_OUTPUT.PUT_LINE('XML Schema '||l_xsd_url_con||' is already registered.');
            
      END;
      /
      
      SET FEEDBACK ON
      h3. 5. Run a test to insert a new XML document conforming to the second XML schema. This test fails as expected.
      PROMPT ---------------------------------------------------------------;
      PROMPT ATTEMPT TO INSERT V2 XSD-COMPLIANT XMLDOC INTO TEST_BATCH TABLE;
      PROMPT ---------------------------------------------------------------;
      
      DECLARE
         l_transaction_xmldoc XMLTYPE;
         
         E_XSD_VALIDATION_FAILURE EXCEPTION;
         PRAGMA EXCEPTION_INIT(E_XSD_VALIDATION_FAILURE, -31061);
      BEGIN
         l_transaction_xmldoc := XMLTYPE.createXML(
                                    '<?xml version="1.0" encoding="UTF-8"?>'||CHR(10)||
                                    '<TransactionBatch>'||CHR(10)||
                                    '  <Transaction>'||CHR(10)||
                                    '    <Portal>CARMAN</Portal>'||CHR(10)||
                                    '    <CustomerId>1234567891</CustomerId>'||CHR(10)||
                                    '    <TransactionQuantity>4</TransactionQuantity>'||CHR(10)||
                                    '    <AppTransactionId>1232K89C</AppTransactionId>'||CHR(10)||
                                    '    <TransactionDetails>This is a long free text field</TransactionDetails>'||CHR(10)||
                                    '    <Username>Jimbo2</Username>'||CHR(10)||
                                    '    <Test>This is an In-Place Schema Evolution Test</Test>'||CHR(10)||
                                    '  </Transaction>'||CHR(10)||
                                    '</TransactionBatch>'
                                 );
         
         -- Attempt to populate the SUBMISSION_BATCH Table.
         INSERT INTO submission_batch
           ( batch_id,
             batch_timestamp,
             batch_xml
           )
         VALUES
           ( SEQ_SUB_BATCH_ID.NEXTVAL,
             SYSTIMESTAMP,
             l_transaction_xmldoc
           );
         
         DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' row inserted.');
         COMMIT;
         DBMS_OUTPUT.PUT_LINE('Commit complete.');
      
      EXCEPTION
         WHEN E_XSD_VALIDATION_FAILURE
         THEN
            DBMS_OUTPUT.PUT_LINE('Insert failed as expected.'||CHR(10));
            DBMS_OUTPUT.PUT_LINE(SQLERRM);
      END;
      /
      h3. 6. Attempt Inplace Evolution of Version 1 of the XML Schema into Version 2.
      PROMPT --------------------------------------------------------------------------------------;
      PROMPT PERFORM IN-PLACE EVOLUTION AND DE-REGISTER/PURGE OLD SCHEMA IF EVOLUTION IS SUCCESSFUL;
      PROMPT --------------------------------------------------------------------------------------;
      
      SET TIMING ON
      
      DECLARE
         l_current_schema   XMLTYPE;
         l_new_schema       XMLTYPE;
         l_xsd_differences  XMLTYPE;
         
         l_resource         BOOLEAN;
      BEGIN
         SELECT schema
           INTO l_current_schema
           FROM user_xml_schemas
          WHERE schema_url = 'http://home/xsd/test_binXML_v1.xsd';
         
         SELECT schema
           INTO l_new_schema
           FROM user_xml_schemas
          WHERE schema_url = 'http://home/xsd/test_binXML_v2.xsd';
         
         SELECT XMLDIFF(l_current_schema, l_new_schema)
           INTO l_xsd_differences
           FROM dual;
         
         DBMS_OUTPUT.PUT_LINE('Successfully extrapolated XDIFF XML Document.');
         
         DBMS_XMLSCHEMA.inPlaceEvolve( 'http://home/xsd/test_binXML_v1.xsd',
                                       l_xsd_differences,
                                       DBMS_XMLSCHEMA.INPLACE_TRACE
                                     );
         
         DBMS_OUTPUT.PUT_LINE('Successfully evolved original XML Schema.');
         
      EXCEPTION
         WHEN OTHERS
         THEN
            ROLLBACK;
            DBMS_OUTPUT.PUT_LINE('Error issued during InPlace XML Schema Evolution:');
            DBMS_OUTPUT.PUT_LINE(SQLERRM);
      END;
      /
      h3. The above scripts provide the following Test Output:
      Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
      Connected as TEST_DB_SCHEMA@dbs02local
       
      SQL> 
      -------------------------------
      INITIAL XML SCHEMA REGISTRATION
      -------------------------------
       
      XML Schema http://home/xsd/test_binXML_v1.xsd is already registered.
       
      Executed in 0 seconds
      
      ----------------------
      ONE-OFF DDL STATEMENTS
      ----------------------
        
      Table created
       
      Executed in 0.016 seconds
       
      Index created
       
      Executed in 0.016 seconds
       
      Table altered
       
      Executed in 0 seconds
       
      Sequence created
       
      Executed in 0 seconds
      
      --------------------------
      CREATING INITIAL TEST DATA
      --------------------------
       
      Table truncated
       
      Executed in 0.032 seconds
      
      Inserted a total of 10259 transaction XML pieces across 200 Test Batches.
      Commit complete.
       
      PL/SQL procedure successfully completed
       
      Executed in 8.487 seconds
      
      -----------------------------------------------------------------------------------
      NEW XML SCHEMA REGISTRATION - IDENTICAL TO OLD SCHEMA EXCEPT FOR NEW <Test> ELEMENT
      -----------------------------------------------------------------------------------
       
      XML Schema http://home/xsd/test_binXML_v2.xsd is already registered.
      
      ---------------------------------------------------------------
      ATTEMPT TO INSERT V2 XSD-COMPLIANT XMLDOC INTO TEST_BATCH TABLE
      ---------------------------------------------------------------
       
      Insert failed as expected.
      
      ORA-31061: XDB error: XML event error
      ORA-19202: Error occurred in XML processing
      LSX-00213: only 0 occurrences of particle "Transaction", minimum is 1
       
      PL/SQL procedure successfully completed
       
      --------------------------------------------------------------------------------------
      PERFORM IN-PLACE EVOLUTION AND DE-REGISTER/PURGE OLD SCHEMA IF EVOLUTION IS SUCCESSFUL
      --------------------------------------------------------------------------------------
       
      Successfully extrapolated XDIFF XML Document.
      Error issued during InPlace XML Schema Evolution:
      ORA-31166: internal error code to handle ignorable attributes
       
      PL/SQL procedure successfully completed
       
      Executed in 0.046 seconds
      SQL> 
      NOTE: When I ran the above tests, I'd already created the test XML schemas; hence the output messages saying so...

      h4. Now, bearing in mind that this is the first time I've ever attempted XML Schema evolution in Oracle XMLDB, is there anybody who could potentially point the way please, re. resolving the <font color="red">ORA-31166</font> error?
        • 1. Re: InPlace Schema Evolution Error: ORA-31166: internal error code to etc...
          odie_63
          Hi,

          First of all, thanks for your efforts on providing a decent test case.

          There are a couple of inconsistencies though :

          - Step 5 attempts to insert into SUBMISSION_BATCH, but the table created above is TEST_BATCH
          - Same remark for the sequence name

          Now back to the real issue...

          I think the problem comes from the fact that you're running XMLDiff against two registered versions of your schemas.
          When Oracle registers a schema, it adds a lot of attributes for internal use (you can see them by querying USER_XML_SCHEMAS.SCHEMA) but we, as end users, don't have to bother about them.
          However, by building the Xdiff document based on these two "internal" versions, we explicitely tell Oracle which internal attributes must be changed when evolving the schema, leading to potential conflicts.

          A few other comments :
          - Since you're using Binary XML schema, you also have to set "genTables => false" in addition to "genTypes => false".
          - Also remove all <tt>xdb:defaultTable</tt> and <tt>xdb:storeVarrayAsTable</tt> annotations from the schemas, they're of no use in this situation.
          - Do not register the v2 schema, it's not necessary. Just use a transient instance when calling XMLDiff

          So, to sum up :

          - run XMLDiff against the two original versions of the schemas (don't register the new one anyway, the evolve process will take care of replacing the old one).
          - of course, don't forget to change INPLACE_TRACE to INPLACE_EVOLVE when ready :)

          Hope that helps.

          Edited by: odie_63 on 23 août 2012 16:31 - fixed some typos
          • 2. Re: InPlace Schema Evolution Error: ORA-31166: internal error code to etc...
            jmcnaug2
            Hi Odie,

            Thanks for supplying such a quick response. I will give it a go and report back. I apologise for the inconsistency in my fifth script aswell. It was the only one I didn't run as part of my wider test case, as it was only a single row insert. I was trying to mask the name of our genuine table... ;)

            In terms of the approach I took, it may be of interest for you to read that my testing in this area was driven by the following tutorial:

            http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/datamgmt/xmldb2_a/xmldb2_a.htm

            This tutorial uses XDBURITYPE to retrieve the XSD definitions from the repository, but it is using an abosolute path setup in XDB Repository rather than schemas registered via DBMS_XMLSCHEMA.REGISTERSCHEMA. I actually tried the XDBURITYPE method to retrieve the schemas I'd registered in the repository via DBMS_XMLSCHEMA.REGISTERSCHEMA, but the evolution failed with an error message informing me that I'd attempted an illegal schema evolution operation. I'm assuming this is because my local schemas were registered under */sys/schemas/<db_username>/* , and that the kernal will not allow this operation on schemas containing the Oracle-specific annotation that are registered under the sys path.

            To be honest, I find Oracle's XDB documentation a little challenging to unify into a single whole, as it looks like it was written by a number of different people at different times. It is fulll of inconsistencies, e.g. talking about using existsNode, extract and extractValue in one section, whereas stating in a later section that these functions are being deprecated as of 11g Release 2. I find some of their examples are done using XDB reposiitory, and some aren't. I'm finding it difficult to figure out whether or not one should actively use XDB Repository, and what the use case would be, as there is no best practise summary for this feature of XMLDB. The conclusion I've come to is that the repository is intended for use if one wishes to store XML, XSL and XSD documents off-database, in a filesystem, and that the repository exists to point to these files. Would you say that's a reasonable summary? ;)

            Once again though, thanks for your help. I'll report back with my findings.

            Cheers.

            James
            • 3. Re: InPlace Schema Evolution Error: ORA-31166: internal error code to etc...
              jmcnaug2
              Odie, many thanks; your suggestions worked wonders. Here are the main differences re. what I've done:

              h3. 1. As you suggested, removed all xdb-specific annotations from my first XML Schema, and then registered it. Script is here.
              PROMPT -------------------------------;
              PROMPT INITIAL XML SCHEMA REGISTRATION;
              PROMPT -------------------------------;
              
              SET SERVEROUTPUT ON
              SET FEEDBACK OFF
              
              DECLARE
                 l_xsd_url_con CONSTANT VARCHAR2(60) := 'http://home/xsd/test_binXML_v1.xsd';
                 
                 l_xml_schema VARCHAR2(32767);
                 
                 E_XSD_ALREADY_REGISTERED EXCEPTION;
                 PRAGMA EXCEPTION_INIT(E_XSD_ALREADY_REGISTERED, -31085);
              BEGIN
                 l_xml_schema :=
                    '<xs:schema'||CHR(10)||
                    'xmlns:xs="http://www.w3.org/2001/XMLSchema"'||CHR(10)||
                    'xmlns:xdb="http://xmlns.oracle.com/xdb"'||CHR(10)||
                    'version="1.0">'||CHR(10)||
                    ' <xs:element name="TransactionBatch" type="TransactionBatchType"/>'||CHR(10)||
                    ' <xs:complexType name="TransactionBatchType">'||CHR(10)||
                    '  <xs:sequence>'||CHR(10)||
                    '   <xs:element name="Transaction" type="TransactionType" minOccurs="1" maxOccurs="unbounded"/>'||CHR(10)||
                    '  </xs:sequence>'||CHR(10)||
                    ' </xs:complexType>'||CHR(10)||
                    ' <xs:complexType name="TransactionType">'||CHR(10)||
                    '  <xs:sequence>'||CHR(10)||
                    '   <xs:element name="Portal" type="PortalType" minOccurs="1" maxOccurs="1" />'||CHR(10)||
                    '   <xs:element name="CustomerId" type="CustomerIdType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
                    '   <xs:element name="TransactionQuantity" type="TransactionQuantityType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
                    '   <xs:element name="AppTransactionId" type="AppTransactionIdType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
                    '   <xs:element name="TransactionDetails" type="TransactionDetailType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
                    '   <xs:element name="Username" type="UsernameType" minOccurs="1" maxOccurs="1" />'||CHR(10)||
                    '  </xs:sequence>'||CHR(10)||
                    ' </xs:complexType>'||CHR(10)||
                    ' <xs:simpleType name="PortalType">'||CHR(10)||
                    '  <xs:restriction base="xs:string">'||CHR(10)||
                    '   <xs:minLength value="1"/>'||CHR(10)||
                    '   <xs:maxLength value="30"/>'||CHR(10)||
                    '   <xs:enumeration value="TEST1"/>'||CHR(10)||
                    '   <xs:enumeration value="TEST2"/>'||CHR(10)||
                    '  </xs:restriction>'||CHR(10)||
                    ' </xs:simpleType>'||CHR(10)||
                    ' <xs:simpleType name="CustomerIdType">'||CHR(10)||
                    '  <xs:restriction base="xs:string"/>'||CHR(10)||
                    ' </xs:simpleType>'||CHR(10)||
                    ' <xs:simpleType name="TransactionQuantityType">'||CHR(10)||
                    '  <xs:restriction base="xs:integer">'||CHR(10)||
                    '   <xs:minExclusive value="0"/>'||CHR(10)||
                    '  </xs:restriction>'||CHR(10)||
                    ' </xs:simpleType>'||CHR(10)||
                    ' <xs:simpleType name="AppTransactionIdType">'||CHR(10)||
                    '  <xs:restriction base="xs:string">'||CHR(10)||
                    '   <xs:minLength value="1"/>'||CHR(10)||
                    '   <xs:maxLength value="20"/>'||CHR(10)||
                    '  </xs:restriction>'||CHR(10)||
                    ' </xs:simpleType>'||CHR(10)||
                    ' <xs:simpleType name="TransactionDetailType">'||CHR(10)||
                    '  <xs:restriction base="xs:string">'||CHR(10)||
                    '   <xs:minLength value="1"/>'||CHR(10)||
                    '  </xs:restriction>'||CHR(10)||
                    ' </xs:simpleType>'||CHR(10)||
                    ' <xs:simpleType name="UsernameType">'||CHR(10)||
                    '  <xs:restriction base="xs:string">'||CHR(10)||
                    '   <xs:minLength value="1"/>'||CHR(10)||
                    '   <xs:maxLength value="30"/>'||CHR(10)||
                    '  </xs:restriction>'||CHR(10)||
                    ' </xs:simpleType>'||CHR(10)||
                    '</xs:schema>';
                 
                 DBMS_XMLSCHEMA.REGISTERSCHEMA(
                    schemaurl => l_xsd_url_con,
                    schemadoc => l_xml_schema,
                    local     => TRUE,                             -- XSD must only be visible to the Owner DB Schema in which this script is run.
                    genTables => FALSE,                            -- Since we're registering Binary XML, we don't need a hidden table creating.
                    genTypes  => FALSE,                            -- Don't want to generate object types, as we're using Binary XML.
                    force     => FALSE,                            -- Raise all errors on creation
                    owner     => USER,                             -- XML Schema will belong to the currently executing user.
                    options   => DBMS_XMLSCHEMA.REGISTER_BINARYXML -- We wish to register the XSD as Binary XML.
                 );
                 
              EXCEPTION
                 WHEN E_XSD_ALREADY_REGISTERED
                 THEN
                    DBMS_OUTPUT.PUT_LINE('XML Schema '||l_xsd_url_con||' is already registered.');
                    
              END;
              /
              I also followed through on setting the "genTables" parameter to FALSE.

              h3. 2. As you (once again) suggested, performed the XDIFF between the original two XML schemas, rather than the registered equivalents in the XDB Repository.
              PROMPT --------------------------;
              PROMPT PERFORM IN-PLACE EVOLUTION;
              PROMPT --------------------------;
              
              SET FEEDBACK ON
              SET TIMING ON
              
              DECLARE
                 l_old_xml_schema  XMLTYPE;
                 l_new_xml_schema  XMLTYPE;
                 l_xsd_differences XMLTYPE;
              BEGIN
                 l_old_xml_schema := XMLTYPE.createXML(
                                        '<xs:schema'||CHR(10)||
                                        'xmlns:xs="http://www.w3.org/2001/XMLSchema"'||CHR(10)||
                                        'xmlns:xdb="http://xmlns.oracle.com/xdb"'||CHR(10)||
                                        'version="1.0">'||CHR(10)||
                                        ' <xs:element name="TransactionBatch" type="TransactionBatchType"/>'||CHR(10)||
                                        ' <xs:complexType name="TransactionBatchType">'||CHR(10)||
                                        '  <xs:sequence>'||CHR(10)||
                                        '   <xs:element name="Transaction" type="TransactionType" minOccurs="1" maxOccurs="unbounded"/>'||CHR(10)||
                                        '  </xs:sequence>'||CHR(10)||
                                        ' </xs:complexType>'||CHR(10)||
                                        ' <xs:complexType name="TransactionType">'||CHR(10)||
                                        '  <xs:sequence>'||CHR(10)||
                                        '   <xs:element name="Portal" type="PortalType" minOccurs="1" maxOccurs="1" />'||CHR(10)||
                                        '   <xs:element name="CustomerId" type="CustomerIdType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
                                        '   <xs:element name="TransactionQuantity" type="TransactionQuantityType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
                                        '   <xs:element name="AppTransactionId" type="AppTransactionIdType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
                                        '   <xs:element name="TransactionDetails" type="TransactionDetailType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
                                        '   <xs:element name="Username" type="UsernameType" minOccurs="1" maxOccurs="1" />'||CHR(10)||
                                        '  </xs:sequence>'||CHR(10)||
                                        ' </xs:complexType>'||CHR(10)||
                                        ' <xs:simpleType name="PortalType">'||CHR(10)||
                                        '  <xs:restriction base="xs:string">'||CHR(10)||
                                        '   <xs:minLength value="1"/>'||CHR(10)||
                                        '   <xs:maxLength value="30"/>'||CHR(10)||
                                        '   <xs:enumeration value="TEST1"/>'||CHR(10)||
                                        '   <xs:enumeration value="TEST2"/>'||CHR(10)||
                                        '  </xs:restriction>'||CHR(10)||
                                        ' </xs:simpleType>'||CHR(10)||
                                        ' <xs:simpleType name="CustomerIdType">'||CHR(10)||
                                        '  <xs:restriction base="xs:string"/>'||CHR(10)||
                                        ' </xs:simpleType>'||CHR(10)||
                                        ' <xs:simpleType name="TransactionQuantityType">'||CHR(10)||
                                        '  <xs:restriction base="xs:integer">'||CHR(10)||
                                        '   <xs:minExclusive value="0"/>'||CHR(10)||
                                        '  </xs:restriction>'||CHR(10)||
                                        ' </xs:simpleType>'||CHR(10)||
                                        ' <xs:simpleType name="AppTransactionIdType">'||CHR(10)||
                                        '  <xs:restriction base="xs:string">'||CHR(10)||
                                        '   <xs:minLength value="1"/>'||CHR(10)||
                                        '   <xs:maxLength value="20"/>'||CHR(10)||
                                        '  </xs:restriction>'||CHR(10)||
                                        ' </xs:simpleType>'||CHR(10)||
                                        ' <xs:simpleType name="TransactionDetailType">'||CHR(10)||
                                        '  <xs:restriction base="xs:string">'||CHR(10)||
                                        '   <xs:minLength value="1"/>'||CHR(10)||
                                        '  </xs:restriction>'||CHR(10)||
                                        ' </xs:simpleType>'||CHR(10)||
                                        ' <xs:simpleType name="UsernameType">'||CHR(10)||
                                        '  <xs:restriction base="xs:string">'||CHR(10)||
                                        '   <xs:minLength value="1"/>'||CHR(10)||
                                        '   <xs:maxLength value="30"/>'||CHR(10)||
                                        '  </xs:restriction>'||CHR(10)||
                                        ' </xs:simpleType>'||CHR(10)||
                                        '</xs:schema>'
                                     );
                 
                 l_new_xml_schema := XMLTYPE.createXML(
                                        '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" version="1.0">'||CHR(10)||
                                        '<xs:element name="TransactionBatch" type="TransactionBatchType"/>'||CHR(10)||
                                        '<xs:complexType name="TransactionBatchType">'||CHR(10)||
                                        ' <xs:sequence>'||CHR(10)||
                                        '  <xs:element name="Transaction" type="TransactionType" minOccurs="1" maxOccurs="unbounded"/>'||CHR(10)||
                                        ' </xs:sequence>'||CHR(10)||
                                        '</xs:complexType>'||CHR(10)||
                                        '<xs:complexType name="TransactionType">'||CHR(10)||
                                        ' <xs:sequence>'||CHR(10)||
                                        '  <xs:element name="Portal" type="PortalType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
                                        '  <xs:element name="CustomerId" type="CustomerIdType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
                                        '  <xs:element name="TransactionQuantity" type="TransactionQuantityType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
                                        '  <xs:element name="AppTransactionId" type="AppTransactionIdType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
                                        '  <xs:element name="TransactionDetails" type="TransactionDetailType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
                                        '  <xs:element name="Username" type="UsernameType" minOccurs="1" maxOccurs="1"/>'||CHR(10)||
                                        '  <xs:element name="Test" type="TestType" minOccurs="0" maxOccurs="1"/>'||CHR(10)||
                                        ' </xs:sequence>'||CHR(10)||
                                        '</xs:complexType>'||CHR(10)||
                                        '<xs:simpleType name="PortalType">'||CHR(10)||
                                        ' <xs:restriction base="xs:string">'||CHR(10)||
                                        '  <xs:minLength value="1"/>'||CHR(10)||
                                        '  <xs:maxLength value="30"/>'||CHR(10)||
                                        '  <xs:enumeration value="TEST1"/>'||CHR(10)||
                                        '  <xs:enumeration value="TEST2"/>'||CHR(10)||
                                        ' </xs:restriction>'||CHR(10)||
                                        '</xs:simpleType>'||CHR(10)||
                                        '<xs:simpleType name="CustomerIdType">'||CHR(10)||
                                        ' <xs:restriction base="xs:string"/>'||CHR(10)||
                                        '</xs:simpleType>'||CHR(10)||
                                        '<xs:simpleType name="TransactionQuantityType">'||CHR(10)||
                                        ' <xs:restriction base="xs:integer">'||CHR(10)||
                                        '  <xs:minExclusive value="0"/>'||CHR(10)||
                                        ' </xs:restriction>'||CHR(10)||
                                        '</xs:simpleType>'||CHR(10)||
                                        '<xs:simpleType name="AppTransactionIdType">'||CHR(10)||
                                        ' <xs:restriction base="xs:string">'||CHR(10)||
                                        '  <xs:minLength value="1"/>'||CHR(10)||
                                        '  <xs:maxLength value="20"/>'||CHR(10)||
                                        ' </xs:restriction>'||CHR(10)||
                                        '</xs:simpleType>'||CHR(10)||
                                        '<xs:simpleType name="TransactionDetailType">'||CHR(10)||
                                        ' <xs:restriction base="xs:string">'||CHR(10)||
                                        '  <xs:minLength value="1"/>'||CHR(10)||
                                        ' </xs:restriction>'||CHR(10)||
                                        '</xs:simpleType>'||CHR(10)||
                                        '<xs:simpleType name="UsernameType">'||CHR(10)||
                                        ' <xs:restriction base="xs:string">'||CHR(10)||
                                        '  <xs:minLength value="1"/>'||CHR(10)||
                                        '  <xs:maxLength value="30"/>'||CHR(10)||
                                        ' </xs:restriction>'||CHR(10)||
                                        '</xs:simpleType>'||CHR(10)||
                                        '<xs:simpleType name="TestType">'||CHR(10)||
                                        ' <xs:restriction base="xs:string">'||CHR(10)||
                                        '  <xs:minLength value="1"/>'||CHR(10)||
                                        '  <xs:maxLength value="60"/>'||CHR(10)||
                                        ' </xs:restriction>'||CHR(10)||
                                        '</xs:simpleType>'||CHR(10)||
                                        '</xs:schema>'
                                     );
                 
                 SELECT XMLDIFF(l_old_xml_schema, l_new_xml_schema)
                   INTO l_xsd_differences
                   FROM dual;
                 
                 DBMS_OUTPUT.PUT_LINE('Successfully extrapolated XDIFF XML Document.');
                 
                 DBMS_XMLSCHEMA.inPlaceEvolve( 'http://home/xsd/test_binXML_v1.xsd',
                                               l_xsd_differences,
                                               DBMS_XMLSCHEMA.INPLACE_EVOLVE
                                             );
                 
                 DBMS_OUTPUT.PUT_LINE('Successfully evolved original XML Schema.');
                 
              EXCEPTION
                 WHEN OTHERS
                 THEN
                    ROLLBACK;
                    DBMS_OUTPUT.PUT_LINE('Error issued during InPlace XML Schema Evolution:');
                    DBMS_OUTPUT.PUT_LINE(SQLERRM);
              END;
              /
              h3. 3. Finally, for completeness, here's a re-worked single insert script that should have been used for Step 5 in the example I gave above. ;)
              PROMPT ---------------------------------------------------------------;
              PROMPT ATTEMPT TO INSERT V2 XSD-COMPLIANT XMLDOC INTO TEST_BATCH TABLE;
              PROMPT ---------------------------------------------------------------;
              
              DECLARE
                 l_transaction_xmldoc XMLTYPE;
                 
                 E_XSD_VALIDATION_FAILURE EXCEPTION;
                 PRAGMA EXCEPTION_INIT(E_XSD_VALIDATION_FAILURE, -31061);
              BEGIN
                 l_transaction_xmldoc := XMLTYPE.createXML(
                                            '<?xml version="1.0" encoding="UTF-8"?>'||CHR(10)||
                                            '<TransactionBatch>'||CHR(10)||
                                            '  <Transaction>'||CHR(10)||
                                            '    <Portal>TEST2</Portal>'||CHR(10)||
                                            '    <CustomerId>1234567891</CustomerId>'||CHR(10)||
                                            '    <TransactionQuantity>4</TransactionQuantity>'||CHR(10)||
                                            '    <AppTransactionId>1232K89C</AppTransactionId>'||CHR(10)||
                                            '    <TransactionDetails>This is another long free text field that will ultimately be mapped to a CLOB column</TransactionDetails>'||CHR(10)||
                                            '    <Username>Jimbo2</Username>'||CHR(10)||
                                            '    <Test>This is an In-Place Schema Evolution Test</Test>'||CHR(10)||
                                            '  </Transaction>'||CHR(10)||
                                            '</TransactionBatch>'
                                         );
                 
                 -- Attempt to populate the TEST_BATCH Table.
                 INSERT INTO test_batch
                   ( batch_id,
                     batch_timestamp,
                     batch_xml
                   )
                 VALUES
                   ( SEQ_TEST_BATCH_ID.NEXTVAL,
                     SYSTIMESTAMP,
                     l_transaction_xmldoc
                   );
                 
                 DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' row inserted.');
                 COMMIT;
                 DBMS_OUTPUT.PUT_LINE('Commit complete.');
              
              EXCEPTION
                 WHEN E_XSD_VALIDATION_FAILURE
                 THEN
                    DBMS_OUTPUT.PUT_LINE('Insert failed as expected.'||CHR(10));
                    DBMS_OUTPUT.PUT_LINE(SQLERRM);
              END;
              /
              h4. The InPlace Evolution operation worked this time. :)

              NOTE: To begin with, I tried retrieving the registered schema from USER_XML_SCHEMAS into the l_old_xml_schema variable, but the evolution operation failed with the same ORA-31166 error. Using the original XSDs in the XDIFF - without the Oracle-added system annotations - seems to be the way forward.
              • 4. Re: InPlace Schema Evolution Error: ORA-31166: internal error code to etc...
                odie_63
                Hi James,
                I actually tried the XDBURITYPE method to retrieve the schemas I'd registered in the repository via DBMS_XMLSCHEMA.REGISTERSCHEMA, but the evolution failed with an error message informing me that I'd attempted an illegal schema evolution operation.
                Yes, using XDBUriType on the /sys/schemas folder is exactly like querying USER_XML_SCHEMAS directly, see explanation below.

                The conclusion I've come to is that the repository is intended for use if one wishes to store XML, XSL and XSD documents off-database, in a filesystem, and that the repository exists to point to these files. Would you say that's a reasonable summary?
                Actually no.

                The files in the XDB repository are really stored within the database. You can see them by querying RESOURCE_VIEW (based on the resource table XDB.XDB$RESOURCE).
                Now, file contents may not be directly stored in the resource table, but in other XMLType tables. In this case the resource table only holds a pointer to the real location of the data in the database.

                As mentioned above, an example of that is an XML schema resource that appears to be stored in the repository under /sys/schemas/<user>/my_schema.xsd but actually resides in a separate table, XDB.XDB$SCHEMA, on which are based USER_XML_SCHEMAS views :
                SELECT v.any_path
                     , XMLSerialize(document s.object_value) as xsd
                FROM xdb.xdb$schema s
                   , resource_view v
                WHERE equals_path(v.res, '/sys/schemas/DEV/home/xsd/test_binXML_v1.xsd') = 1
                  AND REF(s) = XMLCast(
                                 XMLQuery(
                                   'declare default element namespace "http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
                                    data(/Resource/XMLRef)'
                                    passing v.res returning content
                                 )
                                 as REF XMLType
                               )
                ;
                • 5. Re: InPlace Schema Evolution Error: ORA-31166: internal error code to etc...
                  jmcnaug2
                  Hi Odie,

                  Thanks for your response. It helps to clear up a little of the confusion for me. One thing remains though...

                  In the following tutorial example (also posted in my second post on this thread), a Schema URL of */home/OE/* is being used for their registered XML schemas:

                  http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/datamgmt/xmldb2_a/xmldb2_a.htm

                  Where I'm getting confused is, how did they manage to register their XML Schemas with home/OE/ as the base path for their SchemaURL? As already discussed, when the built-in DBMS_XMLSCHEMA.REGISTER_SCHEMA is used, the schema is created under the base path of */sys/schemas/* by default. There does not seem to be any way to change this behavior using this particular built-in procedure. How did the author of that tutorial manage to register their schema with a base url path of home/OE/ ? Is this the behaviour when a schema is registered using DBMS_XMLSCHEMA.REGISTERURI ?

                  Many thanks for your help to-date. I have made good progress because of it (pulled off a successful copy-evolve today, after the initial hiccup of learning that the type XDB$STRUBG_LIST_T in the 11gR2 Packages and Types reference is actually a mis-spelling of XDB$STRING_LIST_T, and that the parameter name "maptablename" is a mis-spelling of "maptabname"... ;) ).

                  James
                  • 6. Re: InPlace Schema Evolution Error: ORA-31166: internal error code to etc...
                    odie_63
                    In the following tutorial example (also posted in my second post on this thread), a Schema URL of */home/OE/* is being used for their registered XML schemas:
                    No, it's not the URL used for registration, */home/OE* is just where the original unregistered schemas are stored. The author then simply use XDBUriType to access them, compute the Xdiff etc.

                    Do not mistake the schema location URL for the XDB storage path :

                    - The URL (schema location) used for registration is http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd (as seen in §3). That's the URL we must use to qualify instance XML documents.

                    - The registered version is stored in the repository at */sys/schemas/<user>/localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd*.

                    Edited by: odie_63 on 27 août 2012 12:01
                    • 7. Re: InPlace Schema Evolution Error: ORA-31166: internal error code to etc...
                      jmcnaug2
                      Ah, I see. I think I need to make a little more time to experiment with XDB...

                      "+Do not mistake the schema location URL for the XDB storage path+"

                      That's where I was getting confused, so thanks for clearing this up.

                      Overall, thanks for all your help Odie. You have helped to fill in some knowledge gaps for me. Much appreciated.