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)
Notes and Resources
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.
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.
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
Page 2 PL/SQL Process