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
Create TABLE SUPPLIERS ( SUPPLIER_ID INTEGER, SUPPLIER_NAME VARCHAR2(100), ADDRESS VARCHAR2(100), PHONE VARCHAR2(40), AGREEMENT_DOC CLOB, MIMETYPE VARCHAR2(1000) ) /
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:
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:
select SUPPLIER_ID, SUPPLIER_NAME, ADDRESS, PHONE, 'Preview' Preview from SUPPLIERS
Below is the screenshot for the 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"> </iframe> </p>
Below is the screenshot for the above settings:
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:
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:
DECLARE vClob CLOB; vblob blob; vmimetype varchar2(1000); L_DEST_OFFSET INTEGER := 1; L_SRC_OFFSET INTEGER := 1; L_LANG_CONTEXT INTEGER := DBMS_LOB.DEFAULT_LANG_CTX; L_WARNING INTEGER; L_LENGTH INTEGER; BEGIN 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 begin DBMS_LOB.CONVERTTOBLOB( DEST_LOB => vblob, 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 ); end; owa_util.mime_header(vmimetype,false); htp.p('Content-Length: ' || dbms_lob.getlength(vblob)); owa_util.http_header_close; wpg_docload.download_file(vblob); exception when no_data_found then null; when others then null; END;
Below is the screenshot for the above setting:
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; apex.jQuery('#preview1').trigger('apexrefresh'); }
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:
$("#preview1").stickyWidget({toggleWidth:true});
Below is the screenshot for the above JavaScript code reference:
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)
javascript:getClob(#SUPPLIER_ID#);
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.
Do you think it is complicated? Don't worry you can download this Oracle Apex application from the GitHub with the link below: