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) {


} else {

return false;






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;




return false;





Example usage:

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

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

INTO l_clob
FROM apex_collection
WHERE collection_name = 'CLOB_CONTENT'

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



About these ads
Categories: Ajax, ApEx
  1. Uk
    May 17, 2014 at 2:23 pm

    Hello, I’m trying to use ‘setApexCollectionClob’ function to save ‘f28_textarea” details to clob collection. So that I can retrieve to save it back to database after users update. However I’m not able to do that. Appreciate your help. Thanks.

  2. Uday
    May 17, 2014 at 1:49 am

    I would like to save clob value from ‘P1_textarea’ back to table (clob column) and I’m using it to save ‘CLOB_CONTENT Apex Collection’. However, I’m not able to correctly get data using ‘setApexCollectionClob’. Appreciate your help. Thanks.

  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


Get every new post delivered to your Inbox.

%d bloggers like this: