7 Replies Latest reply on Aug 2, 2019 8:12 PM by cacodri

    Invalid number: ORA-31038

    cacodri

      Hello everyone,

       

      The following query gives error in the number 27953,42 which is how it arrives in the XML, if I change the comma to a point 27953.42 it is executed correctly, the question is how do I make the change of the decimal characters in the sql.

       

      WITH sii_dte_email_upload(RutEmisor, RutReceptor,correl, EnvioDTE_XML) AS  

      (SELECT 1,1001,850  

            , XMLTYPE(  

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

                <EnvioDTE xmlns="http://www.sii.cl/SiiDte" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.sii.cl/SiiDte EnvioDTE_v10.xsd"> 

                  <SetDTE ID="SetDoc"> 

                    <Caratula> 

                      <RutEmisor>79760110-1</RutEmisor> 

                      <RutEnvia>6424091-9</RutEnvia> 

                      <RutReceptor>97004000-5</RutReceptor> 

                      <FchResol>2014-08-20</FchResol> 

                      <NroResol>79</NroResol> 

                      <TmstFirmaEnv>2019-07-25T17:47:44.000000</TmstFirmaEnv> 

                      <SubTotDTE> 

                        <TpoDTE>34</TpoDTE> 

                        <NroDTE>1</NroDTE> 

                      </SubTotDTE> 

                      <SubTotDTE> 

                        <TpoDTE>33</TpoDTE> 

                        <NroDTE>2</NroDTE> 

                      </SubTotDTE> 

                    </Caratula> 

                    <DTE>

                      <Documento ID="F6766T34">

                        <Detalle>

                          <NroLinDet>1</NroLinDet>

                          <IndExe>1</IndExe>

                          <NmbItem>SERVICIO SOFTWARE ASISTENCIA JULIO 2019</NmbItem>

                          <QtyItem>23</QtyItem>

                          <UnmdItem>UF</UnmdItem>

                          <PrcItem>27953,42</PrcItem>

                          <MontoItem>642929</MontoItem>

                        </Detalle>

                      </Documento>

                    </DTE>

                  </SetDTE>  

                </EnvioDTE>')        

          FROM dual)  

      SELECT * 

        FROM xmltable(XMLNAMESPACES(DEFAULT 'http://www.sii.cl/SiiDte'), '/EnvioDTE/SetDTE/DTE/Documento/Detalle'  

                passing( select EnvioDTE_XML  

                           from SII_DTE_EMAIL_UPLOAD  

                          where correl = 850 )  

                columns NroLinDet   VARCHAR2(10)    path 'NroLinDet'  

                      , NmbItem     VARCHAR2(90)    path 'NmbItem'  

                      , QtyItem     VARCHAR2(22)    path 'QtyItem'  

                      , UnmdItem    VARCHAR2(22)    path 'UnmdItem'  

                      , PrcItem     number    path 'PrcItem'  

                      , MontoItem   number    path 'MontoItem'  

                      );

       

      thank you very much

       

      Carlos

        • 1. Re: Invalid number: ORA-31038
          mNem

          SELECT to_number(PrcItem, '999999999999D99999', 'nls_numeric_characters='',.''') prcItem0, x.*

            FROM xmltable(XMLNAMESPACES(DEFAULT 'http://www.sii.cl/SiiDte'), '/EnvioDTE/SetDTE/DTE/Documento/Detalle'  

                    passing( select EnvioDTE_XML  

                               from SII_DTE_EMAIL_UPLOAD  

                              where correl = 850 )  

                    columns NroLinDet   VARCHAR2(10)    path 'NroLinDet'  

                          , NmbItem     VARCHAR2(90)    path 'NmbItem'  

                          , QtyItem     VARCHAR2(22)    path 'QtyItem'  

                          , UnmdItem    VARCHAR2(22)    path 'UnmdItem'  

                          , PrcItem     VARCHAR2(22)    path 'PrcItem'  

                          , MontoItem   number    path 'MontoItem'  

                          ) x;

          • 2. Re: Invalid number: ORA-31038
            cacodri

            Thank you,

             

             

            the result is the same, it seems to me that the error is generated during the analysis of the XML, not during the sql select, in fact if I delete the PrcItem column, it also delivers the same error.

             

            WITH sii_dte_email_upload(RutEmisor, RutReceptor,correl, EnvioDTE_XML) AS  

            (SELECT 1,1001,850  

                  , XMLTYPE(  

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

                      <EnvioDTE xmlns="http://www.sii.cl/SiiDte" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.sii.cl/SiiDte EnvioDTE_v10.xsd"> 

                        <SetDTE ID="SetDoc"> 

                          <Caratula> 

                            <RutEmisor>79760110-1</RutEmisor> 

                            <RutEnvia>6424091-9</RutEnvia> 

                            <RutReceptor>97004000-5</RutReceptor> 

                            <FchResol>2014-08-20</FchResol> 

                            <NroResol>79</NroResol> 

                            <TmstFirmaEnv>2019-07-25T17:47:44.000000</TmstFirmaEnv> 

                            <SubTotDTE> 

                              <TpoDTE>34</TpoDTE> 

                              <NroDTE>1</NroDTE> 

                            </SubTotDTE> 

                            <SubTotDTE> 

                              <TpoDTE>33</TpoDTE> 

                              <NroDTE>2</NroDTE> 

                            </SubTotDTE> 

                          </Caratula> 

                          <DTE>

                            <Documento ID="F6766T34">

                              <Detalle>

                                <NroLinDet>1</NroLinDet>

                                <IndExe>1</IndExe>

                                <NmbItem>SERVICIO SOFTWARE ASISTENCIA JULIO 2019</NmbItem>

                                <QtyItem>23</QtyItem>

                                <UnmdItem>UF</UnmdItem>

                                <PrcItem>27953,42</PrcItem>

                                <MontoItem>642929</MontoItem>

                              </Detalle>

                            </Documento>

                          </DTE>

                        </SetDTE>  

                      </EnvioDTE>')        

                FROM dual)  

            SELECT x.*

              FROM xmltable(XMLNAMESPACES(DEFAULT 'http://www.sii.cl/SiiDte'), '/EnvioDTE/SetDTE/DTE/Documento/Detalle'  

                      passing( select EnvioDTE_XML  

                                 from SII_DTE_EMAIL_UPLOAD  

                                where correl = 850 )  

                      columns NroLinDet   VARCHAR2(10)    path 'NroLinDet'  

                            , NmbItem     VARCHAR2(90)    path 'NmbItem'  

                            , QtyItem     VARCHAR2(22)    path 'QtyItem'  

                            , UnmdItem    VARCHAR2(22)    path 'UnmdItem'  

            --                , PrcItem     VARCHAR2(22)    path 'PrcItem'  

                            , MontoItem   number    path 'MontoItem'  

                            ) x;

             

            Informe de error -

            ORA-31038: Valor number no válido: "27953,42"

            • 3. Re: Invalid number: ORA-31038
              mNem

              works fine.

               

              SQL Fiddle  (updated)

              • 4. Re: Invalid number: ORA-31038
                cacodri

                Now I do not understand, when I run it in sqldeveloper it gives error

                • 5. Re: Invalid number: ORA-31038
                  cacodri

                  I'm in Oracle 12.1.0.2.0

                  • 6. Re: Invalid number: ORA-31038
                    mNem

                    Just a thought ... I am not familiar with xsd validation. what if you comment out omit the xsd and give it a go.

                     

                    SQL Fiddle

                    • 7. Re: Invalid number: ORA-31038
                      cacodri

                      Thank you very much, sorry but after resetting my PC, it works perfectly in sql developer.

                       

                       

                      thanks for your time.