Introduction:
- For displaying the Blob content in the column in interactive grid, we don’t have a predefined column type for blob content display. This can be implemented using custom solution with some of the features in the Oracle Apex.
Why we need to do:
- Customers requirement to upload images/PDF files into the table and they need to view the blob content in reports or download it for their use as well as they need to edit the row level data. In that case, We need blob content to be displayed and along with interactive grid design.
- If we use PL/SQL procedure and directly calling using URL, this will have security concerns because default configuration of Oracle Apex has a white list of entry points, callable from a URL.
How do we solve:
- The easiest way to return an image/pdf by URL in Oracle Apex is either via Restful Services or Application Process on Demand. In this blog, I am explaining one of the best way of using On Demand Application process which will allow us to have conditions or authorization Schemes also in place.
Step 1:
- Create an Interactive report region and use the below query as mentioned.
- For Example,
SELECT
ID,
FILENAME,
NAME,
MIME_TYPE,
NULL AS BLOB_CONTENT
FROM
APEX_APPLICATION_FILES;
Screenshot:
Step 2:
- Create an item holding the value of Report column (ID) as hidden item.
Step 3:
1. Create an application process by navigate through shared Components->Application Processes.
2. Create new process-> give process name.
Sequence of execution value->1
Point-> Ajax call back and click Next.
3. Paste the below code into the code section and click next, create process.
DECLARE
v_Blob blob;
v_mime_type varchar2(50);
BEGIN
SELECT BLOB_CONTENT, MIME_TYPE INTO v_Blob, v_mime_type
FROM APEX_APPLICATION_FILES
WHERE ID = V(‘P2_ID’);/* your item name*/
owa_util.mime_header(v_mime_type,false);
htp.p(‘Content-Length: ‘ || dbms_lob.getlength(v_Blob));
owa_util.http_header_close;
wpg_docload.download_file(v_Blob);
exception
when no_data_found then
null;
END;
Step 4:
- Create an URL Type Region and paste the URL as below.
Create an URL Type Region and go to Attributes->setting menu.
URL:
f?p=&APP_ID.:0:&SESSION.:APPLICATION_PROCESS=app_process_image:NO
- Mention the Application Process name in the value of Application Process in the URL.
- If you want to fix the height and width for images you can mention in
- Iframe attribute.
Step 5:
- Go to Blog content column in the interactive grid and make it as type of Link.
- Target: Page in this application, mention the page number as same page.
- Map the Item value with the id column in the Set items option and click Okay.
Final Outcome:
Image:
PDF:
Conclusion:
- This way you can give customized solution with interactive grid to view the Blob content (Image/PDF).It is one of the useful method for the user to access blob content in the reporting page itself.
