2 Replies Latest reply: Jun 4, 2014 1:09 AM by soutou RSS

    when decimal points become a ","

    soutou

      Hi,

       

      When decimal point become ",", how validate a document inserting in a xml schema based xmltype table,

      without having the error ORA-31038: Valeur number non valide : "*****"

       

      In vol_reel2.xml, a decimal is required, see prix element

       

      <?xml version="1.0" encoding="UTF-8"?>

      <vol_reel nvol="AF6143" datev="2014-06-29"

      xsi:noNamespaceSchemaLocation="http://www.ms.eta.lu/sante/vol_reel.xsd"

      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

          <passagers>

              <pax num_carte="1344986034">

                  <nom>Lacombe</nom>

                  <prenom>Serge</prenom>

                  <siege>01B</siege>

                  <prix>95.50</prix>

              </pax>

              <pax num_carte="1690352877">

                  <nom>Diffis</nom>

                  <prenom>Gerard</prenom>

                  <siege>01A</siege>

                  <prix>89</prix>

              </pax>

          </passagers>

      </vol_reel>

       

      XML schema associated : vol_reel.xsd

       

      <?xml version="1.0" encoding="utf-8"?>

      <xsd:schema version="1.0"

      xmlns:xsd="http://www.w3.org/2001/XMLSchema">

          <xsd:element name="vol_reel" type="vol_reelType"/>

          <xsd:complexType name="vol_reelType">

              <xsd:sequence>

                  <xsd:element name="passagers" type="passagersType"/>

              </xsd:sequence>

              <xsd:attribute name="nvol" type="xsd:string"/>

              <xsd:attribute name="datev" type="xsd:date"/>

          </xsd:complexType>

          <xsd:complexType name="passagersType">

              <xsd:sequence>

                  <xsd:element maxOccurs="500" name="pax" type="paxType"/>

              </xsd:sequence>

          </xsd:complexType>

          <xsd:complexType name="paxType">

              <xsd:sequence>

                  <xsd:element name="nom" type="xsd:string"/>

                  <xsd:element name="prenom" type="xsd:string"/>

                  <xsd:element name="siege" type="xsd:string"/>

                  <xsd:element name="prix" type="xsd:decimal"/>

              </xsd:sequence>

              <xsd:attribute name="num_carte" type="xsd:int"/>

          </xsd:complexType>

      </xsd:schema>

       

      ----registering

       

      SQL> BEGIN

        2       DBMS_XMLSCHEMA.DELETESCHEMA

        5  ('http://www.ms.eta.lu/sante/vol_reel.xsd',DBMS_XMLSCHEMA.DELETE_CASCADE_FORCE);

        6        DBMS_XMLSCHEMA.REGISTERSCHEMA(

        7          SCHEMAURL => 'http://www.ms.eta.lu/sante/vol_reel.xsd',

        8          SCHEMADOC => BFILENAME('REPXML','vol_reel.xsd'),

        9          LOCAL     => TRUE,

      10          GENTYPES  => FALSE,

      11          GENTABLES => FALSE,

      12         owner     => 'OXM',

      13         options   => DBMS_XMLSCHEMA.REGISTER_BINARYXML,

      14         CSID => NLS_CHARSET_ID('AL32UTF8'));

      15  END;

      16  /

       

      ProcÚdure PL/SQL terminÚe avec succÞs.

       

      --binary XML table

       

      SQL> CREATE TABLE pax_binxml_schema OF XMLType

        2    XMLTYPE STORE AS BINARY XML

        3    XMLSCHEMA "http://www.ms.eta.lu/sante/vol_reel.xsd"

        4    ELEMENT "vol_reel";

       

      Table crÚÚe.

       

      The document is valid :

      SQL> DECLARE

        2   var_xml    XMLTYPE;

        3   resultat   NUMBER;

        4   url_schema VARCHAR2(50) := 'http://www.ms.eta.lu/sante/vol_reel.xsd';

        5  BEGIN

        6   var_xml :=

        7    XMLType(xmlData    => BFILENAME('REPXML','vol_reel2.xml'),

        8            csid       => NLS_CHARSET_ID('AL32UTF8'));

        9   resultat  :=

      10   var_xml.isSchemaValid(url_schema);

      11   IF resultat = 0 THEN

      12     DBMS_OUTPUT.PUT_LINE('document non valide');

      13   ELSE

      14     DBMS_OUTPUT.PUT_LINE('document valide');

      15   END IF;

      16  END;

      17  /

      document valide

       

      ProcÚdure PL/SQL terminÚe avec succÞs.

       

      -- inserting :

       

      SQL> INSERT INTO pax_binxml_schema

        2  VALUES (XMLTYPE(xmlData => BFILENAME('REPXML','vol_reel2.xml'),

        3                 csid    => NLS_CHARSET_ID('AL32UTF8')));

       

      1 ligne crÚÚe.

       

      -- decimal becomes a ","

       

      SQL> SELECT ROWNUM, OBJECT_VALUE FROM pax_binxml_schema;

       

          ROWNUM

      ----------

      OBJECT_VALUE

      ----------------------------------------------------------------------------------

               1

      <?xml version="1.0" encoding="WINDOWS-1252"?>

      <vol_reel nvol="AF6143" datev="2014-06-29" xsi:noNamespaceSchemaLocation="http://w

      a.lu/sante/vol_reel.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

        <passagers>

          <pax num_carte="1344986034">

            <nom>Lacombe</nom>

            <prenom>Serge</prenom>

            <siege>01B</siege>

            <prix>95,5</prix>

          </pax>

          <pax num_carte="1690352877">

            <nom>Diffis</nom>

            <prenom>Gerard</prenom>

            <siege>01A</siege>

            <prix>89</prix>

          </pax>

        </passagers>

      </vol_reel>

       

      -- the same document isn't valide

       

      SQL> DECLARE

        2   var_xml  XMLTYPE;

        3   url_schema VARCHAR2(50) := 'http://www.ms.eta.lu/sante/vol_reel.xsd';

        4  BEGIN

        5    SELECT OBJECT_VALUE INTO var_xml FROM pax_binxml_schema WHERE ROWNUM = 1;

        6   IF (var_xml.isSchemaValid(url_schema,'vol_reel') = 0) THEN

        7     dbms_output.put_line('document non valide');

        8   ELSE

        9     dbms_output.put_line('document valide');

      10   END IF;

      11  END;

      12  /

      DECLARE

      *

      ERREUR Ó la ligne 1 :

      ORA-31038: Valeur number non valide : "95,5"

      ORA-06512: Ó "SYS.XMLTYPE", ligne 369

      ORA-06512: Ó ligne 6

        • 1. Re: when decimal points become a ","
          odie_63

          There are inconsistencies between the different validation context depending on how the schema-based XMLType instance has been constructed.

           

          • The first method succeeds because the XMLType instance is built directly from the file.
          • The second method (where the instance is fetched from the table) fails because at this point, the XMLType instance is encoded using binary XML types specific to the schema, in particular, the xs:decimal type mapping behaves like the NUMBER datatype and is NLS-sensitive.

           

          Running an ALTER SESSION before calling the method solves the problem :

          alter session set nls_numeric_characters = ".,"

           

          The problem doesn't occur when the table is not schema-based.

           

          Anyway, inserting into a schema-based binary XMLType column always performs a full validation, so it's not necessary to do another one afterwards.

          • 2. Re: when decimal points become a ","
            soutou

            OK Many thanks