Skip navigation.

Andrew Tulley

Syndicate content Andy's Blog: Application Express etc.
SELECT * FROM RANDOM.stuff WHERE subject IN ('Application Express','Oracle','PL/SQL','SQL') AND is_worth_blogging_about IN ('YES','MAYBE','DOUBTFUL');
Updated: 1 hour 20 min ago

Setting and retrieving CLOB values in Apex

Fri, 2014-02-07 16:17

Imagine you have a textarea in your app where you want your users to be able to type in large quantities of text, click a button to store this in the database and then later retrieve this large quantity of text and redisplay it in that textarea.

You can’t do this as you would with other page items (i.e. just submit the page and have the values stored in session state). Any value over 32kb in size won’t work. To get round this there is a technique which involves using an Apex Collection called CLOB_CONTENT. Essentially, you just need the two Javascript functions below to get this working. They are both asynchronous and can be passed a Javascript callback function to be executed once the CLOB value has been set or retrieved.

The Javascript function to save large value to the CLOB_CONTENT Apex Collection


function setApexCollectionClob (pBigValue, callback) {

var apexAjaxObj = new apex.ajax.obj (

function() {

var rs = p.readyState;

if (rs == 4) {

callback();

} else {

return false;

};

}

);

apexAjaxObj._set(pBigValue);

}

Example usage:

setApexCollectionClob ('Some large text value...', function(){alert('Data saved to Apex Collection!')})

The Javascript function to retrieve a large value from the CLOB_CONTENT Apex Collection


function getApexCollectionClob(callback) {

var apexAjaxObj = new apex.ajax.clob (

function() {

var rs = p.readyState;

if(rs==4){

callback(p.responseText);

}else{

return false;

}

}

);

apexAjaxObj._get();
}

Example usage:

getApexCollectionClob (function(pReturnedClobValue){ $('#P1_TEXTAREA').val(pReturnedClobValue) })

Retrieving the set value via PL/SQL in, for example, an Application Process

DECLARE
L_CLOB CLOB
BEGIN
SELECT CLOB001
INTO l_clob
FROM apex_collection
WHERE collection_name = 'CLOB_CONTENT'

INSERT INTO my_table (id, myclob) values (123,l_clob);

HTP.p('SUCCESS');

END;


Passing more than 10 values with apex.server.process

Fri, 2014-02-07 15:53

You may be familiar with the apex.server.process function exposed by Apex’s Javascript API. It allows you to asynchronously interact with Apex Application Processes.

A simple example would be.

Apex Application Process


HTP.p('You passed "'||APEX_APPLICATION.g_x01 ||'" as the value for x01. ');

HTP.p('You passed "'||APEX_APPLICATION.g_x02 ||'" as the value for x02. ');

HTP.p('You passed "'||APEX_APPLICATION.g_x03 ||'" as the value for x03. ');

Javascript

apex.server.process ( 
  "MY_APP_PROCESS"
,   {   x01: 'my first custom value'
    ,   x02: 'mysecond custom value'
    ,   x03: 'my third custom value'
    }
 , { dataType: 'text'
 ,success: function(pData){alert(pData)}
}
);

If you were to create the Application Process “MY_APP_PROCESS” and run the Javascript above, you’d see an alert popup:

——–

You passed “my first custom value” as the value for x01.
You passed “mysecond custom value” as the value for x02.
You passed “my third custom value” as the value for x03.

———

You can use use x01 through to x10 to pass up to 10 parameters to your application process. What about if you want to pass more than 10 parameters, though? To do this, you first need to create a number of Application Items. You might like to call them :G_11, :G_12, :G_13 etc..

You can then set the values of these items in session state (and hence make them available in your Application Process) by doing the following:

apex.server.process ( 
  "MY_APP_PROCESS"
,   {   x01: 'my first custom value'
    ,   x02: 'mysecond custom value'
    ,   x03: 'my third custom value'
    ,   p_arg_names: ['G_11','G_12','G_13']
    ,   p_arg_values: ['My 11th custom value','My 12th custom value','My 13th custom value']
    }
 , {    dataType: 'text'
    ,   success: function(pData){alert(pData)}
    }
);

Referencing these values inside your Application Process is simply a case of using Bind Variable syntax, e.g.:


HTP.p('You passed "'||APEX_APPLICATION.g_x01 ||'" as the value for x01. ');

HTP.p('You passed "'||APEX_APPLICATION.g_x02 ||'" as the value for x02. ');

HTP.p('You passed "'||APEX_APPLICATION.g_x03 ||'" as the value for x03. ');

HTP.p('You passed "'||:G_11 ||'" as the value for G_11. ');