Home > Ajax, ApEx > Setting and retrieving CLOB values in Apex

Setting and retrieving CLOB values in Apex

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;

About these ads
Categories: Ajax, ApEx
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: