1 Reply Latest reply on Mar 3, 2013 10:42 PM by TobiP

    Submit CLOB-data

      Hi folks,

      on one of our applications we have a text area for the user to enter a set of numbers (record identifier) on which a report is generated. Now recently my client entered about 14.000 numbers, copied from an excel sheet - and certainly got an error message.

      So I did a bit of research on how to submit large text data. One detailed example from A.N.T demonstrates how to handel a text editor processing, others work by splitting the input into an array, but all those examples I found are a bit old. So I wondered if APEX 4.2 (our version) provides other, more straight forward means.

      I have done some own really simple testing using the apex_application.g_x01 items, 10 of them as parameters to an On-Demand-Process, which should add up to a total of 320k bytes to submit which would suit our needs (in the procedure all those parameters are added up into a clob variable), but the problem seems to be, that even thou one item can store the varchar2 max of 32k, as a total you still can't pass more than about 61k in the call. If more I get a network error.

      Can you give me any suggestions on how to submit those kind of data? Else I go with the example linked above.

      Thanks very much in advance,
        • 1. Re: Submit CLOB-data
          I have build a little testcase now, thanks to the apex.ajax.clob - retrieving data from a clob field, adjusted it to my needs and simplyfied it a bit, because I am only using a simple text area, no text editor.

          I am creating 200.000 6-digit random numbers with spaces in between in a text area with javascript, adding up to a total of 1.380.708 Bytes (more than I need ;) ).
          In the ajax-submit-function those values are split into chunks of 32.767 Bytes, and added as the parameter for the apex 'f01' item.

          (For all who are after this after me, here is a summary.)

          <b>The javascript:</b>
          function sendCLOB(){
              var ajaxObj = new htmldb_Get( null, 
                                        'APPLICATION_PROCESS=sendCLOB',      // plsql process name
                                        210                     // page
              // split Input:
              var pos = 0;
              while (pos < $v('P210_TAREA').length){
                  ajaxObj.addParam( 'f01', $v('P210_TAREA' ).substr( pos, pos+32767 ) );
                  pos += 32767;
              ajaxObj.GetAsync( pCallback )
              ajaxObj = null;
          function pCallback( data ){
              if ( data.readyState == 1 ) {
              } else if ( data.readyState == 4 ) {
                    // update Interactive Report:
              $('#P210_RESPONSE').html('readyState=' +data.readyState + ' ; Response: ' + data.response);
          The PL/SQL process, basicaly adopted 1:1, for an ON DEMAND PROCESS:
               l_code clob := empty_clob;
               dbms_lob.createtemporary( l_code, false, dbms_lob.SESSION );
               for i in 1..wwv_flow.g_f01.count loop
               end loop;
               apex_collection.create_or_truncate_collection(p_collection_name => 'CLOB_LOAD');
               apex_collection.add_member(p_collection_name => 'CLOB_LOAD',p_clob001 => l_code);
               -- just for testing purposes a simple insert:   
               insert into tmpCLOB (CLOBCOL, INSERTDATE) 
                      values((select clob001 from apex_collections where collection_name = 'CLOB_LOAD'),
               htmldb_application.g_unrecoverable_error := TRUE;
                  -- echo for callback function:
               htp.p('Successfully inserted CLOB.'); -- or what ever...
              exception when others then
          Any comments highly welcome, because I am still far away from being pro.

          Best regards,


          One thing surprises me now thou: I can transmit and save 2,071,705 numeric chars, also (certainly) alphanumeric. Now I tried to insert a long poetry text (don't ask me why) from this site, and there when the length exeeds 32k I get an "Internal Server Error".

          I thought of having some coding problems first, so I copied the text into notpad and saved it utf-8 encoded, but the problem remains.

          Can anyone explain to me why this happenes?

          Edited by: TobiP on 03.03.2013 14:41