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
BLOBcontent 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
BLOBcontents. - 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.
Hey man, nice post. But how can you do it passing parameters?
In the above example, the page has an item p3_empno. Now suppose you are calling this page from another page by setting the p3_empno a value. The blob will show for that particular employee.
I have a problem with mozzila, It starts automatticlly do download content from iframe, have you any sollution. Chrome works fine.....
I have change it from iframe to embeded tags and it works...
<p align="center"> <iframe src="javascript:apex.navigation.dialog.close(false,'/ords/r/r2vm_notaria_digital/notaria-digital/0?request=APPLICATION_PROCESS%3DDISPLAY_ESCRITURA_BLOB&session=16229955116938');" width="99%" height="1000"> </iframe></p>
Hi,
thanks for the post, it's very useful. I'm trying to create it under Apex 20.1 version and the page shows the error "Process show_stored_search not found." where show_stored_search is the Ajax process.
Could you help me?
Thanks
I think the process name here is case sensitive. So please specify the process name the same as you specified at the time of creation.
Facing Same issue. Process name matches all lower case as defined
did you find solution for this?
HI, I have a requirement to open BLOB files which are stored in DB. But there is no mime type define in that table. I was wondering if there is any way to have the browser correctly download BLOB content without knowing the MIME-TYPE and show the file ? Many thanks in advance.
nice post! How would it be with friendly-url?
Hi
Thank you for the post, it is really very helpful.
Can you tell us how to do it for multiple records? let's say displaying employee_blob for each employee of a particular department.
I am generating QR code for each employee using plsql procedure and storing the blob in a table along with employee_id and department_no.
I am calling the process in PLSQL dynamic content inside employee's loop,
passing it new employee id each time the loop is executed (employee id is set using apex_util.set_session_state).
Now if i select department 10 the loop will iterate 3 times.
qr code will be stored for 3 employees 101,102,103
but the qr code of 103 will be displayed for the other two instead of their own.
can you please help?
Really great post. Much appreciated!
hi, does this work on mobile devices too?
Hi Vinish, great post !
in my case (APEX 21.1) in step (8) in the source of the iFrame I had to delete one ' : ' in front of :P3_EMPNO
then it did work
Hello, does this one work with friendly urls too?
Hello, it does not work if page is public...
Application Process does not have authorization!
Could you please tell me what to do?
Open your application process and from the Authorization drop-down, select the -No authorization is required- option.
Thanks, I have already done that with no effect.
The document dos not get loaded.
Could you please try it yourself?
Hi,
based on the correct APEX URL syntax, the correct fix is not to delete one ' : ' in front of :P3_EMPNO, but to replace comma ' , ' before &P3_EMPNO. by ' : ' . Then it works fine.
Vinish,
thank you for the great post - simple and effective solution.