Sometimes, it is very handy to be able to upload and download files in a web application. For example, an intranet site where users can upload their own images, or a product catalog with images of the products. We will make a webpage where users can upload and download important documents such as templates of letters, fax sheets, telephone directories, and so on.
Make sure you have access to the APP_DOCUMENTS table. Also make sure the directory parameter in the call to bfilename points to an existing directory and that at least read access is granted. To create a directory with read access, execute the following commands (under the sys or system user) in sqlplus:
Create directory <<directory>> as 'c:documents'; Grant read on directory <<directory>> to <<user>>; [1346_03_17.txt]
Here,<<directory>> is the name of the directory you want to create in the database. This is a virtual directory and you can give it any name.<<user>> is the name of the Oracle user the read rights should be granted to. You can also use public if you want all users to be able to read from that directory.
- In the application builder, go to your application and click the create page button.
- Select Report and then select Classic report.
- Enter a page name for the report, for example, Documents.
- Select Do not use tabs and click Next.
- In the PL/SQL text area, enter the following query:
select id , description , filename , dbms_lob.getlength("DOC_CONTENT") doc_content from app_documents [1346_03_18.txt] - Click Next. In the next step, click Next.
- Click Finish to confirm the creation of the report.
The report is ready but you have to do one more thing.
Click the edit icon.
- In the regions section, click on the Edit report columns link.
- In the column attributes section, click on the edit icon right before the column named
doc_content. - In the column formatting section, enter the following in the number/date format text field:
DOWNLOAD:APP_DOCUMENTS:DOC_CONTENT:ID: [1346_03_19.txt]
- The format mask must be filled with parameters in the following order and separated by colons :
DOWNLOAD:<TABLE_NAME>:<BLOB COLUMN NAME>:<PRIMARY KEY COLUMN NAME>: [1346_03_20.txt]
- Alternatively, you can use the Blob download format mask link below the number/date format text field.
- Click the Apply changes button. The page is ready now. Run the page to test the download.
So far, we have seen the download page. To make an upload page, follow these steps:
- In the application builder, go to your application and click the Create page button.
- Select Form.
- Select Form on a table or view.
- Click Next.
- In the table/view name text field, enter the name of the table. In our case, enter here APP_DOCUMENTS.
- In the next step, enter a page name and a region name. Click Next.
- Select Do not use tabs and click Next.
- Select the primary key column of APP_DOCUMENTS, ID and click next.
- Select existing sequence and in the sequence list box select the sequence DCM_SEQ.
- Click Next.
- In the select columns list box, select DESCRIPTIONS, DOC_CONTENT, and FILENAME. Click Next.
- In the next step, leave the options as they are and click Next.
- Enter the IDs of the pages APEX should navigate to when submitting or canceling. Click Next.
- Click Finish to confirm. The page is almost ready now. Click the Edit icon.
- In the items section, click the doc_content item.
- In the source section, enter the following in the source value text area:
DOC_CONTENT:MIMETYPE:FILENAME:CONTENT_LAST_UPDATED:: [1346_03_21.txt]
- The parameters represent the blob column, the column containing the mimetype (this indicates what type of file has been uploaded, for example, image or wordpad), the column containing the filename, and the column containing the date last updated.
By the way, you can also use the Blob download format mask link below the text area to enter the source value.
By the way, you can also use the Blob download format mask link below the text area to enter the source value.
Click the Apply changes button. The page is ready now. Run the page to see the result.
You just created an upload page and a page where you can download files. As you can see in the upload page, the blob column is represented by an item of type file browse. At runtime, this field gets a button which shows a file dialog on clicking. If the record already contains an uploaded file, you will also see a download link which makes it possible to download the file.
In the report page with the download link, there are two important things. First, the column in the query. For the blob column, the function dbms_lob.getlength() is used. Second, the column formatting needs a special type of formatting. These two things make APEX put a download link on the screen.
We discussed the uploading and downloading of files that are stored in a blob in a table. You can also download files from the filesystem on the server.
Create a page with a HTML region with nothing in it.
In that page, create an on load-before header page process of type PL/SQL and include the following code:
declare
l_name varchar2(100) := 'testdocument.doc';
l_filename bfile;
v_length number(8);
begin
l_filename := bfilename('DIR',l_name);
v_length := dbms_lob.getlength(l_filename);
--
begin
owa_util.mime_header('application/octet',false);
sys.htp.p ('Content-length: ' || v_length );
sys.htp.p('Content-Disposition: attachment; filename="'||l_name||'"'),
owa_util.http_header_close;
wpg_docload.download_file(l_filename);