Oracle HTML DB applications may include 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:
First, create a new application using the Create Application Wizard with the assumption you will include an upload form on page 2.
To create an application using the Create Application Wizard:
Navigate to the Workspace home page.
Click Create Application.
On Select Creation Method, select From Scratch and click Next.
For Name, enter Download App
and click Next.
For Tabs, select No Tabs and click Next.
For Page Name, accept the defaults and click Next.
For Theme, select Theme 2 and click Next.
Click Create Application.
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:
To create an HTML region:
Navigate to the Page Definition of page 2.
Under Regions, click the Create icon.
On Identify Region Type, select HTML and click Next.
On Region Type, select HTML and click Next.
For the Region Title, enter Submit File and click Next.
Accepting the remaining defaults and click Create Region.
To create a file upload item:
Navigate to the Page Definition of page 2.
Under Items, click the Create icon.
On Choose Item Type, select File Browse and click Next.
For Item Name, enter P2_FILE_NAME
and click Next.
Accept the remaining defaults and click Create Item.
To create a button:
Under Buttons, click the Create icon.
For the region, select Submit File 1.10 and click Next.
For Task, select Create a button in a region position and click Next.
On Identify Button Name and Label:
For Button Name, enter Submit
.
Accept the remaining defaults.
Click Next.
For Button template, accept the default and click Next.
On Display Properties, accept the defaults and click Next.
On Branching:
In Branch to Page, select page 2.
Click Create Button.
Run the page by clicking the Run Page icon.
When prompted for a user name and password:
For User Name, enter the name of your workspace
For Password, enter the password for your workspace
Click Login.
When you run the page, it should look similar Figure 8-1.
Once you create the form to upload documents, the next step is to create a report on the document table that contains links to download documents. When you use the file upload item type, 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 HTMLDB_APPLICATION_FILES
.
Topics in this section include:
To create a report on HTMLDB_APPLICATION_FILES
:
Navigate to the Page Definition of page 2.
Under Regions, click the Create icon.
On Identify Region Type, select Report and click Next.
From Report Implementation, select SQL Report and click Next.
For Region Title, enter Uploaded Files
and click Next.
On Page Region Source, enter the following SQL query:
SELECT id,name FROM HTMLDB_APPLICATION_FILES
Click Create Region.
Run the page.
As shown in Figure 8-2, the report you just created shows all documents that have been uploaded. Next, you provide a link to download the document.
To provide a link to download the documents in the report:
Navigate to the Page Definition of page 2.
Under Regions, click Q next to Uploaded Files.
Under Report Column Attributes, click the Edit icon next to the ID
column.
Scroll down to Column Link. Under Column Link:
In the Link Text field, enter:
download
From Target, select URL.
In the URL field, enter the following:
p?n=#ID#
Click Apply Changes.
When you run the page, it should look similar to Figure 8-3.
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:
To create the table to store additional information about uploaded files:
Navigate to Workspace home page.
Click SQL Workshop.
Click SQL Command Processor.
In the SQL Statement field, enter
CREATE TABLE file_subjects(name VARCHAR2(4000), subject VARCHAR2(4000) );
Click Run SQL.
To create an item to capture the subject of the document:
Navigate to the Page Definition of page 2.
Under Items, click the Create icon.
On Choose Item Type, select Text and click Next.
On Pick Text Control, select Text Field and click Next.
On Identify Item Name and Display Position:
For Item Name, enter P2_SUBJECT
.
From the Region list, select Uploaded Files.
Accepting the remaining defaults and click Next.
On Identify Item Attributes:
In the Label field, enter Subject
.
Accepting the remaining defaults.
Click Next.
Click Create Item.
To create a process to insert the subject information into the new table:
Navigate to the Page Definition of page 2
Under Page Processing, Processes, click the Create icon.
On Select Process Type, click PL/SQL.
On Identify Process Attributes:
For Name, enter Insert
.
From Point, select On Submit - After Computations and Validations.
For Type, select PL/SQL anonymous block.
Click Next.
On Identify Page Process, enter the following PL/SQL process:
INSERT INTO file_subjects(name, subject) VALUES(:P2_FILE_NAME,:P2_SUBJECT);
On Identify Messages:
In the Success Message field, enter:
Subject inserted
In the Failure Message field, enter:
Error inserting subject
Click Next.
From When Button Pressed, select SUBMIT.
Click Create Process.
To alter the SQL Report region to join to the additional attributes table:
Under Regions, select Uploaded Files.
Replace the Region Source with the following:
SELECT w.id,w.name,s.subject FROM HTMLDB_APPLICATION_FILES w,file_subjects s WHERE w.name = s.name
Click Apply Changes.
Run the page. As shown in Figure 8-4, the Uploaded Files report now contains a Subject column.
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 file_subjects
table:
Navigate to the Workspace home page.
Click SQL Workshop.
Click SQL Command Processor.
Enter the following SQL statement:
ALTER TABLE file_subjects ADD(id number,blob_content BLOB,mime_type varchar2(4000) );
Click Run SQL.
To alter the process to insert documents into the file_subjects
table:
Navigate to the Page Definition of page 2.
Under Page Processing, select Insert.
Under source, replace the process with the following:
IF ( :P2_FILE_NAME is not null ) THEN INSERT INTO file_subjects(id,NAME, SUBJECT, BLOB_CONTENT, MIME_TYPE) SELECT ID,:P2_FILE_NAME,:P2_SUBJECT,blob_content,mime_type FROM HTMLDB_APPLICATION_FILES WHERE name = :P2_FILE_NAME; DELETE from HTMLDB_APPLICATION_FILES WHERE name = :P2_FILE_NAME; END IF;
Click Apply Changes.
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 HTMLDB_PUBLIC_USER
.
To accomplish this you need to change:
The SQL report region to no longer join to the HTMLDB_APPLICATION_FILES
view
The URL supplied for the ID column in the SQL report to execute the new procedure instead of executing the p procedure
To create a procedure to download documents from the file_subjects
table and grant execute to public:
Navigate to the SQL Workshop.
Click SQL Command Processor.
In the SQL Statement field, enter:
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 file_subjects 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="'||substr(v_file_name,instr(v_file_name,'/')+1)|| '"'); -- close the headers owa_util.http_header_close; -- download the BLOB wpg_docload.download_file( Lob_loc ); end download_my_file; /
Click Run SQL.
In the SQL Statement field, enter:
GRANT EXECUTE ON download_my_file TO PUBLIC/
Click Run SQL.
To change the SQL report region to no longer join with the HTMLDB_APPLICATION_FILES
view:
Navigate to the Page Definition of page 2.
Under Regions, select Uploaded Files.
Replace the Region Source with the following:
SELECT s.id,s.name,s.subject FROM file_subjects s
Click Apply Changes.
To change the download link to use the new download procedure:
Navigate to the Page Definition of page 2.
Under Regions, click Q next to Uploaded Files.
Next to the ID column, click the Edit icon.
Scroll down to the Column Link region.
Replace existing URL with the following
#OWNER#.download_my_file?p_file=#ID#
Click Apply Changes.