Download a BLOB file using the Cards Region - Oracle Apex v21.1

Question

I am trying to create some sort of "archives" pages where users can download the reports of previous years. To do this, I first tried to create a table which will hold my documents, (with a MIMETYPE, BLOB, FILENAME, CHARSET columns, etc...).

CREATE TABLE tb_document
(
    pk_document NUMBER,
    nom_document VARCHAR2(4000),
    mimetype_document VARCHAR2(512),
    charset_document VARCHAR2(512),
    blob_document VARCHAR2(512),
    comment_document VARCHAR2(4000),
    tags_document VARCHAR2(4000),
    creation_document TIMESTAMP(6),
    PRIMARY KEY(pk_document)
);

After uploading some files, I have this type of report :

By clicking on the download icon, I can successfully download the file.

However, I don't like the look of the report. I then tried to create a Cards region, which will display my files, here's what I did, which I like more :

The problem is that I don't have the BLOB download link anymore, so I tried to add an action to the card region, which by clicking on it, will redirect to a specific URL

This leads me to my question, what is the URL that does the same action as the BLOB download link , and how then how to download the specific file that I'm clicking on the card ?

Do not hesitate to ask for more details, thanks in advance,

Thomas

Answer 1

I have this result :

Which gives me these downloads :

Here's how I did :

1. Create this procedure in SQL Commands

CREATE OR REPLACE PROCEDURE get_file (p_file_id  IN VARCHAR2) IS --The parameter will be the ID of the file in my document table
  l_blob_content  tb_document.blob_document%TYPE;       --The BLOB FILE
  l_mime_type     tb_document.mimetype_document%TYPE;   --The MIMETYPE of the File
  l_nom_document  tb_document.nom_document%TYPE;        --The name of the file
BEGIN
  SELECT blob_document,
         mimetype_document,
         nom_document
  INTO   l_blob_content,
         l_mime_type,
         l_nom_document
  FROM   tb_document
  WHERE  pk_document = p_file_id; --SELECT the BLOB file and its information based on the ID from the document table

  -- This below creates the download
  sys.HTP.init;
  sys.OWA_UTIL.mime_header(l_mime_type, FALSE);
  sys.HTP.p('Content-Length: ' || DBMS_LOB.getlength(l_blob_content));
  sys.HTP.p('Content-Disposition: attachment; filename="' || l_nom_document || '"'); --Create a download with the name of the original file
  sys.OWA_UTIL.http_header_close;                                                    --If you remove "attachment;", the file will not be downloaded automatically, but opened in a new Tab

  sys.WPG_DOCLOAD.download_file(l_blob_content);                                     --File is downloaded
  apex_application.stop_apex_engine;
EXCEPTION
  WHEN apex_application.e_stop_apex_engine THEN
    NULL;
  WHEN OTHERS THEN
    HTP.p('Whoops');
END;
/

2. Create an Application Item

• Name : FILE_ID
• Scope : Application

3. Create an Application Process

• Sequence : 1
• Process Point : Ajax Callback: Run this application process when requested by a page process
• PL/SQL Code :

BEGIN
  GET_FILE(:FILE_ID);
END;

4. Go to your page where your Cards Region is

• Under your Cards region, right-click on "Actions" -> "Create Action"

5. Configure your action with the following settings :

• Identification > Type : Full Card
• Link > Type : Redirect to URL
• Link > Target : f?p=&APP_ID.:0:&APP_SESSION.:APPLICATION_PROCESS=GET_FILE:::FILE_ID:&PK_DOCUMENT.

The only thing to modify is &PK_DOCUMENT., for example, if the ID of your document table is ID_DOCUMENT, it will be &ID_DOCUMENT.

You are now able to download your files.

---

Now, If you want the same look, here's how to do :

6. SQL Query for the Cards Region

select PK_DOCUMENT,
       NOM_DOCUMENT,
       MIMETYPE_DOCUMENT,
       CHARSET_DOCUMENT,
       BLOB_DOCUMENT,
       COMMENT_DOCUMENT,
       TAGS_DOCUMENT,
       EXTRACT(YEAR FROM TRUNC(CREATION_DOCUMENT)) AS ANNEE, --extract the year from the date
    CASE -- if the type of the document is PDF, change the font awesome icon to PDF File
    WHEN mimetype_document = 'application/pdf' THEN
    'fa fa-file-pdf-o'
    -- if the type of the document is Excel, change the font awesome icon to Excel File
    WHEN mimetype_document = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' 
    THEN 'fa fa-file-excel-o'
    -- if the type of the document is PNG, change the font awesome icon to Image File
    WHEN mimetype_document = 'image/png' 
    THEN 'fa fa-image'
  END as ico_document --creates a column just to display the font awesome text
  from TB_DOCUMENT
ORDER BY ANNEE DESC; --order the list from most recent to the oldest

7. Change the Attributes of the Cards Region

• Card > Primary Key Column 1 : [YOUR ID COLUMN]
• Title > Column : [YOUR FILENAME COLUMNN]
• Body > Column : [YOUR COMMENT COLUMN] (for example, to give more information about the document)
• Icon and Badge > Icon Source : Icon Class Column
• Icon and Badge > Icon Column : [THE ICON_COLUMN CREATED IN THE CASE STATEMENT IN THE SQL QUERY]
• Icon and Badge > Badge Position: [MY "ANNEE" COLUMN, WHICH WILL DISPLAY THE YEAR]

That's all ! :-)