Here I am giving an example to display BLOB
contents (PDF, Images) into a region in Oracle Apex page.
Suppose you have an employee screen and whenever the user opens that screen to edit or view record, you want to display employee's payslip whether it is in image format or in PDF format. To perform this task, follow the below steps:
- First of all, you need a table in which you want to store the
BLOB
content and the structure of that table should be as follows:
CREATE TABLE EMP_PAYSLIPS (EMPNO NUMBER, FILENAME VARCHAR2(200), REPORT BLOB, MIMETYPE VARCHAR2(100), CREATED_DATE DATE, CREATED_BY VARCHAR2(100) ); ALTER TABLE EMP_PAYSLIPS ADD CONSTRAINT PK_EMPPAYSLIPS PRIMARY KEY (EMPNO);
Note: The column MIMETYPE
has an important role here. Whenever you store a PDF in the BLOB
data, the MIMETYPE
should be application/pdf
and if storing images then MIMETYPE
should be image/jpeg
.
Also, please check my previous post, in which I have given an example to load PDF files into a BLOB field in Oracle Apex.
- Now I am assuming that you have data in the above table.
- Now in Oracle Apex, click on the Shared Components > Application Process and click the Create button to create an Application Process. The screen will popup as shown in the below image.
- Specify a name in the Name field and for Point select Ajax Callback: Run this application process when requested by a page process option. Then click on the Next button.
- On the next step, add the following PL/SQL code in the PL/SQL code section as shown in the below:
DECLARE vBlob blob; vmimetype varchar2(50); BEGIN SELECT report, mimetype INTO vBlob, vmimetype FROM emp_payslips WHERE empno = V('P3_EMPNO'); 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; END;
- Then click on the Next button and on another screen just click on the Finish button to create the Application Process.
- Now open the Page in Oracle Apex in which you want to display the
BLOB
contents. - Create a new Region and set the type as Static content and put the following code into the Text source, as shown in the below image:
<p align="center"> <iframe src="f?p=&APP_ID.:0:&SESSION.:APPLICATION_PROCESS=display_emp_blob:NO::P3_EMPNO,&P3_EMPNO." width="99%" height="1000"> </iframe> </p>
You can notice in the above code that we are calling the Application Process display_emp_blob
we just created, using the Apex URL format.
Save the changes and run the page to test. Whenever you will open the Employee screen in Edit mode BLOB
data will display in the region, whether it is an image or a PDF file.