Displaying CLOB Contents in Oracle Apex

Displaying CLOB Contents in Oracle Apex

In this tutorial, you will learn how to display CLOB contents in Oracle Apex. The Character large object (CLOB) is a collection of character data in the database. Usually stores the large data like HTML, XML, CSV, Plain Text, Word and Excel file, etc. Here I am giving an example to display the CLOB contents into a region in Oracle Apex. Follow these steps:

Steps to Display CLOB Contents in Oracle Apex Page Region

To demonstrate the following example, I am using the SUPPLIERS table. To test this example in your Oracle environment, create the table as shown below:

1. Create the following table with CLOB column


Insert some data in the above table with some CLOB data also and provide the correct MIME TYPE for each type of CLOB content. For example:

  • For HTML Data provide mime type text/html.
  • For CSV Data > text/csv
  • For Plain Text > text/plain
  • Word file > msword
  • Excel file > ms-excel

Below is the screenshot of the data:

CLOB data example.

2. Create an Interactive Report in Oracle Apex

Create an interactive report in Oracle Apex based on the above table. Use the following query to display all the data except the CLOB column:

       'Preview' Preview

Below is the screenshot for the report settings:

Oracle Apex - Interactive report settings.

3. Create a Page Item

Do the right-click on the interactive report region and from the shortcut menu select Create Page Item. Set the name as SUPPLIER_ID, my page number is 2 so the page item name is P2_SUPPLIER_ID.

4. Create a Static Content Region

Do the right-click on the content region and from the shortcut menu select Create Region and set the following properties:

  • Title: Preview
  • Type: Static Content
  • Start New Row: No
  • Advanced > Static ID: preview1
  • Source > Text: Add the following code:
<p align="center">
<iframe id="myFrame1" src="" width="99%" height="750">

Below is the screenshot for the above settings:

Oracle Apex: Static region settings to display CLOB contents.

Save the changes for the page.

5. Create an Application Process

Now create an application process in Oracle Apex. To do this, click on the Shared Components > Application Process and then click on the Create button. The following window will appear:

Create application process in Apex.

Specify the name as preview_clob_data and select the Ajax Callback for the Point drop-down.

Then click on the Next button. And in the next screen paste the following PL/SQL code, as shown below:

  vClob CLOB;
  vblob blob;
  vmimetype varchar2(1000);
    L_SRC_OFFSET     INTEGER := 1;
    L_LENGTH         INTEGER;

 Dbms_Lob.Createtemporary(Lob_Loc => vclob,
                                   Cache   => TRUE,
                                   Dur     => Dbms_Lob.Call);

 Dbms_Lob.Createtemporary(Lob_Loc => vblob,
                                   Cache   => TRUE,
                                   Dur     => Dbms_Lob.Call);                                   

  SELECT agreement_doc, mimetype  INTO vClob, vmimetype
                FROM suppliers
                WHERE supplier_id = :P2_SUPPLIER_ID;

-- tranform the input CLOB into a BLOB of the desired charset
                            SRC_CLOB     => vclob,
                            AMOUNT       => DBMS_LOB.LOBMAXSIZE,
                            DEST_OFFSET  => L_DEST_OFFSET,
                            SRC_OFFSET   => L_SRC_OFFSET,
                            BLOB_CSID    => NLS_CHARSET_ID('WE8MSWIN1252'),
                            LANG_CONTEXT => L_LANG_CONTEXT,
                            WARNING      => L_WARNING
  htp.p('Content-Length: ' || dbms_lob.getlength(vblob)); 
  when no_data_found then
 when others then 

Below is the screenshot for the above setting:

PL/SQL code for Application process in Apex.

After that click on the Next button and on the next step click on the Create Process button.

Now come back to your page where you created the report and follow the remaining steps:

6. Create a JavaScript Function

Click on the page and in the Properties, palette add the following JavaScript code in the Function and Global Variable Declaration section:

function getClob(p_supplier_id)
    apex.item( "P2_SUPPLIER_ID" ).setValue (p_supplier_id);
    document.getElementById("myFrame1").src = "f?p=&APP_ID.:0:&SESSION.:APPLICATION_PROCESS=preview_clob_data:NO:RP:P2_SUPPLIER_ID:" + p_supplier_id;



7. Add JavaScript Code to Make the Preview Region Sticky

The Preview region, we created in the 4th step, should be sticky when the user will scroll the report. So add the following JavaScript code in the Execute when Page Loads section as shown below:


Below is the screenshot for the above JavaScript code reference:

Oracle Apex - Execute JavaScript on Page load.

8. Create a Link for Preview in the Interactive Report

Click on the Preview column of the report and set the type as Link and paste the following JavaScript code for the Link Target as URL type: (Also shown in the below image)


Create a link in report to preview the CLOB data.

Save the changes and run the page to test. Now when you will click on the Preview link it will display the CLOB contents in the Static Region without refreshing the whole page.

Displaying CLOB contents in Oracle Apex output.

Do you think it is complicated? Don't worry you can download this Oracle Apex application from the GitHub with the link below:

Related tutorials:

This Post Has 23 Comments

  1. Marc


    I have tried a little modified code but unfortunately I always get Oracle get data services
    400 Bad Request{timeStamp} | {requestId}

    1. Vinish Kapoor

      I think you are missing something because the above code is tested.

      I have given the link above to download this app from GitHub, you should download it and install it in your workspace to test.

      Then you can import that specific page from this app to your application.

    2. Marc

      Thanks for fast response.
      I have installed the app and it worked well until I set longer supplier_id := 12345678901234567890. In that case strange behaviour started. Somethimes I have got error (400 Bad Request{timeStamp} | {requestId}), sometimes nothing, sometimes worked well.....

    3. Vinish Kapoor

      Is this supplier id really exists? if not then you should not have to worry about it.

      You can do one more thing, in the Ajax process, modify the Exception area as below:

         when no_data_found then
           htp.p('Supplier id not found.');
         when others then
           htp.p('An error occurred.');

      After making the above changes, it will not show any other error messages. Please try.

    4. Marc


      in case supplier_id = 1 - OK
      in case supplier_id = 123456789012345678 'Supplier id not found.'

    5. Vinish Kapoor

      Then it means it is not found. You should check in your database.

    6. Marc

      I won't bother anymore.
      I doubt it is Database (Suppliers), It is only one row with supplier_id edited and tested with different values (lengths) using SQL Workshop, Tables, Data Edit. It seems that a length of the supplier_id is the reason because the problem occurs only if lenght > certain lenght. 
      If I replace in prew_clob_data in select, WHERE, P2_SUPPLIER_ID with value '12345678901234567890' (suppliers_id in the table) works well, so I suppose that the problem is 'transfer' P2_SUPPLIER_ID to prew_clob_data if the length is too long.
      Anyway, best regards

    7. Marc

      Another one result in Preview window after Preview button clicked:

      xjnr4bcss0cbo6c-dbparbih.adb.eu-frankfurt-1.oraclecloudapps.com refused to connect.

    8. Vinish Kapoor

      It seems like connection issue. I have no idea about it. I have just tested this app here and it is working fine.

    9. Tor

      Did you turn off friendly URLs? I had the same problem with friendly URLs turned on

  2. Mike

    Hi Vinish,
    Is this possible with a blob instead of a clob?
    I need the user to be able to preview a pdf using your method here, or the user will download the pdf.
    Please let me know, thank you.

    1. Mike

      Awesome, thank you.

  3. Tor

    This is great, thank you for this! But it only seems to work if friendly URLs is turned off in Application Definition Attributes I assume because of this which use the old non friendly URL

    document.getElementById("myFrame1").src = "f?p=&APP_ID.:0:&SESSION.:APPLICATION_PROCESS=preview_clob_data:NO:RP:P2_SUPPLIER_ID:" + p_supplier_id;

    I tried to change it to:

    document.getElementById("myFrame1").src = apex.util.makeApplicationUrl({pageId:0,session: $v( "pInstance" ), request: 'APPLICATION_PROCESS=preview_clob_data',itemNames:['P2_SUPPLIER_ID'], itemValues:[p_supplier_id]});

    But that also only work with Friendly URLs turned off. Do you have a solution for friendly URLs?

    1. Vinish Kapoor

      If using friendly URL then me also facing some issues in passing parameter. But so far I found the following approach:

      Change the static region content as follows:

      <p align="center">
      <iframe id="myFrame1" src="f?p=&APP_ID.:0:&SESSION.:APPLICATION_PROCESS=preview_clob_data" width="99%" height="750">

      Change the getClob function as follows:

      function getClob(p_supplier_id)
          apex.item( "P2_SUPPLIER_ID" ).setValue (p_supplier_id);
          document.getElementById("myFrame1").src = "f?p=&APP_ID.:0:&SESSION.:APPLICATION_PROCESS=preview_clob_data";

      In this case, I am not passing the supplier id as parameter, instead setting the item value and submitting it using the apex.submit() method.

    2. Tor Loege

      Thanks Vinish! Works like a charm!

    3. Tor Loege

      Thanks but I just realized that with this method, the page is submitted every time you click on preview. That obviously takes longer but more importantly it means you lose your place in the report.

      Oh well, no big deal I will just use the old URL "unfriendly" URL construct for this app.

    4. Vinish Kapoor

      Yes, that is the problem. I am looking for the solution to how to pass the parameters to application process URL in friendly URL format. Yesterday I tried everything but it didn't work.

      When I will resolve this I will comment again here to let you know.

    5. Vinish Kapoor

      Finally, the following approach is working with friendly URL without submitting the whole page.

      Revert the static region changes:

      <p align="center">
      <iframe id="myFrame1" src="" width="99%" height="750">
      function getClob(p_supplier_id)
          apex.item( "P2_SUPPLIER_ID" ).setValue (p_supplier_id);
       // below code will set the p2_supplier_id value
        apex.server.process ( "SAVE_HIDDEN_VALUE_IN_SESSION_STATE", {
           x01: "set_session_state",
           pageItems: "#P2_SUPPLIER_ID"
           }, {dataType: 'text'} );
          document.getElementById("myFrame1").src = "f?p=&APP_ID.:0:&SESSION.:APPLICATION_PROCESS=preview_clob_data";

      The above JavaScript code will set the page item value p2_supplier_id including its session state so that the Ajax process can see it.

    6. Tor Loege

      Very good looks great! Thank you sir!

  4. Ivan

    I have a question: how can I make a modal window or a form from the region "Preview" so that it opens when the link is clicked.
    That is, I want to display the preview only when clicked and in front of the main region and close it after viewing.

  5. Venkat

    I have a slightly different use case where I am generating html content on the fly using PL/SQL code. For some reason, the provided JS code to set the iframe source didn't work. Searched the internet and found it. But this works now: document.getElementById("myFrame1").src = "javascript:'"+html_content+"'";

  6. Maarten

    This is a clever approach, thanks for sharing.
    But I'm having an issue, the files keeps getting downloaded in stead of it's contents showing in the preview region.

Comments are closed.