Oracle Database Tips by Donald Burleson

Uploading and Downloading Documents in APEX

Loading documents is very similar to loading images.  Anyone who has ever surfed the Internet has been to a web site and downloaded a file.  The online code depot for this book is a good example.  This section describes the steps necessary to provide a document storage system in the database and two methods of providing download links in application pages.

The authors built an application to serve as the document storage repository for each of the chapters of the book, and it also served as a version control system.  Since everything is stored in Oracle, there was an added benefit of nightly backups to insure none of their work was lost.

Up to this point, the f?p= syntax has been present on the URL which is used when accessing pages in APEX.  This is an indication of the format used to call stored procedures in APEX.  Actually, it is the syntax used by the modplsql module.  The F part of f?p=is a stored procedure in the FLOWS_020000 schema of APEX.  So, every page rendered in APEX is done by calling the f procedure and passing values into the P parameter.  For downloading files, the P procedure is used.  It then changes the URL to include p?n=.  The Pprocedure receives the N parameter which is the value of the ID column in WWV_FLOW_FILES.  Using this syntax in the URL will allow the file to be downloaded from WWV_FLOW_FILES.

This lesson will create a single page used to upload and download documents.  It is a simplified version of what was used when writing the book.  We will create a region for uploading the document and a report to show the documents available for download.  We will also create a table named EASY_DOCUMENT so the name of the document and an abstract of what the document is about can be stored.  The EASY_DOCUMENT table is also used to join to the WWV_FLOW_FILES view to limit the records returned from WWV_FLOW_FILES.  Otherwise we would see a lot of records in the report that had nothing to do with documents.

Create the EASY_DOCUMENT table by running the following script in a SQL Command window.

Create the Document Upload Page

From the Easy HTML DB Book application home page click on the Create Page button.

1.       Choose the Blank Page option and click Next.

2.       On the Page Attributes page, enter 1092 for the Page and click Next.

3.       On the Page Name page, enter Easy Documents for both Name and Title and click Next.

4.       Tabs: NO, then click Next.

5.       Click Finish.

When the Success page is displayed, click on the Edit Page so items can be added to the new page.

1.       Click on the  icon in the Regions area.

2.       Choose HTML and click Next.

3.       For Title, enter Easy Document Upload and click Next.

4.       Click the Create Region button.

5.       Back on the Page Definition page, click the  icon in the Buttons area.

6.       On the Button Region page, choose the Easy Document Upload option and click Next.

7.       Choose Create a button in a region position and click Next.

8.       On the Button Attributes page, enter UPLOAD for the Button Name and click Next.

9.       On the Image Attributes page, click Next.

10.    On the Display Properties page, click Next.

11.    For Branch to Page enter &APP_PAGE_ID. complete with the ending period and click Create Button.

12.    Back on the Page Definition page, click the  icon in the Items area.

13.    Choose the File Browse option and click Next.

14.    For Item Name enter P1092_NAME and click Next.

15.    On the Item Attributes page:

·        Label: Document Name

·        Field Width: 80

·        Click Next.

16.    On the next page, click Create Item.

17.    Back on the Page Attributes page, click the  icon in the Items area.

18.    Choose the Text Area option and click Next.

19.    For Item Type, choose Textarea w/Character Counter and click Next.

20.    For Item Name, enter P1092_ABSTRACT and click Next.

21.    On the Item Attributes page:

·        Label: Abstract

·        Label Alignment: Top right

·        Field Width: 80

·        Click Next.

22.    Click the Create Item button.

Run the application page as it is now.  With the page running, notice the numbers below the Abstract text area (0 of 2000).  This is called the character counter.  It reads 2000 because the maximum length of the text area is set to 2000 by default.  This must be changed to 250 to prevent too many characters from being entered and causing SQL errors when trying to perform the insert.  The following example includes another way to edit items without too much navigation between pages.

While the application page is running, click on the Show Edit Linkslink in the developer toolbar.  A bunch of little icons will show up in the Easy Document Upload region.  Figure 9.2 shows this.  Click on the Edit link below the 0 - 2000 character counter, as shown in Figure 9.2.

Clicking on the Edit link as shown will display a popup window.  When the popup window displays, locate the maxWidth page item in the Element region and change it to a value of 250.  Then, click the Apply Changes button.  When the changes have been processed, close the popup window.

Now that you are back on the running application page, click on the Hide Edit Links link in the developer toolbar.  Refresh the application page.  This is accomplished in Internet Explorer by pressing the F5 key.  When the page refreshes, the character counter will change to 0 - 250.

The process created in the following example will insert information into the EASY_DOCUMENT table when a document is uploaded.  Click on the Edit Page 1092 link in the developer toolbar.

1.       Click on the  icon in the Processes area of the Page Processing region.

2.       Choose the PL/SQL option and click Next.

3.       On the Process Attributes, enter Insert Document for the Name and click Next.

4.       On the Process page, enter the following for the text and click Next.

5.       On the Messages page:

·        Success Message: Document successfully loaded.

·        Failure Message: Document successfully loaded.

·        Click Next.

7.       On the Process Conditions page:

·        When Button Pressed: Select the UPLOAD item.

·        Click Create Process.

Now, it is time to upload a document.  Run the application page.

Click on the Browse button and navigate to the online code depot.  Select the file name EasyHTMLDB.pdf.  Enter "A sample upload document" for the abstract text area, and click the Upload button.  The success message will display once the document has successfully loaded.

Now that a document has been loaded, create a report to show the documents available in the repository.

The above book excerpt is from:

Easy HTML-DB Oracle Application Express

Create Dynamic Web Pages with OAE

ISBN 0-9761573-1-4   

Michael Cunningham & Kent Crotty

http://www.rampant-books.com/book_2005_2_html_db.htm