4 Replies Latest reply: Feb 18, 2011 2:39 AM by odie_63 RSS

    ORA-19201: Datatype not supported when validating XML against an XSD

    534868
      Hello.

      Info: using Oracle 10.2.0.1.0, Java components are installed.

      I've created an XML view using ora:view, which is validated against a registered XSD. This view is created successfully.

      But when I SELECT * from this view, it gives ORA-19201 - Datatype not supported.

      Can't understand why. I've matched the XML to the XSD, and the datatypes all look fine to me. I've referred to the XSD using the XMLFORMAT.CREATEFORMAT option. When I use the same option to specify "NO_SCHEMA", it returns the correct XML, but we need to validate it against the XSD.

      I'm thinking one of two things has happened here:

      1. I've made a mistake with either my registration, or how I've used XMLFORMAT to refer to the XSD.

      2. There's a bug in Oracle.

      My money's on 1. Can anyone help?

      Thanks,
      Ray.
        • 1. Re: ORA-19201: Datatype not supported when validating XML against an XSD
          157315
          Ray,

          It'd help if you can post your code along with the errors you are getting.

          Regards,
          Geoff
          • 2. Re: ORA-19201: Datatype not supported when validating XML against an XSD
            534868
            Hello Geoff, I didn't want to send any code until I knew someone was willing to look at it (there's a lot of it).

            Here is the XSD:
            <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
            <!--W3C Schema generated by XMLSpy v2006 rel. 3 sp1 (http://www.altova.com)-->
            <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
                 <xs:element name="TransInfo">
                      <xs:complexType>
                           <xs:sequence>
                                <xs:element name="PIE_ERROR" type="PIE_ERRORType" minOccurs="0"/>
                                <xs:element name="Transaction" type="TransactionType" maxOccurs="unbounded"/>
                           </xs:sequence>
                      </xs:complexType>
                 </xs:element>
                 <xs:complexType name="PIE_ERRORType">
                      <xs:sequence>
                           <xs:element ref="ERROR_CODE"/>
                           <xs:element ref="DESCRIPTION"/>
                      </xs:sequence>
                 </xs:complexType>
                 <xs:element name="ERROR_CODE">
                      <xs:simpleType>
                           <xs:restriction base="xs:string">
                                <xs:enumeration value="0001"/>
                           </xs:restriction>
                      </xs:simpleType>
                 </xs:element>
                 <xs:element name="DESCRIPTION">
                      <xs:simpleType>
                           <xs:restriction base="xs:string">
                                <xs:enumeration value="LEAD NUMBER 12345 IS INVALID"/>
                           </xs:restriction>
                      </xs:simpleType>
                 </xs:element>
                 <xs:complexType name="TransactionType">
                      <xs:sequence>
                           <xs:element ref="TRANSACTION_NUMBER"/>
                           <xs:element ref="TRANSACTION_STATUS"/>
            <xs:element ref="CONTACT_ID"/>
                           <xs:element ref="INVOICE_NUMBER"/>
                           <xs:element ref="CHANNEL"/>
                           <xs:element ref="CSA"/>
                           <xs:element ref="OPEN_DATETIME"/>
                           <xs:element ref="CLOSED_DATETIME"/>
                           <xs:element name="Orderline" type="OrderlineType" maxOccurs="unbounded"/>
                      </xs:sequence>
                 </xs:complexType>
                 <xs:element name="TRANSACTION_NUMBER">
                      <xs:simpleType>
                           <xs:restriction base="xs:int">
                                <xs:enumeration value="3455229"/>
                                <xs:enumeration value="3455230"/>
                           </xs:restriction>
                      </xs:simpleType>
                 </xs:element>
                 <xs:element name="TRANSACTION_STATUS">
                      <xs:simpleType>
                           <xs:restriction base="xs:string">
                                <xs:enumeration value="C"/>
                           </xs:restriction>
                      </xs:simpleType>
                 </xs:element>
                 <xs:element name="CONTACT_ID">
                      <xs:simpleType>
                           <xs:restriction base="xs:int">
                                <xs:enumeration value="111"/>
                                <xs:enumeration value="222"/>
                           </xs:restriction>
                      </xs:simpleType>
                 </xs:element>
                 <xs:element name="INVOICE_NUMBER">
                      <xs:simpleType>
                           <xs:restriction base="xs:string">
                                <xs:enumeration value="123456789"/>
                                <xs:enumeration value="987654321"/>
                           </xs:restriction>
                      </xs:simpleType>
                 </xs:element>
                 <xs:element name="CHANNEL">
                      <xs:simpleType>
                           <xs:restriction base="xs:string">
                                <xs:enumeration value="Test 1"/>
                                <xs:enumeration value="Test 2"/>
                           </xs:restriction>
                      </xs:simpleType>
                 </xs:element>
                 <xs:element name="CSA">
                      <xs:simpleType>
                           <xs:restriction base="xs:string">
                                <xs:enumeration value="SEARLEA"/>
                                <xs:enumeration value="SILVAJ"/>
                           </xs:restriction>
                      </xs:simpleType>
                 </xs:element>
                 <xs:element name="OPEN_DATETIME">
                      <xs:simpleType>
                           <xs:restriction base="xs:dateTime">
                                <xs:enumeration value="2001-10-26T20:32:52"/>
                                <xs:enumeration value="2004-12-06T10:23:25"/>
                           </xs:restriction>
                      </xs:simpleType>
                 </xs:element>
                 <xs:element name="CLOSED_DATETIME">
                      <xs:simpleType>
                           <xs:restriction base="xs:dateTime">
                                <xs:enumeration value="2001-10-26T21:32:52"/>
                                <xs:enumeration value="2004-12-06T10:23:25"/>
                           </xs:restriction>
                      </xs:simpleType>
                 </xs:element>
                 <xs:complexType name="OrderlineType">
                      <xs:sequence>
                           <xs:element ref="ORDER_QTY"/>
                           <xs:element ref="RETAIL_AMT"/>
                           <xs:element ref="SALE_AMT"/>
                           <xs:element ref="PRODUCT_DESCR"/>
                           <xs:element ref="PART"/>
                           <xs:element ref="IMEI"/>
                           <xs:element name="BankDetails" type="BankDetailsType"/>
                      </xs:sequence>
                 </xs:complexType>
                 <xs:element name="ORDER_QTY">
                      <xs:simpleType>
                           <xs:restriction base="xs:int">
                                <xs:enumeration value="1"/>
                                <xs:enumeration value="3"/>
                           </xs:restriction>
                      </xs:simpleType>
                 </xs:element>
                 <xs:element name="RETAIL_AMT">
                      <xs:simpleType>
                           <xs:restriction base="xs:decimal">
                                <xs:enumeration value="45.67"/>
                                <xs:enumeration value="54.76"/>
                           </xs:restriction>
                      </xs:simpleType>
                 </xs:element>
                 <xs:element name="SALE_AMT">
                      <xs:simpleType>
                           <xs:restriction base="xs:string">
                                <xs:enumeration value="45.67"/>
                                <xs:enumeration value="54.76"/>
                           </xs:restriction>
                      </xs:simpleType>
                 </xs:element>
                 <xs:element name="PRODUCT_DESCR">
                      <xs:simpleType>
                           <xs:restriction base="xs:string">
                                <xs:enumeration value="Product test 2"/>
                                <xs:enumeration value="Vodafone Nokia 6230 Pack"/>
                           </xs:restriction>
                      </xs:simpleType>
                 </xs:element>
                 <xs:element name="PART">
                      <xs:simpleType>
                           <xs:restriction base="xs:string">
                                <xs:enumeration value="6806"/>
                                <xs:enumeration value="8606"/>
                           </xs:restriction>
                      </xs:simpleType>
                 </xs:element>
                 <xs:element name="IMEI">
                      <xs:simpleType>
                           <xs:restriction base="xs:string">
                                <xs:enumeration value="099000000000001"/>
                                <xs:enumeration value="09900078900001"/>
                           </xs:restriction>
                      </xs:simpleType>
                 </xs:element>
                 <xs:complexType name="BankDetailsType">
                      <xs:sequence>
                           <xs:element ref="BANK_NAME"/>
                           <xs:element ref="ACCOUNT_NAME"/>
                           <xs:element ref="ACCOUNT_NUMBER"/>
                           <xs:element ref="SORT_CODE"/>
                      </xs:sequence>
                 </xs:complexType>
                 <xs:element name="BANK_NAME">
                      <xs:simpleType>
                           <xs:restriction base="xs:string">
                                <xs:enumeration value="HSBC"/>
                                <xs:enumeration value="LLOYDS"/>
                           </xs:restriction>
                      </xs:simpleType>
                 </xs:element>
                 <xs:element name="ACCOUNT_NAME">
                      <xs:simpleType>
                           <xs:restriction base="xs:string">
                                <xs:enumeration value="MR A SEARLE"/>
                                <xs:enumeration value="MR J SILVA"/>
                           </xs:restriction>
                      </xs:simpleType>
                 </xs:element>
                 <xs:element name="ACCOUNT_NUMBER">
                      <xs:simpleType>
                           <xs:restriction base="xs:string">
                                <xs:enumeration value="87698789"/>
                                <xs:enumeration value="89769082"/>
                           </xs:restriction>
                      </xs:simpleType>
                 </xs:element>
                 <xs:element name="SORT_CODE">
                      <xs:simpleType>
                           <xs:restriction base="xs:string">
                                <xs:enumeration value="100928"/>
                                <xs:enumeration value="123346"/>
                           </xs:restriction>
                      </xs:simpleType>
                 </xs:element>
            </xs:schema>

            I then registered this XSD using DBMS_XMLSCHEMA.REGISTER, calling it test_1.xsd. It is visble in USER_XML_SCHEMAS.

            Here are the 15 views used to drive the XML:

            SQL> desc v_pie_branch
            Name Null? Type
            ----------------------------------------- -------- ----------------------------
            BRANCH NOT NULL NUMBER(4)

            SQL> desc v_pie_branch_feature
            Name Null? Type
            ----------------------------------------- -------- ----------------------------
            BRANCH_FEATURE NOT NULL NUMBER(10)
            BRANCH NUMBER(4)
            FEATURE_TYPE VARCHAR2(10)
            CHANNEL VARCHAR2(40)

            SQL> desc v_pie_transaction
            Name Null? Type
            ----------------------------------------- -------- ----------------------------
            TRANSACTION_NUMBER NOT NULL NUMBER(10)
            BRANCH NUMBER(4)
            EMPLOYEE_TRANS VARCHAR2(12)
            CONTACT NUMBER(8)
            OPEN_DAT DATE
            OPEN_TIM DATE
            TRANSACTION_STATUS VARCHAR2(1)
            TRANS_TYPE VARCHAR2(1)
            CLOSED_DAT DATE
            CLOSED_TIM DATE
            INVOICE_NUMBER NUMBER(10)
            INVOICE_DAT DATE
            CSA VARCHAR2(12)
            CREATED_ON DATE
            AMENDED_BY VARCHAR2(12)
            AMENDED_ON DATE
            OPEN_DATETIME VARCHAR2(19)
            CLOSED_DATETIME VARCHAR2(19)

            SQL> desc v_pie_bank_detail
            Name Null? Type
            ----------------------------------------- -------- ----------------------------
            BANK_DETAIL NOT NULL NUMBER(12)
            BANKCODE VARCHAR2(40)
            SORTCODE VARCHAR2(40)
            BANK_NAME VARCHAR2(60)

            SQL> desc v_pie_account_detail
            Name Null? Type
            ----------------------------------------- -------- ----------------------------
            ACCOUNT_DETAIL NOT NULL NUMBER(12)
            BANK_DETAIL NUMBER(12)
            ACCOUNT_NUMBER VARCHAR2(40)
            SORT_CODE VARCHAR2(40)
            ACCOUNT_NAME VARCHAR2(40)
            BANKCODE VARCHAR2(40)

            SQL> desc v_pie_account_link
            Name Null? Type
            ----------------------------------------- -------- ----------------------------
            ACCOUNT_LINK NOT NULL NUMBER(12)
            CONTACT NUMBER(12)
            ACCOUNT_DETAIL NUMBER(12)
            ACC_LINK_TYP NUMBER(12)
            BNK_SINCE DATE
            AC_DET VARCHAR2(40)

            SQL> desc v_pie_compesn
            Name Null? Type
            ----------------------------------------- -------- ----------------------------
            COMPESN NOT NULL NUMBER(10)
            CONTACT NUMBER(8)
            IMEI VARCHAR2(25)

            SQL> desc v_pie_contact
            Name Null? Type
            ----------------------------------------- -------- ----------------------------
            CONTACT_ID NOT NULL NUMBER(8)
            CONTACT_TYP VARCHAR2(1)
            COMPANY NUMBER(10)
            BRANCH NUMBER(4)
            DEAD_FLG VARCHAR2(1)
            FORENAME VARCHAR2(20)
            TITLE VARCHAR2(5)
            MID_INITIAL VARCHAR2(1)
            GENDER VARCHAR2(1)
            FRAUD_FLG VARCHAR2(1)

            SQL> desc v_pie_servcon
            Name Null? Type
            ----------------------------------------- -------- ----------------------------
            SERVCON NOT NULL NUMBER(10)
            CONTRACT NUMBER(10)
            CONNECTION_CURR NUMBER(10)
            CONTACT NUMBER(8)

            SQL> desc v_pie_tpplan_compline
            Name Null? Type
            ----------------------------------------- -------- ----------------------------
            TPPLAN_COMPLINE NOT NULL NUMBER(10)
            SERVCON NUMBER(10)
            PPLAN NUMBER(10)

            SQL> desc v_pie_tpplan_comp_detail
            Name Null? Type
            ----------------------------------------- -------- ----------------------------
            TPPLAN_COMPLINE NOT NULL NUMBER(10)
            RULE_DETAIL_NO NOT NULL VARCHAR2(5)
            RULE_DETAIL_LINE NOT NULL NUMBER(2)
            U_VERSION VARCHAR2(1)
            OPTION VARCHAR2(40)
            OPT_STRING VARCHAR2(40)

            SQL> desc v_pie_tpplan
            Name Null? Type
            ----------------------------------------- -------- ----------------------------
            SERVCON NOT NULL NUMBER(10)
            PPLAN NOT NULL NUMBER(10)
            U_VERSION VARCHAR2(1)

            SQL> desc v_pie_connection
            Name Null? Type
            ----------------------------------------- -------- ----------------------------
            CONNECTION NOT NULL NUMBER(10)
            CONTACT NUMBER(8)
            ADDRESSNO NUMBER(5)
            TRANSACTION NUMBER(10)
            TPRODLINE NUMBER(10)
            CONTRACT NUMBER(10)
            SERVCON NUMBER(10)

            SQL> desc v_pie_tprodline
            Name Null? Type
            ----------------------------------------- -------- ----------------------------
            TRANSACTION NOT NULL NUMBER(10)
            TPRODLINE NOT NULL NUMBER(10)
            CONTACT NUMBER(8)
            PRODUCT NUMBER(10)
            ORDER_QTY NUMBER(12)
            RETAIL_AMT NUMBER
            SALE_AMT NUMBER

            SQL> desc v_pie_product
            Name Null? Type
            ----------------------------------------- -------- ----------------------------
            PRODUCT NOT NULL NUMBER(10)
            PART VARCHAR2(20)
            PRODUCT_DESCR VARCHAR2(40)


            This is the code to create an XML view, validating against the schema:

            create OR REPLACE VIEW v_xml_validate AS
            SELECT SYS_XMLAGG(XMLQuery('
            <PIE_ERROR>
            <ERROR_CODE>"0001"</ERROR_CODE>
            <DESCRIPTION>"There was an error with this customer"</DESCRIPTION>
            </PIE_ERROR>,
            <Transaction>
            {for $t in ora:view("V_PIE_TRANSACTION")/ROW
                ,$ct in ora:view("V_PIE_CONTACT")/ROW
                ,$b in ora:view("V_PIE_BRANCH")/ROW
                ,$bf in ora:view("V_PIE_BRANCH_FEATURE")/ROW
            where $t/CONTACT = $ct/CONTACT_ID
            and   $t/BRANCH = $b/BRANCH
            and   $b/BRANCH = $bf/BRANCH
            return ($t/TRANSACTION_NUMBER
                    ,$t/TRANSACTION_STATUS
                    ,$ct/CONTACT_ID
                    ,$t/INVOICE_NUMBER
                    ,$bf/CHANNEL
                    ,$t/CSA
                    ,$t/OPEN_DATETIME
                    ,$t/CLOSED_DATETIME
                    ,<Orderline>
                          {for $tpl in ora:view("V_PIE_TPRODLINE")/ROW
                              ,$pr  in ora:view("V_PIE_PRODUCT")/ROW
                              ,$cmpesn  in ora:view("V_PIE_COMPESN")/ROW
                           where $tpl/TRANSACTION eq $t/TRANSACTION_NUMBER
                           and   $tpl/PRODUCT eq $pr/PRODUCT
                           and   $ct/CONTACT_ID eq $cmpesn/CONTACT
                           return ($tpl/ORDER_QTY
                                  ,$tpl/RETAIL_AMT
                                  ,$tpl/SALE_AMT
                                  ,$pr/PRODUCT_DESCR
                                  ,$pr/PART
                                  ,$cmpesn/IMEI
                                  ,<BankDetails>
                                       {for $conn in ora:view("V_PIE_CONNECTION")/ROW
                                        for $scon in ora:view("V_PIE_SERVCON")/ROW
                                        for $tpp in ora:view("V_PIE_TPPLAN")/ROW
                                        for $tppc in ora:view("V_PIE_TPPLAN_COMPLINE")/ROW
                                        for $tppcd in ora:view("V_PIE_TPPLAN_COMP_DETAIL")/ROW
                                        for $al in ora:view("V_PIE_ACCOUNT_LINK")/ROW
                                        for $ad in ora:view("V_PIE_ACCOUNT_DETAIL")/ROW
                                        for $bd in ora:view("V_PIE_BANK_DETAIL")/ROW
                                        where $conn/CONTACT eq $ct/CONTACT_ID
                                        and   $scon/CONTACT eq $conn/CONTACT
                                        and   $tpp/SERVCON eq $scon/SERVCON
                                        and   $tppc/SERVCON eq $tpp/SERVCON
                                        and   $tppc/PPLAN eq $tpp/PPLAN
                                        and   $tppcd/TPPLAN_COMPLINE eq $tppc/TPPLAN_COMPLINE
                                        and   $al/AC_DET eq $tppcd/OPT_STRING
                                        and   $ad/ACCOUNT_DETAIL eq $al/ACCOUNT_DETAIL
                                        and   $bd/BANK_DETAIL eq $ad/BANK_DETAIL
                                        return ($bd/BANK_NAME
                                               ,$ad/ACCOUNT_NAME
                                               ,$ad/ACCOUNT_NUMBER
                                               ,$ad/SORT_CODE
                                               )
                                       }
            </BankDetails>
            )
            }
            </Orderline>
            )
            }
            </Transaction>'
            RETURNING CONTENT),
            XMLFORMAT.CREATEFORMAT('TransInfo','USE_GIVEN_SCHEMA','test_1.xsd', 'http://xmlns.cpw.co.uk/CPW/EPOS/Order/RetrieveTransaction/RetrieveTransaction', NULL, NULL)) AS xml_string FROM DUAL;

            The view is created with no errors. Selecting * from it, however, gives:

            ORA-19201 - Datatype not supported

            Marrying the XML to the XSD shows nothing obviously wrong.

            Thanks,
            Ray.
            • 3. Re: ORA-19201: Datatype not supported when validating XML against an XSD
              Wishmaster
              The problem is actual for me too :( I have exactly the same problem: XML was validated in Altova XMLSpy with my XSD schema. But when i try to use this XSD in XMLFormat function I'm always getting such error - ORA-19201 :( Any ideas?
              • 4. Re: ORA-19201: Datatype not supported when validating XML against an XSD
                odie_63
                Hi,

                Please start a new thread, and give the following info :

                - database version, all four digits from "SELECT * FROM v$version"
                - XML schema
                - XML sample document
                - the query/code you're using that gives the error

                Use
                 tags to post code snippets (explained here : http://forums.oracle.com/forums/help.jspa).
                It'll preserve formatting and improve readability.
                
                Thanks.