Oracle APEX Tutorial 8 – Up and Downloading Files – Part 2 – Video Training

kevin.landonTechnical Tips5 Comments

Introduction

Reports and data entry constitute the bulk of what most of these tutorials cover, and what many businesses need. However, there is the need to be able to upload documents for anyone with permissions to share, after all, why re-create the same document 100 times if you can just upload it, and others can download it. This tutorial focuses on integrating document upload and download into an APEX application. We recommend you review our Oracle APEX tutorial Preparation and OEHR Sample Data Install article before beginning.

Part 2 – Adding Additional Information and Download Links

[youtube]http://www.youtube.com/watch?v=9d1iNnoFwec[/youtube]

High Level Steps

1) Alterations to Store Additional Information (0:30)
2) Storing Documents in a Custom Table (1:19)
3) Setting Up Document Downloads (2:17)
4) Modifications for Embedded PL/SQL Gateway (4:34) (see notes)

Times in parenthesis are approximate start times for that step

Notes and Resources

Differing Results

Some of the screen shots in the video may vary from your own screen shots as you proceed through this tutorial. This is due to the fact that you may not be on an installation of APEX that was made specifically for training. You may see other files in your report, as your DBA may have other workspaces and items in another part of the installation.

Custom Table

While having a universal table for uploaded files is fine, it presents a serious security threat. So we create a custom table in which to store our uploaded files. What we actually do is add a new column to the File Subject table we already created. This will allow us to restrict access to the files to those with access to the application.

Download Procedure Links between Oracle 11g and others

If you are using APEX in an Oracle 11g environment, you can skip the steps and procedures outlined from approximately 3:39 to 4:34 in the video. For 11g, you need a different procedure that begins at the later time mark. This is due to an issue where the Oracle XML DB HTTP server with embedded PL/SQL Gateway (most typically seen in 11g) can produce an error if a given PL/SQL procedure (download_my_file) is called directly from a URL that is not known to the list of allowed procedures. To correct this, we are taking a method where we create a second page that branches to the PL/SQL procedure before main page processing to populate the required information so the error cannot occur. You never actually see the second page during application operation due to the hidden nature of the item on it, and the way it is set up to run.

Security Issues

Even with creating a custom table for file storage, a user (malicious, curious or otherwise) can potentially manipulate the location address in the browser and gain access to files they are not meant to. The need for securing a given workspace or uploaded files can vary and determining that is beyond the scope of this tutorial. There are however a few things you can do to further limit access to the files. One technique is to use the built in APEX security API’s inside the download_my_file procedure to perform a check that the user is properly authenticated before allowing the download. One such way to do this is to insert at the beginning of the procedure the following code:

APEX_APPLICATION.G_FLOW_ID := ;
IF NOT wwv_flow_custom_auth_std.is_session_valid THEN
--
--
-- display this message or a custom message.
--
htp.p('Unauthorized access - file will not be retrieved.');
--
-- You can do whatever else you need to here to log the
-- unauthorized access attempt, get the requestor's
-- IP address, send email, etc.
--
RETURN;
END IF;

Note this isn’t the only way, and you should consult your IT Department and see what levels of
security are needed or required for your needs.

Code and Pasted Text

Updated Region Source

SELECT w.id,w.name,s.subject
FROM APEX_APPLICATION_FILES w,oehr_file_subject s
WHERE w.name = s.name

Table alteration command

ALTER TABLE oehr_file_subject ADD(id NUMBER,blob_content BLOB,mime_type
varchar2(4000) );

New Insert File Description Process source

IF ( :P1_FILE_NAME IS NOT NULL ) THEN
INSERT INTO oehr_file_subject(id,NAME, SUBJECT, BLOB_CONTENT, MIME_TYPE)
SELECT ID,:P1_FILE_NAME,:P1_SUBJECT,blob_content,mime_type
FROM APEX_APPLICATION_FILES
WHERE name = :P1_FILE_NAME;
DELETE FROM APEX_APPLICATION_FILES WHERE name = :P1_FILE_NAME;
END IF;

Download file procedure

CREATE OR REPLACE PROCEDURE download_my_file(p_file IN NUMBER) AS
v_mime VARCHAR2(48);
v_length NUMBER;
v_file_name VARCHAR2(2000);
Lob_loc BLOB;
BEGIN
SELECT MIME_TYPE, BLOB_CONTENT, name,DBMS_LOB.GETLENGTH(blob_content)
INTO v_mime,lob_loc,v_file_name,v_length
FROM oehr_file_subject
WHERE id = p_file;
--
-- set up HTTP header
--
-- use an NVL around the mime type and
-- if it is a null set it to application/octect
-- application/octect may launch a download window from windows
owa_util.mime_header( nvl(v_mime,'application/octet'),
FALSE );
-- set the size so the browser knows how much to download
htp.p('Content-length: ' || v_length);
-- the filename will be used by the browser if the users does a save as
htp.p('Content-Disposition: attachment;
filename="'||REPLACE(REPLACE(substr(v_file_name,instr(v_file_name,'/')+1),chr(10),NULL),chr(13)
 
,NULL)|| '"');
-- close the headers
owa_util.http_header_close;
-- download the BLOB
wpg_docload.download_file( Lob_loc );
END download_my_file;
/

Privilege Grant command

GRANT EXECUTE ON download_my_file TO PUBLIC
/

Uploaded files new source

SELECT s.id,s.name,s.subject FROM oehr_file_subject s

Uploaded Files Report Link new URL

#OWNER#.download_my_file?p_file=#ID#

Page 2 PL/SQL Process

download_my_file(:P2_DOC_ID)

5 Comments on “Oracle APEX Tutorial 8 – Up and Downloading Files – Part 2 – Video Training”

  1. Hello, I have small problem with download_my_file procedure, after the start procedure, webbrowser set name of the downloaded file as “f” or “f.txt”. What I doing wrong?

  2. You forgot to mention you need to update the apex_030200.wwv_flow_epg_include_mod_local function to allow your procedure to run in the non 11g env.

  3. Actually i have 2 questions:
    1) when i create the file_browse item on the settings page i am asked for storage type with 2 options:
    BLOB clumn specified in item Source attribute.
    Table WWV_FLOW_FILES wich one should iI choose.

    2)After I created the region with the file browse I click on submit but nothing is saved on the application files table so when the process to copy the registry from applicatio_files to oehr_file_subject does not insert anything into the table

  4. It does not work for me. I have tried part 1 and 2 at least 5 times and did not get it to work. My databases where APEX is installed was 11.2.0.2 (version of APEX Application Express 4.1.0.00.32) and 11.2.0.3 (Application Express 4.2.0.00.27)

    I was not able to get any file uploaded into a table oehr_file_subject
    and then get it back downloaded to my PC.
    Something is missing in these tutorials.
    Any help would be great.
    Thank you,
    vr

Leave a Reply to Krzysztof Cancel reply

Your email address will not be published. Required fields are marked *