4 Replies Latest reply: Oct 9, 2012 11:26 AM by 964614 RSS

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

    964614
      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
          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
            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
              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
                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 : )