Oracle Apex Download BLOB link output.

Oracle Apex - Create Download BLOB Link in Report

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

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 BLOB column 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 FILENAME and 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

BLOB Attributes

  • 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

Appearance

  • Download Text: Download
  • Content-Disposition: Attachment

The following is the screenshot for the above settings:

Oracle Apex download BLOB link 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:

Oracle Apex Download BLOB link output.

On clicking of the Download link, the BLOB content will be download.

Related tutorials:

This Post Has 13 Comments

  1. Marc August 27, 2020

    Hello,

    it works great, just one question, when I want to download the file uploaded as abc.doc, it is downloaded with a file name "download" not with a name of the file abc.doc (FILENAME from a database).
    How can I fix this?

    Thanks

    1. Vinish Kapoor August 27, 2020

      It is very simple, even I mentioned above.

      If your table which is having the blob column, also having the filename field, then specify it in the BLOB attributes.

      Then it will be downloaded with the name stored in the filename field.

      I am sharing the image below again:

    2. Marc August 27, 2020

      Thanks

  2. furqan September 22, 2020

    I want to open the document with in the browser.How to do it ?

    2. furqan September 22, 2020

      Not displaying word document

    3. Vinish Kapoor September 23, 2020

      For MS Word, use CLOB not BLOB.

    4. furqan September 23, 2020

      Cant we use a single method to upload files(word/pdf etc)
      on my server or in database ?

    5. Vinish Kapoor September 23, 2020

      You can use BLOB to upload any file. Try it and let me know if any issues.

  3. ete October 20, 2020

    Thank for the tutorial. I have two questions. First where the files (mydoc.pdf) should be stored and second I don't see the download link text after applying the settings above.

    Thanks

  4. Nilamkunvar Jam January 24, 2021

    I can't get values for mime type. how can i do that?

  5. Tushar Mittal June 16, 2021

    Hi Vinish,
    I have one question, how we can add css class on download ? I want to add a icon in place of download.

  6. Sonal July 22, 2021

    Hi, I have tried the exact same steps, but it is still not working for me. Moreover, the sys.dbms_lob.get_length() displays the size of the file, does not download exact file.

Comments are closed.