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