| Oracle® Database Express Edition 2 Day Plus Application Express Developer Guide Release 2.1 Part Number B25310-01 |
|
|
View PDF |
| Oracle® Database Express Edition 2 Day Plus Application Express Developer Guide Release 2.1 Part Number B25310-01 |
|
|
View PDF |
Oracle Application Express 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:
On the Database Home Page, click the Application Builder icon.
The Application Builder home page appears.
Click Create.
Select Create Application and click Next.
Specify the page name.
For Name, enter Download App.
For Application, accept the default.
For Create Application, select From scratch.
For Schema, accept the default.
Click Next.
Add a blank page:
Under Select Page Type, select Blank and click Add Page as shown in Figure 8-1.
The new page appears in the list at the top of the page.
Click Next.
For Tabs, accept the default, One Level of Tabs, and click Next.
For Copy Shared Components from Another Application, accept the default, No, and click Next.
For Attributes, accept the defaults for Authentication Scheme, Language, and User Language Preference Derived From and click Next.
For User Interface, select Theme 2 and click Next.
Review your selections and click Create.
The Application home page appears.
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:
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:
Navigate to the Page Definition for page 1 by clicking Page 1.
Under Regions, click the Create icon as shown in Figure 8-2.
For Region, select HTML and click Next.
Select a type of HTML region container. Select HTML again and click Next.
For Display Attributes:
In Title, enter Submit File.
Accept the remaining defaults and click Next.
Accept the remaining defaults and click Create Region.
The Page Definition appears.
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.
To create a file upload item:
On the Page Definition for page 1, locate the heading Items.
Under Items, click the Create icon.
For Item Type, select File Browse and click Next.
For Display Position and Name:
For Item Name, enter P1_FILE_NAME.
For Sequence, accept the default.
For Region, select Submit File.
Click Next.
Accept the remaining defaults and click Next.
Click Create Item.
The Page Definition appears.
Next, you need to create a button to submit the file.
To create a button:
Under Buttons, click the Create icon.
For Button Region, select Submit File (1) 1 and click Next.
For Button Position, select Create a button in a region position and click Next.
On Button Attributes:
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 1.
Click Create Button.
Run the page by clicking the Run Page icon as shown in Figure 8-3.
When prompted for a user name and password:
For User Name, enter the name of your database user account.
For Password, enter the password for your database user account.
Click Login.
When you run the page, it should look similar Figure 8-4.
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 database user account 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 for page 1. Click Edit Page 1 on the Developer toolbar.
Under Regions, click the Create icon.
For Region, select Report and click Next.
Specify the type of report. For Report Implementation, select SQL Report and click Next.
For Display Attributes:
In Title, enter Uploaded Files.
Accept the remaining defaults and click Next.
For Source, enter the following SQL query:
SELECT id,name FROM HTMLDB_APPLICATION_FILES
Click Create Region.
Run the page by clicking the Run Page icon.
As shown in Figure 8-5, the report you just created shows all documents that have been uploaded.
Next, you need to provide a link to download each document.
To provide a link to download the documents in the report:
Navigate to the Page Definition for page 1. Click Edit Page 1 on the Developer toolbar.
Under Regions, click Report adjacent to Uploaded Files as shown in Figure 8-6.
The Report Attributes page appears. You can add a link to the ID column by editing Column Attributes.
Under 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#
#ID# passes the value contained in the column where ID is the column alias.
Scroll back to the top of the page and click Apply Changes.
The Page Definition appears.
Run the page by clicking the Run Page icon.
When you run the page, it should look similar to Figure 8-7.
Figure 8-7 Uploaded Files Report with Download Links
Click Edit Page 1 on the Developer toolbar to return to the Page Definition.
Return to the Database Home Page. Click the Home breadcrumb link at the top of the page as shown in Figure 8-8.
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:
First, you create a table in SQL Commands.
See Also:
"Using SQL Commands" in Oracle Database Express Edition Application Express User's Guide.To create the table to store additional information about uploaded files:
On the Database Home Page, click the SQL icon.
Click SQL Commands.
In the SQL Commands, enter:
CREATE TABLE file_subjects(name VARCHAR2(4000), subject VARCHAR2(4000) );
Click Run.
The message Table created appears.
Click the Home breadcrumb link.
The Database Home Page appears.
To create an item to capture the subject of the document:
Navigate to the Page Definition for page 1:
On the Database Home Page, click the Application Builder icon.
The Application Builder home page appears.
Select Download App.
The Application home page appears.
Select Page 1.
The Page Definition for Page 1 appears.
Under Items, click the Create icon.
For Item Type, select Text and click Next.
For Text Control Display Type, select Text Field and click Next.
For Display Position and Name:
For Item Name, enter P1_SUBJECT.
For Sequence, accept the default.
From Region, select Uploaded Files.
Click Next.
For Item Attributes:
In the Label field, enter Subject.
Accept the remaining defaults.
Click Next.
Click Create Item.
Next, you need to create a process to insert the subject information into the new table.
To create a process:
Under Page Processing, Processes, click the Create icon as shown in Figure 8-9.
For Process Type, select PL/SQL and click Next.
For Process Attributes:
For Name, enter Insert.
For Sequence, accept the default.
From Point, select On Submit - After Computations and Validations.
Click Next.
In Enter PL/SQL Page Process, enter the following:
INSERT INTO file_subjects(name, subject) VALUES(:P1_FILE_NAME,:P1_SUBJECT);
Click Next.
For Messages:
In Success Message, enter:
Subject inserted
In Failure Message enter:
Error inserting subject
Click Next.
For Process Conditions:
From When Button Pressed, select SUBMIT.
Accept the remaining defaults and click Create Process.
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:
Under Regions, click Uploaded Files as shown in Figure 8-10.
The Region Definition appears.
Scroll down to Source.
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
Scroll up to the top of the page and click Apply Changes.
Run the page by clicking the Run Page icon.
As shown in Figure 8-11, the Uploaded Files report now contains a Subject column.
Figure 8-11 Uploaded Files Report with Subject Column
If your Uploaded Files report does not initially contain all three columns, try uploading a file and clicking the Submit button.
Return to the Page Definition by clicking Edit Page 1 on the Developer toolbar.
Return to the Database Home Page. Click the Home breadcrumb link at the top of the page.
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:
On to the Database Home Page, click the SQL icon.
Click SQL Commands.
SQL Commands appears.
Enter the following SQL statement:
ALTER TABLE file_subjects ADD(id number,blob_content BLOB,mime_type varchar2(4000) );
Click Run.
The message Table Altered appears.
Return to the Database Home Page. Click the Home breadcrumb link at the top of the page.
To alter the process to insert documents into the file_subjects table:
On the Database Home Page, click the Application Builder icon.
Select Download App.
Select Page 1.
Under Processes, select Insert.
Scroll down to Source.
Under Source, replace the process with the following:
IF ( :P1_FILE_NAME is not null ) THEN
INSERT INTO file_subjects(id,NAME, SUBJECT, BLOB_CONTENT, MIME_TYPE)
SELECT ID,:P1_FILE_NAME,:P1_SUBJECT,blob_content,mime_type
FROM HTMLDB_APPLICATION_FILES WHERE name = :P1_FILE_NAME;
WHERE name = :P1_FILE_NAME;
DELETE from HTMLDB_APPLICATION_FILES WHERE name = :P1_FILE_NAME;
END IF;
Scroll up to the top of the page and click Apply Changes.
Return to the Database Home Page. Click the Home breadcrumb link at the top of the page.
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:
On the Database Home Page, click the SQL icon.
Click SQL Commands.
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 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.
The message Procedure Created appears. Run another SQL statement.
Click SQL breadcrumb link and then SQL Commands.
SQL Commands appears.
Enter the following SQL statement:
GRANT EXECUTE ON download_my_file TO PUBLIC/
Click Run.
The message Statement processed appears.
Return to the Database Home Page. Click the Home breadcrumb link at the top of the page.
To change the SQL report region to no longer join with the HTMLDB_APPLICATION_FILES view:
Navigate to the Page Definition of page 1:
On the Database Home Page, click the Application Builder icon.
The Application Builder home page appears.
Select Download App.
The Application home page appears.
Select Page 1.
Under Regions, select Uploaded Files.
Scroll down to Source.
Replace the Region Source with the following:
SELECT s.id,s.name,s.subject FROM file_subjects s
Scroll up to the top of the page and click Apply Changes.
The Page Definition appears.
To change the download link to use the new download procedure:
Under Regions, click Report adjacent to Uploaded Files.
Next to the ID column, click the Edit icon.
Scroll down to the Column Link.
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.
Scroll up to the top of the page and click Apply Changes.
