Download BLOB From Database in Oracle APEX
In this tutorial you will learn how to DOWNLOAD ANY BLOB FILE FROM DATABASE
Solution
For this example, we are going to assume you have a table called BLOB_FILES that holds your files in a BLOB column, along with a column for the MIME type. Here is the code we would use to initiate the file download.
create or replace procedure download_blob (p_file_id IN NUMBER) IS
v_blob_content blob;
v_mime_type varchar2(500);
v_filename varchar2(500);
BEGIN
SELECT "FILE",
MIMETYPE,
FILENAME
INTO v_blob_content,
v_mime_type,
v_filename
FROM BLOB_FILES
WHERE FILE_ID = p_file_id;
sys.HTP.init;
sys.OWA_UTIL.mime_header(v_mime_type, FALSE);
sys.HTP.p('Content-Length: ' || DBMS_LOB.getlength(v_blob_content));
sys.HTP.p('Content-Disposition: filename="' || v_filename || '"');
sys.OWA_UTIL.http_header_close;
sys.WPG_DOCLOAD.download_file(v_blob_content);
apex_application.stop_apex_engine;
EXCEPTION
WHEN apex_application.e_stop_apex_engine THEN
NULL;
END; APEX Item and Process
The following APEX setup is required, whether you plan to initiate the download from a link or a button.
1) Create Application Item
- Shared Components > Application Items
- Click the “Create” button.
- Enter the following details.
- Name: FILE_ID
- Scope: Application
- Session State Protection: Checksum Required – User Level
- Click the “Create Application Item” button.
2) Create Application Process
- Shared Components > Application Processes
- Click the “Create” button.
- Enter the following details.
- Name: DOWNLOAD_BLOB
- Sequence: {accept the default}
- Process Point: Ajax Callback: Run this application process when requested by a page process.
- Click the “Next” button.
- Enter the PL/SQL to perform the download.
BEGIN download_blob(:FILE_ID); END;
- Click the “Next” button.
- Select the “User is Authenticated (not public)” condition type.
- Click the “Create Process” button.
- If you need to add authorisation, click on the new application process, select the authorization scheme and click the “Apply Changes” button.
3) APEX Button
The following process associates the button with the DOWNLOAD_BLOB application process, passing in a FILE_ID value of page item.
- Highlight the button you want to use to initiate the download.
- In the property panel, under the “Behavior” section, select the “Action” of “Redirect to URL”.
- Click the “No Link Defined” button next to the “Target”.
- Enter the following URL.
f?p=&APP_ID.:1:&APP_SESSION.:APPLICATION_PROCESS=DOWNLOAD_BLOB:::FILE_ID:&P11_FILE.
- Click the “OK” button.
4) APEX Link
The following HTML defines a link that calls the DOWNLOAD_BLOB application process, passing in a FILE_ID value of page item.
<a href="f?p=&APP_ID.:1:&APP_SESSION.:APPLICATION_PROCESS=DOWNLOAD_FILE:::FILE_ID:&P11_FILE." download> Click Here </a> to download file.
