9 How to Upload and Download Files in an Application

Oracle Application Express applications support the ability to upload and download files stored in the database. This tutorial illustrates how to create a form and report with links for file upload and download, how to create and populate a table to store additional attributes about the documents, and finally how to create the mechanism to download the document in your custom table.

This section contains the following topics:

For additional examples on this topic, please visit the following Oracle by Examples (OBEs):

Creating an Application

First, create a new application using the Create Application Wizard with the assumption you will include an upload form on page 1.

To create an application using the Create Application Wizard:

  1. On the Workspace home page, click the Application Builder icon.

    The Application Builder home page appears.

  2. Click Create.

  3. Select Create Application and then click Next.

  4. For Name, specify the following:

    1. For Name, enter Download App.

    2. Accept the remaining defaults and click Next.

  5. Add a blank page:

    1. Under Select Page Type, select Blank and click Add Page.

      The new page appears in the Create Application list at the top of the page.

    2. Click Next.

  6. For Tabs, accept the default, One Level of Tabs, and click Next.

  7. For Copy Shared Components from Another Application, accept the default, No, and click Next.

  8. For Attributes, accept the defaults for Authentication Scheme, Language, and User Language Preference Derived From and click Next.

  9. For User Interface, select Theme 2 and then click Next.

  10. Review your selections and click Create.

    The Application home page appears.

Creating an Upload Form

Once you create an application, the next step is to create a form to upload documents. In the following exercise, you create a form in an HTML region that contains a file upload item and a button. The button submits the page and returns the user to the same page.

Topics in this section include:

Create an HTML Region

First, you need to create a container to hold the form. In Application Builder, this container is called a region.

To create an HTML region:

  1. Click the Page 1 icon.

    The Page Definition appears.

  2. Under Regions, click the Create icon as shown in Figure 9-1.

    Figure 9-1 Create Icon

    Description of Figure 9-1 follows
    Description of "Figure 9-1 Create Icon "

  3. For Region:

    1. Identify the type of region to add to this page - Accept the default, HTML, and click Next.

    2. Select the type of HTML region container you wish to create - Accept the default, HTML, and click Next.

  4. For Display Attributes:

    1. Title - Enter Submit File.

    2. Accept the remaining defaults and click Next.

  5. Accept the remaining defaults and click Create Region.

    The Page Definition appears.

Create an Upload Item

Next, you need to create a text field or item. In Application Builder, an item is part of an HTML form. An item can be a text field, text area, password, select list, check box, and so on. In this exercise, you will create a File Browse item. When you create a File Browse item, files you upload are stored in a table named wwv_flow_file_objects$.

To create a file upload item:

  1. Under Items on the Page Definition for page 1, click the Create icon.

  2. For Item Type, select File Browse and then click Next.

  3. For Display Position and Name:

    1. Item Name - Enter P1_FILE_NAME.

    2. For Sequence, accept the default.

    3. For Region, select Submit File.

    4. Click Next.

  4. Accept the remaining defaults and click Next.

  5. Click Create Item.

    The Page Definition appears.

Create a Button

Next, you need to create a button to submit the file.

To create a button:

  1. Under Buttons, click the Create icon.

  2. For Button Region, select Submit File (1) 1 and click Next.

  3. For Button Position, select Create a button in a region position and then click Next.

  4. On Button Attributes:

    1. Button Name - Enter Submit.

    2. Accept the remaining defaults.

    3. Click Next.

  5. For Button Template, accept the default and click Next.

  6. For Display Properties, accept the defaults and click Next.

  7. For Branching:

    1. Branch to Page - Select Page 1.

      This selection causes the page to call itself on submit rather than navigate to another page.

    2. Click Create Button.

  8. Run the page by clicking the Run Page icon as shown in Figure 9-2.

    Figure 9-2 Run Page Icon

    Description of Figure 9-2 follows
    Description of "Figure 9-2 Run Page Icon"

  9. If prompted to enter a user name and password, enter your workspace user name and password and click Login. See "About Application Authentication".

When you run the page, it should look similar to Figure 9-3.

Figure 9-3 Submit File Form

Description of Figure 9-3 follows
Description of "Figure 9-3 Submit File Form"

Creating a Report with Download Links

Once you create the form to upload documents, the next step is to create a report on the document table that contains links to the uploaded documents. When you use a File Browse item, the files you upload are stored in a table called wwv_flow_file_objects$. Every workspace has access to this table through a view called APEX_APPLICATION_FILES.

Topics in this section include:

Create a Report on APEX_APPLICATION_FILES

To create a report on APEX_APPLICATION_FILES:

  1. Click Edit Page 1 on the Developer toolbar at the bottom of the page.

    The Page Definition appears.

  2. Under Regions, click the Create icon.

  3. For Region, select Report and then click Next.

  4. For Report Implementation, select SQL Report and then click Next.

  5. For Display Attributes:

    1. Title - Enter Uploaded Files.

    2. Accept the remaining defaults and click Next.

  6. For Source, enter the following SQL query:

    SELECT id,name FROM APEX_APPLICATION_FILES

  7. Click Create Region.

  8. Run the page.

Your report should resemble Figure 9-4. Note that your display may differ slightly depending on what files you have uploaded.

Figure 9-4 Uploaded Files Report

Description of Figure 9-4 follows
Description of "Figure 9-4 Uploaded Files Report"

Add Link to Download Documents

Next, you need to provide a link to download each document.

To provide a link to download the documents in the report:

  1. Click Edit Page 1 on the Developer toolbar.

  2. Under Regions, click Report next to Uploaded Files as shown in Figure 9-5.

    Figure 9-5 Report Link

    Description of Figure 9-5 follows
    Description of "Figure 9-5 Report Link"

    The Report Attributes page appears. You can add a link to the ID column by editing Column Attributes.

  3. Under Column Attributes, click the Edit icon in the ID row.

  4. Scroll down to Column Link.

  5. Under Column Link:

    1. Link Text - Select #ID#.

    2. Target - Select URL.

    3. In the URL field, enter the following:

      p?n=#ID#

      #ID# parses the value contained in the column where ID is the column alias.

  6. At the top of the page, click Apply Changes.

  7. Run the page.

    When you run the page, it should look similar to Figure 9-6.

    Figure 9-6 Uploaded Files Report with Download Links

    Description of Figure 9-6 follows
    Description of "Figure 9-6 Uploaded Files Report with Download Links"

  8. To test the links, click an ID.

    A File Download dialog box appears.

  9. Click Edit Page 1 on the Developer toolbar to return to the Page Definition.

Storing Additional Attributes About the Document

Next, you create another table to store additional information about the documents that are uploaded. In this exercise, you:

  • Add an item to the upload form to capture the information

  • Add a process to insert this information along with the name of the file

  • Alter the SQL Report of uploaded files to join to the table containing the additional information

Topics in this section include:

Create a Table to Store Document Attributes

First, you create a table in SQL Commands.

See Also:

"Using SQL Commands" in Oracle Database Application Express User's Guide

To create the table to store additional information about uploaded files:

  1. Go to SQL Commands:

    1. Click the Home breadcrumb link at the top of the page as shown in Figure 9-7.

      Figure 9-7 Breadcrumb Menu

      Description of Figure 9-7 follows
      Description of "Figure 9-7 Breadcrumb Menu"

      The Workspace home page appears.

    2. On the Workspace home page, click SQL Workshop and then SQL Commands.

      The SQL Commands page appears.

  2. In the top section, enter:

    CREATE TABLE oehr_file_subject 
   (name     VARCHAR2(4000) primary key, 
    subject  VARCHAR2(4000));

  3. Click Run.

    The message Table created appears in the Results section.

  4. Click the Home breadcrumb link.

    The Workspace home page appears.

Create an Item to Capture the Document Subject

To create an item to capture the subject of the document:

  1. Go to the Page Definition for page 1:

    1. On the Workspace home page, click the Application Builder icon.

    2. On the Application Builder home page, click Download App.

    3. On the Application home page, click the Page 1 icon.

    The Page Definition for Page 1 appears.

  2. Under Items, click the Create icon.

  3. For Item Type, select Text and click Next.

  4. For Text Control Display Type, select Text Field and click Next.

  5. For Display Position and Name:

    1. For Item Name - Enter P1_SUBJECT.

    2. Sequence - Accept the default.

    3. Region - Select Uploaded Files.

    4. Click Next.

  6. For Item Attributes:

    1. Label field - Enter Subject.

    2. Accept the remaining defaults.

    3. Click Next.

  7. Click Create Item.

Create a Process to Insert Information

Next, you need to create a process to insert the subject information into the new table.

To create a process:

  1. Under Page Processing, Processes, click the Create icon.

  2. For Process Type, select PL/SQL and then click Next.

  3. For Process Attributes:

    1. Name - Enter Insert file description.

    2. Sequence- Accept the default.

    3. From Point - Select On Submit - After Computations and Validations.

    4. Click Next.

  4. In Enter PL/SQL Page Process, enter the following:

    INSERT INTO oehr_file_subject(name, subject) VALUES(:P1_FILE_NAME,:P1_SUBJECT);

  5. Click Next.

  6. For Messages:

    1. Success Message - Enter:

      Subject inserted

    2. Failure Message - Enter:

      Error inserting subject

    3. Click Next.

  7. For Process Conditions:

    1. When Button Pressed - Select SUBMIT.

    2. Accept the remaining defaults and click Create Process.

Show Additional Attributes in the Report Region

Finally, you need to alter the SQL Report region to join it to the additional attributes table. To accomplish this, you edit the Region Source attribute on the Region Definition page.

To edit the Region Source:

  1. Under Regions, click Uploaded Files.

    The Region Definition appears.

  2. Scroll down to Source.

  3. Replace the Region Source with the following:

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

  4. Click Apply Changes.

  5. Run the page.

  6. Click Browse, locate a file to upload, and click Submit.

    As shown in Figure 9-8, the Uploaded Files report now contains a Subject column.

    Figure 9-8 Uploaded Files Report with Subject Column

    Description of Figure 9-8 follows
    Description of "Figure 9-8 Uploaded Files Report with Subject Column"

  7. Click Edit Page 1 on the Developer toolbar to return to the Page Definition.

Storing the Document in a Custom Table

In certain cases, you may want to store uploaded documents in a table owned by your schema. For example, if you want to create an Oracle Text index on uploaded documents, you need to store the documents in a custom table.

To store documents in your custom table:

  • Add a column of type BLOB to hold the document

  • Alter the process to insert documents into the custom table

To add a BLOB column to the oehr_file_subject table:

  1. Go to SQL Commands:

    1. Click the Home breadcrumb link at the top of the page.

      The Workspace home page appears.

    2. On the Workspace home page, click SQL Workshop and then SQL Commands.

      The SQL Commands page appears.

  2. In the top section, enter the following SQL statement:

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

  3. Click Run.

    The message Table Altered appears.

  4. Click the Home breadcrumb link at the top of the page.

To alter the process to insert documents into the oehr_file_subject table:

  1. On the Workspace home page, click Application Builder.

  2. Click Download App.

  3. Click Page 1.

  4. Under Processes, click the Insert file description link.

  5. Scroll down to Source.

  6. Under Source, replace the process with the following:

      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;

  7. Click Apply Changes.

Downloading Documents from the Custom Table

Now that documents are being stored in a custom table, you need to provide a way to download them. You do this by creating a procedure and granting execute on that procedure to the pseudo user APEX_PUBLIC_USER.

To accomplish this you need to change:

  • The SQL report region to no longer join to the APEX_APPLICATION_FILES view

  • The URL supplied for the ID column in the SQL report to execute the new procedure instead of executing the previous procedure

Topics in this section include:

Create a Procedure to Download Documents

To create a procedure to download documents from the oehr_file_subject table and grant execute to public:

  1. Go to SQL Commands:

    1. Click the Home breadcrumb link at the top of the page.

      The Workspace home page appears.

    2. On the Workspace home page, click SQL Workshop and then SQL Commands.

      The SQL Commands page appears.

  2. Enter the following SQL statement:

    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;
/

  3. Click Run.

    The message Procedure Created appears.

    Next, you want to run another SQL statement.

  4. Click the SQL Workshop breadcrumb link and then click SQL Commands.

    The SQL Commands page appears.

  5. In the top section, replace the existing SQL statement with the following:

    GRANT EXECUTE ON download_my_file TO PUBLIC/

  6. Click Run.

    The message Statement processed appears.

  7. Click the Home breadcrumb link at the top of the page to return to the Workspace home page.

Edit the Uploaded Files Region

To change the SQL report region to no longer join with the APEX_APPLICATION_FILES view:

  1. Go to the Page Definition of page 1:

    1. On the Workspace home page, click Application Builder.

    2. On the Application Builder home page, click Download App.

    3. On the Application home page, click Page 1.

  2. Under Regions, click Uploaded Files.

  3. Scroll down to Source.

  4. Replace the Region Source with the following:

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

  5. Click Apply Changes.

    The Page Definition appears.

Change the Download Link to Use the New Procedure

Next you need to change the download link to call the PL/SQL download_my_file procedure.

Note:

If you are using Application Express in Database 11g, instead of calling the PL/SQL procedure as described in this section, please follow the steps outlined in the following section Create Download Page for Embedded PL/SQL Gateway.

To change the download link to use the new download procedure:

  1. Under Regions, click Report next to Uploaded Files.

  2. In the ID row, click the Edit icon.

  3. Scroll down to the Column Link section.

  4. In the URL field, replace the existing URL with the following:

    #OWNER#.download_my_file?p_file=#ID#

    In this URL:

    • #OWNER# is the parsing schema of the current application.

    • download_my_file is the new procedure you just created.

    • You are passing in the value of the column ID to the parameter p_file.

  5. Click Apply Changes.

    The Page Definition appears.

Create Download Page for Embedded PL/SQL Gateway

The Oracle XML DB HTTP Server with the embedded PL/SQL Gateway is typically used for Application Express in Oracle Database 11g. Calling a PL/SQL procedure directly from a URL that is not known in a list of allowed procedures, as shown in Change the Download Link to Use the New Procedure, results in an error message.

To avoid this situation, there are a couple of available options. The first option is to modify the PL/SQL function WWV_FLOW_EPG_INCLUDE_MOD_LOCAL to include the PL/SQL download_my_file procedure and then recompile. The second, described below, is to create a page in the application that has a before header branch to the PL/SQL download_my_file procedure. You then create a hidden item on that page for the document ID of the document to be downloaded.

To accomplish the second option you need to:

  • Create a page with a before header branch to the PL/SQL procedure download_my_file

  • Change the download link to use the new page to display the file

To create a page with a before header branch to the PL/SQL procedure download_my_file:

  1. On the Application Home page, click Create Page.

  2. Select Blank Page and click Next.

  3. For Page Number, enter 2 and click Next.

  4. For Name, enter Download File and click Next.

  5. For Tabs, select No and click Next.

  6. Click Finish.

    The Success page appears.

  7. Click Edit Page icon.

    The Page Definition for page 2 appears.

  8. Under Regions, click the Create icon.

  9. For Region:

    1. Identify the type of region to add to this page - Accept the default, HTML, and click Next.

    2. Select the type of HTML region container you wish to create - Accept the default, HTML, and click Next.

  10. For Display Attributes, specify the following:

    1. For Title - Enter Display Document.

    2. Accept the remaining default values.

    3. Click Next.

  11. Click Create Region.

    The Page Definition for page 2 appears. A confirmation message displays at the top of the page: Region created.

  12. Under Items on the Page Definition for page 2, click Create icon.

  13. For Item Type, select Hidden and then click Next.

  14. For Hidden Item Type, select Hidden and Protected and then click Next.

  15. For Item Name, enter P2_DOC_ID and then click Next.

  16. For Source, accept all defaults and then click Create Item.

    The Page Definition for Page 2 appears.

  17. Under Branches, click the Create icon.

  18. For the Branch Point list, select On Load: Before Header.

  19. For the Branch Type, select Branch to PL/SQL Procedure and then click Next.

  20. For the Identify PL/SQL procedure to call text box, enter download_my_file(:P2_DOC_ID).

  21. For Branch Conditions, accept defaults and click Create Branch.

    The Page Definition for page 2 appears.

Change the download link to display to the Download Display page:

  1. Click the Page 1 icon.

    The Page Definition for page 1 appears.

  2. Under Regions, click Report next to Uploaded Files.

    The Report Attributes page appears.

  3. Under Column Attributes, click the Edit icon in the ID row.

  4. Scroll down to Column Link

  5. Under Column Link:

    1. Target - select Page in this Application

    2. Page - enter 2

    3. Item 1 Name - P2_DOC_ID

    4. Item 1 Value - #ID#

  6. Click Apply Changes.

  7. Run the application.

Security Issues to Consider

The application you built in this tutorial provides download links that invoke the procedure download_my_file. Note that this approach has security implications that you need to be aware of.

To invoke your procedure, a user can click the links you provide, or a user can enter similar URLs in the Web browser's Address (or Location) field. Be aware that a curious or malicious user could experiment with your download_my_file procedure, passing in any file ID as the p_file argument. A hacker could determine what file IDs exist in your table by legitimate or illicit means. Worse yet, in a mechanized attack, a hacker could submit successive IDs until an ID matches a file in your table at which time your procedure would download the file to the hacker.

The measures you take to protect your data from unauthorized access depend upon:

  • Your assessment of the degree of harm that would result if a hacker were able to download a file.

  • The likelihood of such an attack balanced against the cost and difficulty of providing controls.

One technique you can use to protect an application is to call one of the Oracle Application Express security APIs from within the procedure in order to ensure that the user has already been authenticated. For example, you could include a block of code into the procedure so that it runs first. Consider the following example:

-- Assuming your application's numeric ID is 100, set g_flow_id to
--     that value, otherwise change the value as required. 
--
APEX_APPLICATION.G_FLOW_ID := 100;

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;