This discussion is archived
4 Replies Latest reply: Feb 18, 2011 12:39 AM by odie_63 RSS

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

534868 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    840769 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.