This discussion is archived
4 Replies Latest reply: Oct 9, 2012 9:26 AM by 964614 RSS

Problem printing a Clob variable in chunks of 32k using a loop

964614 Newbie
Currently Being Moderated
Hi,
I have been trying to generate and return an extensive XML file in APEX 4.1 with oracle 11g Express Edition (more than 900 rows, and for each row should sees 34 columns of information), but I don`t know what happen with the loop that I`m using because only when I use a filter for consulting 19 rows the xml file is generated, but if I need to generated the xml for all rows (more than 900) the xml is blank, and didn`t display error.

Please I need your help.
Thanks.

Attached the code source processed:

declare
l_clob_XML CLOB;
xml_size pls_integer:= 32000;
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("razonSocial", J.RAZON_SOCIAL),
...
xmlElement (
"compras", (
select xmlAgg (
xmlElement (
"detalleCompra",
xmlForest (
K.COD_SUSTENTO as "codSustento",
K.TPLD_PROV as "tpldProv",
....
)
)
)
)
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;

--Recorre intervalos de 32k
for i in 0.. CEIL((dbms_lob.getlength(l_clob_XML) - 1) / xml_size)
loop
sys.htp.prn( dbms_lob.substr(l_clob_XML, xml_size, 1 + i*xml_size) );
end loop;

DBMS_LOB.FREETEMPORARY(lob_loc => l_clob_XML);
end;
  • 1. Re: Problem printing a Clob variable in chunks of 32k using a loop
    odie_63 Guru
    Currently Being Moderated
    Hi Johanna,

    Posting the same question over and over again won't help anyone, you least of all.

    If people are not responding, that's because either they don't know the solution, or the problem lacks useful details and steps to reproduce.

    So, how can we reproduce the issue in APEX?
    Could you describe what type of region you're writing the XML into?

    Thanks.
  • 2. Re: Problem printing a Clob variable in chunks of 32k using a loop
    964614 Newbie
    Currently Being Moderated
    Hi Odie

    I understand. Excuse me, please. But I had a problem with my connection and I haven`t been able to view previous post or removed them.

    This xml file is generated in an application developed on Apex 4.1., that contains:
    DML Form contains:
    3 items (Anio, Mes, Ruc): are filters key for the xml generation.
    1 Tab Set: it calls a HTML page that generate the XML file.

    Static HTML page:
    The PL/SQL code (source) is in a process point on load-after header.

    On whether the application is not difficult to develop. I`m using a 2 tables (master - detail):
    Table master: ANEXO_INFORMANTE: with 7 columns
    Column Name,"Data Type","Nullable","Default","Primary Key"
    ID_INFORMANTE,"VARCHAR2(6)","No","","1"
    RUC,"VARCHAR2(13)","No","",""
    RAZON_SOCIAL,"VARCHAR2(60)","No","",""
    ID_REPRE,"VARCHAR2(10)","Yes","",""
    RUC_CONTADOR,"VARCHAR2(13)","Yes","",""
    ANIO,"VARCHAR2(4)","No","",""
    MES,"VARCHAR2(2)","No","",""


    SRI_COMPRAS: with 39 columns that contains >950 rows that must be processed in xml file.
    Column Name,"Data Type","Nullable","Default","Primary Key"
    ID,"VARCHAR2(20)","No","","1"
    COD_SUSTENTO,"VARCHAR2(2)","No","",""
    TLD_PROV,"VARCHAR2(2)","No","",""
    ID_PROV,"VARCHAR2(13)","No","",""
    TIPO_COMPROBANTE,"VARCHAR2(2)","No","",""
    FECHA_REGISTRO,"DATE","No","",""
    ESTABLECIMIENTO,"VARCHAR2(3)","No","",""
    PUNTO_EMISION,"VARCHAR2(3)","No","",""
    SECUENCIAL,"VARCHAR2(9)","No","",""
    FCHA_EMISION,"DATE","No","",""
    AUTORIZACION,"VARCHAR2(37)","No","",""
    BASE_NO_GRA_IVA,"NUMBER(12,2)","No","",""
    BASE_IMPONIBLE,"NUMBER(12,2)","No","",""
    BASE_IMP_GRAV,"NUMBER(12,2)","No","",""
    MONTO_ICE,"NUMBER(12,2)","No","",""
    MONTO_IVA,"NUMBER(12,2)","No","",""
    VALOR_RET_BIENES,"NUMBER(12,2)","No","",""
    VALOR_RET_SERVICIOS,"NUMBER(12,2)","No","",""
    VALOR_RET_SERV_100,"NUMBER(12,2)","No","",""
    COD_RET_AIR,"VARCHAR2(5)","Yes","",""
    BASE_IMP_AIR,"NUMBER(12,2)","Yes","",""
    PORCENTAJE_AIR,"NUMBER(5,2)","Yes","",""
    VAL_RET_AIR,"NUMBER(12,2)","Yes","",""
    COD_RET_AIR2,"VARCHAR2(5)","Yes","",""
    BASE_IMP_AIR2,"NUMBER(12,2)","Yes","",""
    PORCENTAJE_AIR2,"NUMBER(5,2)","Yes","",""
    VAL_RET_AIR2,"NUMBER(12,2)","Yes","",""
    ESTAB_RETENCION_1,"VARCHAR2(3)","Yes","",""
    PTO_EMI_RETENCION_1,"VARCHAR2(3)","Yes","",""
    SEC_RETENCION_1,"VARCHAR2(9)","Yes","",""
    AUT_RETENCION_1,"VARCHAR2(37)","Yes","",""
    FECHA_EMI_RET_1,"DATE","Yes","",""
    DOC_MODIFICADO,"VARCHAR2(2)","Yes","",""
    ESTAB_MODIFICADO,"VARCHAR2(3)","Yes","",""
    PTO_EMI_MODIFICADO,"VARCHAR2(3)","Yes","",""
    SEC_MODIFICADO,"VARCHAR2(9)","Yes","",""
    AUT_MODIFICADO,"VARCHAR2(37)","Yes","",""
    ID_COMPRA,"VARCHAR2(30)","No","",""
    ID_INFORMANTE_XML,"VARCHAR2(6)","No","",""


    Thank you.
    Have a nice day.
  • 3. Re: Problem printing a Clob variable in chunks of 32k using a loop
    odie_63 Guru
    Currently Being Moderated
    OK, I've reproduced the issue.

    The problem is that DBMS_LOB.SUBSTR doesn't necessarily return the amount specified as input.
    That's a documented behaviour made to account for non fixed-width character sets.

    The solution is to check the length of the extracted substring to calculate the next offset.

    Here's a sample code successfully tested with a query on ALL_OBJECTS, in order to get a sufficiently large output XML doc :
    declare
      
      v_clob        clob;
      v_buffer      varchar2(32767);
      v_length      number;
      v_amount      number := 32767;
      v_offset      number := 1;
    
    begin
    
      sys.htp.init;
      wwv_flow.g_page_text_generated := true;
      wwv_flow.g_unrecoverable_error := true;
    
      dbms_lob.createtemporary(lob_loc => v_clob, cache => false, dur => dbms_lob.session);
    
      select xmlserialize(document
               xmlelement("rowset",
                 xmlagg(
                   xmlelement("row",
                     xmlforest(owner, object_name, subobject_name, object_id, 
                               data_object_id, object_type, created, last_ddl_time, 
                               timestamp, status, temporary, generated, 
                               secondary, namespace, edition_name
                               )
                   )
                 )
               ) as clob
             )
      into v_clob
      from all_objects;
      
      v_length := dbms_lob.getlength(v_clob);
    
      sys.owa_util.mime_header('text/xml', false);
      sys.htp.p('Content-Length: ' || v_length);
      sys.owa_util.http_header_close;
    
      -- read and write in chunk of 32k
      while v_offset <= v_length loop          
        dbms_lob.read(v_clob, v_amount, v_offset, v_buffer);
        htp.prn(v_buffer);
        v_offset := v_offset + v_amount;
      end loop;
    
      dbms_lob.freetemporary(lob_loc => v_clob);
      
    end;
  • 4. Re: Problem printing a Clob variable in chunks of 32k using a loop
    964614 Newbie
    Currently Being Moderated
    Hi Odie

    Thank you so much for your help, I`m very grateful with you.

    Truly I didn`t imagine that DBMS_LOB.SUBSTR doesn't necessarily return the amount specified as input. But knew that my loop was crazy and something was wrong.

    I tested your sample code yesterday coupling it to my needs, and in fact it is working and generating properly the XML doc. Now I am working for adding an header (general information for all rows) and adding more tables to this xml doc, something like this:

    SELECT
    XMLSerialize (CONTENT
    xmlElement ("iva",
    xmlElement("numeroRuc",I.RUC),
    xmlElement("razonSocial", I.RAZON_SOCIAL),
    xmlElement("idRepre", I.ID_REPRE),

    xmlElement
    ("compras",
    (
    xmlAgg (
    xmlElement ("detalleCompras",
    xmlAttributes( I.ID AS "Codigo"),
    xmlForest (
    I.COD_SUSTENTO as "codSustento",
    I.TPLD_PROV as "tpldProv",
    I.ID_PROV as "idProv"
    )
    )
    )
    )
    )
    )as Clob
    )
    into l_clob_XML
    from XML_ANEXO_SRI I

    end;

    Have a nice day : )

Legend

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