Displaying Large CLOBS in Oracle APEX

I had a problem retrieving a clob from the database and displaying it in an apex text area.  The reason for this is it is a very large clob and Apex areas have a 32k character (byte) limit. This is because PL/SQL treats oracle apex page items as varchars not clobs and varchars have a maximum size. Anything over that size will not be displayed properly.

So a PL/SQL process such as

select clob_field
into :P20_CLOB_ITEM
from table
where id = :P20_CLOB_ID

won't work for very large clobs. It won't throw an error it just won't display

To get round this problem you have to use apex.ajax.clob. This uses collections to process large clobs and write them to the text area. Firstly you need to select the CLOB into the collection using the following procedure. Run this on the same page that has the id value for the row that you are getting the clob from

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 clob_field
into l_clob
from table
where id = :P19_CLOB_ID;

apex_collection.add_member(p_collection_name => 'CLOB_CONTENT',p_clob001 => l_clob);
end;

Then on the page with the text area put the following javascript code in the HTML Header of the page

<script type="text/javascript">
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('P20_CLOB_ITEM',p.responseText);
                    $x_Hide('AjaxLoading');
                }else{return false;}
            }
        );
        clob_ob._get();
    }
</script>

p.responseText retrieves the clob value from the collection CLOB_CONTENT

then call the javascript. in my case I place this in the html body of the page

onload = "javascript:clob_get();"

Hope this helps as it caused me quite a few problems.

for more on how to use this check out a blog post that was very helpful to me in solving this problem
http://www.advnettech.net/blog/?p=7