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.clob (

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



Categories: Ajax, ApEx
  1. SN
    March 11, 2019 at 4:32 pm

    I am able to display the CLOB content(with >32K length) into Text Area with this method. but the HTML content is not being interpreted by Text Area. (For example this CLOB column has Audio data).
    If it was a direct Classic Report selected from database column then there is an option to set “Escape special characters” to “No” which automatically displays the audio control .

    Can you please help me achieve this via the java script to eliminate 32K limit.

  2. John
    February 27, 2017 at 5:02 pm

    Thanks for this post. I’m getting TypeError: pString.substr is not a function when trying to pass a JSON object. Can we not pass a JSON object directly? If I convert my object to a string with this JSON.stringify(response) then it works fine. Just curious…Thanks for the post; very very helpful!

  3. Drugelis
    September 8, 2016 at 1:04 pm

    correction on line 6:
    FROM apex_collectionS

    instead of:
    FROM apex_collection

  4. July 22, 2016 at 9:59 am

    Thanks Andy, that came in useful.

  5. Mirela
    March 3, 2016 at 10:43 am

    Excelent post. This works for me.
    And thank you Eog for your comment. I hab problems getting this to work when there was an autorisation (in my case on page level). By debugging p.responseText and my autorisation function, I found out that this requires an autorisation for page 0, component name 0. I am using APEX 5.0.
    Thanks guys.

  6. September 1, 2015 at 5:32 pm

    Excellent post. Thanks, Andy.

  7. eoghainanderson
    August 11, 2015 at 12:38 pm

    Very nice bit of code Andy! Worked a treat!

    Should the line

    var apexAjaxObj = new apex.ajax.obj (


    var apexAjaxObj = new apex.ajax.clob (

    Also recommend passing out p.responseText in the callback() of the setter function.


  8. 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.

  9. 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 )

Connecting to %s

%d bloggers like this: