Saving and retrieving CLOB data greater than 32K in Oracle Apex

Question

My aim is to retrieve CLOB data from the database into a textarea in an Oracle Apex application and then to be able to save it into the database from the textarea itself on pressing a 'Save' button. I also have some other fields on this page (as textfields) which are non CLOB fields and they need to be saved in the database as well on clicking the button.

For this, I'm using the following code under "HTML Header and Body Attribute" of the page. This is used to retrieve/save CLOB into the textarea/database. Note that a simple PLSQL code inside the Apex item will suffice to do what I'm doing here but only if CLOB data is less than 32k bytes. I am using this function due to the 32k limit in plsql in apex (and 4k limit when sql is used).

function clob_set(){  
        var clob_ob = new apex.ajax.clob(  
            function(){  
                var rs = p.readyState  
                if(rs == 1||rs == 2||rs == 3){  
                    $x_Show('AjaxLoading');  
                }else if(rs == 4){  
                    $s('P5075_RESPONSETEXT',p.responseText);  
                    $x_Hide('AjaxLoading');  
                }else{return false;}  
            }  
        );  

        if(!$v_IsEmpty('P5075_STYLESHEET')){clob_ob._set($v('P5075_STYLESHEET'))};  
    }  

    function clob_get(){  
        var clob_ob = new apex.ajax.clob(  
            function(){  
                var rs = p.readyState  
                if(rs == 1||rs == 2||rs == 3){  
                    $x_Show('AjaxLoading');  
                }else if(rs == 4){  
                    $s('P5075_STYLESHEET',p.responseText);  
                    $x_Hide('AjaxLoading');  
                }else{return false;}  
            }  
        );  
        clob_ob._get();  
    }

I am calling one of the functions under "Page HTML Body Attribute" as onload = "javascript:clob_get();"

I have a PLSQL after header process for this.

declare
l_clob clob:= empty_clob();

begin

if apex_collection.collection_exists(p_collection_name=>'CLOB_CONTENT') then
apex_collection.delete_collection(p_collection_name=>'CLOB_CONTENT');
end if;

apex_collection.create_or_truncate_collection(p_collection_name=>'CLOB_CONTENT');
dbms_lob.createtemporary( l_clob, false, dbms_lob.SESSION );

SELECT xslt
INTO l_clob
FROM schematransform
WHERE namn = 'f';

apex_collection.add_member(p_collection_name => 'CLOB_CONTENT',p_clob001 => l_clob);
end;

This is working just fine. Now, I have a plsql process which saves the details entered in the CLOB and non-CLOB fields into the database. But as soon as the page submits, I get a "HTTP Bad Request".

Can anyone please explain why is this happening and how can I solve this?

Answer 1

this is an old thread, but CLOB handling changed in new Apex, so i think this will useful for someone.

I use Apex 21.2 and the CLOB upload much simpler.

Item - Rich Text Editor (CKEDITOR5) Submit button -> Submit

Javascript:

• Catch the Submit event
• Slice CLOB smaller parts
• Insert smaller parts into the Form
• Disable the original CLOB field

    apex.gPageContext$.on( "apexpagesubmit", function() {
        var $ = apex.jQuery,
        form$ = $( "#wwvFlowForm" ),
        f1 = apex.server.chunk( $v( "P1_CLOB_COLUMN" ) );
    
        if ( !Array.isArray( f1 ) ) {
            f1 = [f1];
        }
        
        f1.forEach( function( v ) {
            form$.append( "" );
        });
        
        $('#P1_CLOB_COLUMN').prop("disabled", true);
        $s( "P1_CLOB_COLUMN", "" );
    }); 

Then CLOB parts submitted in the normal way, no need Ajax Callback and COLLECTIONS. Only need a Process that combine parts (after row processing), and update the CLOB field. Because after the Row Processing, you have P1_ID_COLUMN too (if insert).

SetCLOB plsql:

    DECLARE
        l_clob CLOB;
    BEGIN
        l_clob := empty_clob();
        DBMS_LOB.CREATETEMPORARY(l_clob,true);
    
        for i in 1..apex_application.g_f01.count 
        LOOP   
            l_clob := l_clob || apex_application.g_f01(i) ;
        END LOOP;
    
        Update CLOB_TABLE set 
        P1_CLOB_COLUMN= l_clob
        Where ID = :P1_ID_COLUMN;
    END;

Getting the CLOB to the RichText item - Javascript: Copied getting code from: https://www.stinolez.com/2020/07/03/clob-in-apex-rich-text-editor/ Execute it when page loads.

    function getData() {
        apex.server.process("GetCLOB", {
            pageItems:['P1_ID_COLUMN']
          }, {
            dataType: 'html'
          }).done(function(rData) {
               $s('P1_CLOB_COLUMN',rData);
        });}

And the GetCLOB Ajax callback PL/SQL block:

    DECLARE
        l_clob        CLOB;
        l_file        BLOB;
        l_dest_offset PLS_INTEGER := 1;
        l_src_offset  PLS_INTEGER := 1;
        l_lang_ctx    PLS_INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
        l_csid        PLS_INTEGER := NLS_CHARSET_ID('UTF8');
        l_blob_warn   PLS_INTEGER;
    BEGIN
        BEGIN
            -- Get the CLOB
            SELECT NVL(P1_CLOB_COLUMN, EMPTY_CLOB())
            INTO l_clob
            FROM CLOB_TABLE
            WHERE ID = :P1_ID_COLUMN;
          -- Exception handling when no data were found for the ID (you are creating new row
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                l_clob := EMPTY_CLOB();
          
        END;
    
        -- If we don't have a CLOB, just return empty content
        IF (DBMS_LOB.GETLENGTH(l_clob) = 0) THEN
            HTP.P('');
        ELSE
            -- Create BLOB from CLOB
    
            DBMS_LOB.CREATETEMPORARY(  lob_loc => l_file
                                 , cache => true
                                 , dur => DBMS_LOB.CALL);
            DBMS_LOB.CONVERTTOBLOB(l_file, l_clob, dbms_lob.lobmaxsize, l_dest_offset, l_src_offset, l_csid, l_lang_ctx, l_blob_warn);
            -- Download BLOB
            OWA_UTIL.MIME_HEADER('text/html', false);
            HTP.P('Content-Length: ' || dbms_lob.getlength(l_file));
            HTP.P('Content-Disposition: attachment; filename="content.html"');
            OWA_UTIL.HTTP_HEADER_CLOSE();
            WPG_DOCLOAD.DOWNLOAD_FILE(l_file);
        END IF;
    END;

Regards, Józsi

Answer 2

This is the code for apex.ajax.clob, taken from apex_4_1.js:

/**
 * @namespace = apex.ajax
 */
apex.ajax = {
    /*clob*/
    clob : function (pReturn){
        var that = this;
        this.ajax = new htmldb_Get(null,$x('pFlowId').value,'APXWGT',0);
        this.ajax.addParam('p_widget_name','apex_utility');
        this.ajax.addParam('x04','CLOB_CONTENT');
        this._get = _get;
        this._set = _set;
        this._return = !!pReturn?pReturn:_return;
        return;
        function _get(pValue){
            that.ajax.addParam('x05','GET');
            that.ajax.GetAsync(that._return);
        }
        function _set(pValue){
            that.ajax.addParam('x05','SET');
            that.ajax.AddArrayClob(pValue,1);
            that.ajax.GetAsync(that._return);
        }
        function _return(){
        if(p.readyState == 1){
            }else if(p.readyState == 2){
            }else if(p.readyState == 3){
            }else if(p.readyState == 4){
              return p;
            }else{return false;}
        }
    },

So, clob setting and getting is truly asynchronuous. The code you posted provides a handling function that is called when the request is finished (done in htmldb_get). I think it's an ugly workaround but ok. We need to manipulate this function code for our submit to work. Since the set is async, we can't be sure that the page will not be submitted before the set has occured. To prevent this, amend your clob_set code as such:

function clob_set(pSubmit){
   var clob_ob = new apex.ajax.clob(
      function(){
         var rs = p.readyState
         if(rs == 1||rs == 2||rs == 3){
            $x_Show('AjaxLoading');
         }else if(rs == 4){
             //here the clob has actually been saved, and
             // the ajax call finished
            $s('P5075_RESPONSETEXT',p.responseText);
            $x_Hide('AjaxLoading');

            //pSubmit is a new param
            //use it to check if set has been called for
            //a page submit or not
            if(pSubmit){
               //disable the clob field: it should not be
               //substituted to the session state!!
               $('#P5075_STYLESHEET').prop("disabled", true);
               //actually submit the page. This will submit
               //all fields to session except the disabled ones
               apex.submit('SUBMIT');
            };
         }else{
            return false;
         };
      });

   if(!$v_IsEmpty('P5075_STYLESHEET')){
      clob_ob._set($v('P5075_STYLESHEET'));
   };
};

Alter your submit button, and have it's action be defined by a dynamic action. You need to do this to prevent the substitution of your clob-fields to the session through the default process. Create a dynamic action which executes javascript, call clob_set with pSubmit set:

clob_set(true);

Do have a look at the apex.submit api description. Also understand how a button works: it submits the page and will set the request to the name of that button (or another request value if explicitly defined).

For example, a button could be named 'APPLY_CHANGES' and have a label 'Change'. This is important if you use for example the built-in row processing. The request value will determine which SQL-action will be invoked, and you can view the possible values in the details of the process, next to the checkboxes for insert/update/delete.

Here, a most helpful beautiful flowchart: