This discussion is archived
7 Replies Latest reply: Oct 1, 2012 7:28 AM by 964614 RSS

Problem XML generation in APEX  ORA-06502

964614 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Any new error messages?

    Please show how you've implemented the solution.
  • 7. Re: Problem XML generation in APEX  ORA-06502
    964614 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points