Introduction

Reports and data entry constitue 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, afterall, 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 reccomend you review our Oracle APEX tutorial Preparation and OEHR Sample Data Install article before beginning.

Part 2 - Adding Additional Information and Download Links

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)

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 colum 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 enviornment, 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;
/

Privledge 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)

Related Information:

1. Oracle APEX Tutorial 8 - Up and Downloading Files - Part 1 - Video Training

   Reports and data entry constitue the bulk of what most of these tutorials cover, and what many businesses need. However,...

2. Oracle APEX Tutorial 10 - Access Control - Part 2 - Video Training

   Security. We hear about it everywhere. Make this more secure, make this less secure. In the computer industry this is...

3. Oracle APEX Tutorial 10 - Access Control - Part 1 - Video Training

   Security. We hear about it everywhere. Make this more secure, make this less secure. In the computer industry this is...

4. Oracle APEX Tutorial 9 - JavaScript - Video Training

   JavaScript is everywhere on the Internet today. Even if you don't realize it, this handy little scripting tool has become...

5. Oracle APEX Tutorial 4 – Form Layout – Part 1 – Video Training

   APEX gives you a lot of power and flexibility with its built in wizards. They are very good at creating...

6. Oracle APEX Tutorial 2 - Parameterized Report Part 2 - Video Training

   This Tutorial is a guided walkthrough of section 3 of the Oracle APEX Advanced Tutorials....

7. Install Oracle Apex 3.2 on XE and XP Pro - Video Walkthrough Tutorial

   This tutorial is a supplment to the install video linked to below. It takes you through the process of installing...

8. Oracle APEX Tutorial 2 - Parameterized Report Part 1 - Video Training

   This Tutorial is a guided walkthrough of section 3 of the Oracle APEX Advanced Tutorials....