Tuesday 11 June 2013

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

19 comments:

  1. Thank you so much this was very helpful!!

    ReplyDelete
  2. Some things to note (that may or may not be obvious):
    - The SQL needs to be changed to reference the appropriate CLOB field and table as required.
    - Source type that should be used on the Apex Page Item is PL/SQL Function body
    - In the java script header code - P20_CLOB_ITEM should be replaced by the page item name of the text area field to be populated

    ReplyDelete
  3. Thank you for your comment. Yes I used generic item and variable names that you will have to change for your specific circumstances. From now on I will make that more explicit.

    ReplyDelete
  4. Works Like charm - Thank you Mayo and Yanizm for the GR8 comment

    ReplyDelete
  5. Hi Mayo, This does work displaying 1 clob item on a page but what if multiple clob items need to be displayed on a page? (ie..P20_CLOB_ITEM and P20_CLOB_ITEM2) I am not seeing the obvious solution if it is present.

    Thank you.

    ReplyDelete
    Replies
    1. Hi Matt. I'm not sure how to solve that as I have never had that requirement. If I find a solution I will post it. If you find a solution please link to it in the comments as I'm sure someone else will have the same problem

      Delete
  6. You legend Mayo, your blog helped me again! Hope you are keeping well, pop by sometime!

    ReplyDelete
  7. Error occurred while painting error page: ORA-01403: no data found

    hi when i use it get that error ?
    anyone know why?

    ReplyDelete
  8. Any solutions dealing with more than one clob item on a page? The solution above only seems to work for one clob item only.

    ReplyDelete
  9. What about Blobs ? How can we do the workaround for blobs ?

    ReplyDelete
  10. How to insert data into clob from APEX (Oracle Apex5) ? Can we use same approach?

    ReplyDelete
  11. Hi,

    I have the same issue to display CLOB field(with audio control) whihc is about 84K big. I am new to Apex, So could you please let me know where I should be placing the code to select CLOB into collection
    I am hardcoding my query to pull only one row for testing so I do not need another page or page item (eg., :P19_CLOB_ID)..

    Please help me with details as to where to place each part of the code and if we need to run any process or create any dynamic actions.

    Thank you for your help.

    ReplyDelete
  12. All good except the final step.
    Where do you really put the "onload = "javascript:clob_get();"" ???
    Page HTML Body Attribute ?
    Create JavaScript for onPageLoad event?

    ReplyDelete
  13. Anybody looking for this in Apex 18+ (apex.jax.clob is not a constructor error)

    apex.ajax.clob was deprecated and moved to /i/libraries/apex/legacy_18.js (in 18.2 at least). There's a comment to use apex.server instead but it's not very intuitive on how.

    To follow the example:

    On the page you need the clob
    - In Page->JavaScript
    - File URLs, include the legacy_18.js
    /i/libraries/apex/legacy_18.js
    - Function and Global Variables Definition
    Include clob_get function definition from above.
    - Execute when Page Loads
    clob_get();

    There's probably a new way to do it but this at least lets you use the old way.

    ReplyDelete
  14. Man.. It worked like a charm..
    Thanks

    ReplyDelete
  15. This comment has been removed by the author.

    ReplyDelete
  16. Hello Mayo. I'm using Apex 21.2 on a 21C database. Great effort on your part, but I'm having a problem implementing it fully. I have a button on the page that calls a PL/SQL process to copy multiple fields (including a clob, using the code you suggested) from the DB into the page item. It works fine when creating a new row. However, whenever I attempt to edit an existing record, the page item containing the clob displays whatever was last called into the collection. It never seems to clear the collection.
    I placed your suggested code into the page HTML Header, modifying the page item to P52_BODY. I also placed the suggested line of code into the Page HTML Body Attribute.
    My Before Header process code is:
    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 me.body
    into l_clob
    from MAIL_EVENT me
    where me.prim_key = :P52_COPY_FROM_EVENT;
    apex_collection.add_member(p_collection_name => 'CLOB_CONTENT',p_clob001 => l_clob);
    -- Select '**EDIT** ' || me.body into :P52_BODY from MAIL_EVENT me Where me.prim_key = :P52_COPY_FROM_EVENT;
    EXCEPTION
    When NO_DATA_FOUND
    Then :P52_COPY_FROM_EVENT := null;
    END;
    The Execution Options / Point = Before Regions
    P52_COPY_FROM_EVENT contains the primary key of the row from which I want to copy. P52_BODY contains the CLOB.
    Many thanks for your help.
    Is there a way to get your code to execute only when the button is pressed?

    ReplyDelete
  17. I solved the problem. I used Dynamic Actions. The action fires when the button is clicked. I gave two True actions. The first is Execute Server-side Code / PL/SQL. The second was Execute JavaScript Code. I used the one line:
    javascript:clob_get();
    I removed the suggested line from the page's HTML Body:
    onload = "javascript:clob_get();"
    That allowed me to copy the CLOB by clicking on a button.

    ReplyDelete