7 Replies Latest reply: Oct 1, 2012 9:28 AM by 964614 RSS

    Problem XML generation in APEX  ORA-06502

    964614
      Hi, I have a problem with XML generation, I developed an application in APEX, and in a html page I have this process:+

      declare
      l_XML varchar2(32767);
      begin
      .......
      select xmlElement
      (
      "iva",
      xmlElement("numeroRuc",J.RUC),
      xmlElement("razonSocial", J.RAZON_SOCIAL),
      xmlElement("idRepre", J.ID_REPRE),
      xmlElement("rucContador", J.RUC_CONTADOR),
      xmlElement("anio", J.ANIO),
      xmlElement("mes", J.MES),

      xmlElement
      (
      "compras",
      (
      select xmlAgg
      (
      xmlElement
      (
      "detalleCompra",
      --xmlAttributes(K.ID_COMPRA as "COMPRA"),
      xmlForest
      (
      K.COD_SUSTENTO as "codSustento",
      K.TPLD_PROV as "tpldProv",
      K.ID_PROV as "idProv",
      K.TIPO_COMPROBANTE as "tipoComprobante",
      to_char(K.FECHA_REGISTRO, 'DD/MM/YYYY') as "fechaRegistro",
      K.ESTABLECIMIENTO as "establecimiento",
      K.PUNTO_EMISION as "puntoEmision",
      K.SECUENCIAL as "secuencial",
      to_char(K.FECHA_EMISION, 'DD/MM/YYYY') as "fechaEmision",
      K.AUTORIZACION as "autorizacion",
      to_char(K.BASE_NO_GRA_IVA, 9999999999.99) as "baseNoGraIva",
      to_char(K.BASE_IMPONIBLE, 9999999999.99) as "baseImponible",
      to_char(K.BASE_IMP_GRAV, 9999999999.99) as "baseImpGrav",
      to_char(K.MONTO_ICE, 9999999999.99) as "montoIce",
      to_char(K.MONTO_IVA, 9999999999.99) as "montoIva",
      to_char(K.VALOR_RET_BIENES, 9999999999.99) as "valorRetBienes",
      to_char(K.VALOR_RET_SERVICIOS, 9999999999.99) as "valorRetServicios",
      to_char(K.VALOR_RET_SERV_100, 9999999999.99) as "valorRetServ100"
      ),
      xmlElement
      (
      "air",
      (
      select xmlAgg
      (
      xmlElement
      (
      "detalleAir",
      xmlForest
      (
      P.COD_RET_AIR as "codRetAir",
      to_char(P.BASE_IMP_AIR, 9999999999.99) as "baseImpAir",
      to_char(P.PORCENTAJE_AIR, 999.99) as "porcentajeAir",
      to_char(P.VAL_RET_AIR, 9999999999.99) as "valRetAir"
      )
      )
      )
      from ANEXO_COMPRAS P
      where P.ID_COMPRA = K.ID_COMPRA
      AND P.ID_INFORMANTE_XML = K.ID_INFORMANTE_XML
      )
      ),
      xmlElement("estabRetencion1", K.ESTAB_RETENCION_1),
      xmlElement("ptoEmiRetencion1", K.PTO_EMI_RETENCION_1),
      xmlElement("secRetencion1", K.SEC_RETENCION_1),
      xmlElement("autRetencion1", K.AUT_RETENCION_1),
      xmlElement("fechaEmiRet1", to_char(K.FECHA_EMI_RET_1,'DD/MM/YYYY')),
      xmlElement("docModificado", K.DOC_MODIFICADO),
      xmlElement("estabModificado", K.ESTAB_MODIFICADO),
      xmlElement("ptoEmiModificado", K.PTO_EMI_MODIFICADO),
      xmlElement("secModificado", K.SEC_MODIFICADO),
      xmlElement("autModificado", K.AUT_MODIFICADO)
      )
      )
      from SRI_COMPRAS K
      WHERE K.ID IS NOT NULL
      AND K.ID_INFORMANTE_XML = J.ID_INFORMANTE
      AND K.ID BETWEEN 1 AND 25
      )
      )
      ).getClobVal()
      into l_XML
      from ANEXO_INFORMANTE J
      where J.ID_INFORMANTE =:P3_MES
      and J.RUC =:P3_ID_RUC
      and J.ANIO =:P3_ANIO
      and J.MES =:P3_MES;

      --HTML
      sys.owa_util.mime_header('text/xml',FALSE);
      sys.htp.p('Content-Length: ' || length(l_XML));
      sys.owa_util.http_header_close;
      sys.htp.print(l_XML);
      end;



      Now my table has more than 900 rows and only when I specifically selected 25 rows of the table "ANEXO_COMPRAS" in the where ( AND K.ID BETWEEN 1 AND 25) the XML is generated.

      I think that the problem may be the data type declared "varchar2", but I was trying with the data type "CLOB" and the error is the same.+

      declare
      l_XML CLOB;
      begin
      --Oculta XML
      sys.htp.init;
      wwv_flow.g_page_text_generated := true;
      wwv_flow.g_unrecoverable_error := true;

      --select XML
      select xmlElement
      ( .......

      )
      from SRI_COMPRAS K
      WHERE K.ID IS NOT NULL
      AND K.ID_INFORMANTE_XML = J.ID_INFORMANTE
      .........
      ).getClobVal()
      into l_XML
      from ANEXO_INFORMANTE J
      where J.ID_INFORMANTE =:P3_MES
      and J.RUC =:P3_ID_RUC
      and J.ANIO =:P3_ANIO
      and J.MES =:P3_MES;

      --HTML
      sys.owa_util.mime_header('text/xml',FALSE);
      sys.htp.p('Content-Length: ' || length(l_XML));
      sys.owa_util.http_header_close;
      sys.htp.print(l_XML);
      end;

      The error generated is ORA-06502: PL/SQL: numeric or value error+_

      Please I need your help. I don`t know how to resolve this problem, how to use the data type "CLOB" for the XML can be generate+

      Edited by: JohannaCevallos07 on 26-sep-2012 10:52
        • 1. Re: Problem XML generation in APEX  ORA-06502
          odie_63
          Hi,
          I think that the problem may be the data type declared "varchar2", but I was trying with the data type "CLOB" and the error is the same.
          Yes, more precisely the problem is that HTP.PRINT only supports VARCHAR2, so you're limited in passing a maximum of 32k per call.

          The solution is to print the CLOB in chunks of 32k, using a loop.
          I also suggest you use HTP.PRN instead, so that no newline are printed in the middle of the data.
          • 2. Re: Problem XML generation in APEX  ORA-06502
            964614
            Hi,
            Thank you for your answer... : )

            Excuse me please, but I don`t know how I can do this.
            Please, can you help me? I am new developing in this tool...
            • 3. Re: Problem XML generation in APEX  ORA-06502
              odie_63
              In the DECLARE section :
              l_xml       clob;
              chunk_size  pls_integer := 32767;
              then replace
              htp.print(l_XML);
              with the loop :
              for i in 0..trunc((dbms_lob.getlength(l_xml) - 1) / chunk_size)
              loop
                htp.prn( dbms_lob.substr(l_xml, chunk_size, 1 + i*chunk_size) );
              end loop;
              • 4. Re: Problem XML generation in APEX  ORA-06502
                964614
                Hi Odie,
                Thank you very much for your help. I can solve my problem with this XML.

                Have a nice day.
                Regards.
                Johanna
                • 5. Re: Problem XML generation in APEX  ORA-06502
                  964614
                  Hi,
                  Actually I am testing the application and the process for generate xml file and the problem initially reported persists. I can´t generate the XML file for more than 26 rows of the table. : (
                  • 6. Re: Problem XML generation in APEX  ORA-06502
                    odie_63
                    Any new error messages?

                    Please show how you've implemented the solution.
                    • 7. Re: Problem XML generation in APEX  ORA-06502
                      964614
                      Hi Odie

                      Thanks for your help.
                      Well, I`m trying to generate the xml file and when I run the application considering all existing records in the table the error is:


                      This page contains the following errors:+
                      error on line 1 at column 25005: Opening and ending tag mismatch: ptoEmiRetencion1 line 0 and ptoEmiRcion1+
                      Below is a rendering of the page up to the first error.+


                      But the interesting thing is when I consider only 19 records of the table, the xml file is generated without problem.

                      The solution implemented is:

                      declare
                      l_clob_XML CLOB;
                      xml_size pls_integer := 32767;

                      begin
                      sys.htp.init;
                      wwv_flow.g_page_text_generated := true;
                      wwv_flow.g_unrecoverable_error := true;

                      dbms_lob.CREATETEMPORARY(lob_loc => l_clob_XML, cache => false, dur => dbms_lob.session);

                      select xmlElement
                      (
                      "iva",
                      xmlElement("numeroRuc",J.RUC),
                      ...

                      xmlElement
                      (
                      "compras",
                      (
                      select xmlAgg
                      (
                      xmlElement
                      (
                      "detalleCompra",
                      xmlForest
                      (
                      K.COD_SUSTENTO as "codSustento",
                      K.TPLD_PROV as "tpldProv",
                      K.ID_PROV as "idProv",
                      ....
                      ),
                      ...
                      from SRI_COMPRAS K
                      .....
                      )
                      )
                      ).getClobVal()
                      into l_clob_XML
                      from ANEXO_INFORMANTE J
                      ....

                      sys.owa_util.mime_header('text/xml',FALSE);
                      sys.htp.p('Cache-Control: no-cache');
                      sys.htp.p('Pragma: no-cache');
                      sys.owa_util.http_header_close;


                      for i in 0.. trunc((dbms_lob.getlength(l_clob_XML) - 1) / xml_size)
                      loop
                      htp.prn( dbms_lob.substr(l_clob_XML, xml_size, 1 + i*xml_size) );
                      end loop;

                      --Termina el LOB temporal
                      DBMS_LOB.FREETEMPORARY(lob_loc => l_clob_XML);

                      end;


                      I don`t understand where is the wrong : (

                      Hopefully you can help.


                      Have a nice day

                      Edited by: Jojace07 on Oct 1, 2012 7:26 AM