This Oracle Apex tutorial shows you, how to create a download BLOB link in the interactive report.
I have the following
EMPLOYEE_DOCS table, in which I have the documents stored for each employee in a
CONTENT and the field
EMPLOYEE_ID is the primary key.
CREATE TABLE "EMPLOYEE_DOCS" ( "EMPLOYEE_ID" NUMBER, "FILENAME" VARCHAR2(100), "CONTENT" BLOB, "MIMETYPE" VARCHAR2(50), "CREATED_DATE" DATE, CONSTRAINT "PK_EMPDOCS" PRIMARY KEY ("EMPLOYEE_ID") USING INDEX ENABLE ) /
Note: We should have the columns
MIMETYPE in our table having the
BLOB data so that we configure the Oracle Apex settings for the Download link. Valid Mime types are, for PDF:
application/pdf, for CSV:
text/csv, for image:
image/jpeg. Now follow these steps to create an Interactive Report with a Download link in Oracle Apex.
Create an Interactive Report with Download Link in Oracle Apex
1. In Oracle Apex, Create an Interactive report with the following query for the above table.
select "EMPLOYEE_ID","FILENAME",sys.dbms_lob.getlength("CONTENT")"CONTENT","MIMETYPE","CREATED_DATE"from "EMPLOYEE_DOCS"
You can notice above that I have used the
sys.dbms_lob.getlength() function to get the
BLOB content-length instead of getting the
BLOB data right away.
2. Now in Oracle Apex page designer, open the report region columns and click on the
CONTENT column and set the following property settings:
- Type: Download BLOB
- Table Owner: Parsing Schema
- Table Name: EMPLOYEE_DOCS
- BLOB Column: CONTENT
- Primary Key: EMPLOYEE_ID
- Mimetype Column: MIMETYPE
- Filename Column: FILENAME
- Last Updated Column: CREATED_DATE
- Download Text: Download
- Content-Disposition: Attachment
The following is the screenshot for the above settings:
Note: The Primary Key column is a must for the above table to configure BLOB settings, if you don't have the Primary Key, then you can use the ROWID. In this case, include the ROWID column in your report query and specify the ROWID as a Primary Key in the above settings.
Save the changes and run the report to test. You will have the output as follows:
On clicking of the Download link, the
BLOB content will be download.