Easy Application AttachmentsBy David Peake
Incorporate BLOB support in Oracle Application Express.
Do you have an existing Oracle Application Express application to which you want to add attachments such as Microsoft Word documents, presentation files, or images? Oracle Application Express 3.1 makes it simple to add attachment support and manage binary large objects (BLOBs) within the database.
Including one column of type BLOB allows a file to be stored in a database table, but with this single column, it may be difficult to effectively manage files being uploaded and downloaded and to display them correctly in the application. Therefore, creating a new table in the application with a BLOB column and additional columns that support BLOBs is valuable because
This article provides the steps required to modify an existing Oracle Application Express application to store BLOBs, using a new table to store both the BLOBs and additional information. You can run through the steps on a local instance or on the hosted instance of Oracle Application Express, at apex.oracle.com. (You must request a free workspace to use this hosted instance.)
Preliminary Application Loading
Let’s start by downloading and installing the asset manager packaged application.
1. Download the asset manager application from oracle.com/technetwork/products/database/application_express/packaged_apps/asset_manager_1.0.zip, and unzip it.
Creating the Attachments Table
Using SQL Workshop, we now add a new table to hold the files (in the BLOB column) and additional information.
1. In Oracle Application Express (Home), click SQL Workshop.
6. Select Populated from a new sequence , select ID(NUMBER) for Primary Key , and click Next.
Maintaining the Attachments
The next step is to build a new application page to maintain the file attachments. We create two regions: the first is for inserting, updating, and deleting attachments, and the second will be visible only if an attachment is an image (and the region will show the image).
1. In Oracle Application Express (Home), click Application Builder.
BLOB column: CONTENT
21. For P16_MIMETYPE , under Name for Display As , select Hidden ; click Apply Changes.
SELECT ID, DBMS_LOB.GETLENGTH(CONTENT) Content FROM EBA_ASSET_ATTACHMENTS WHERE ID = :P16_ID
and then click Next twice.
27. For Condition Type , select PL/SQL Expression ; enter INSTR(:P16_MIMETYPE, 'image') = 1 in the Expression 1 box; and click Create Region.
IMAGE:EBA_ASSET_ATTACHMENTS: CONTENT:ID::MIMETYPE:FILENAME: LAST_UPDATE::inline:Download
in the Number/Date Format box; and click Apply Changes twice.
Integrating Attachments into the Report Page
The last step is to integrate a new region into the Asset Details page to display the attachments associated with a specific asset and call the new page for maintaining the attachments.
1. Navigate to page 3, Asset Details.
SELECT ID, FILENAME, LAST_UPDATE, DESCRIPTION, DBMS_LOB.GETLENGTH(CONTENT) Content FROM EBA_ASSET_ATTACHMENTS WHERE ASSET_ID = :P3_ID
and then click Create Region.
7. Click Attachments in the Regions area, and click the Report Attributes tab. Click the Edit icon for the ID column, navigate to the Column Link area, click [Icon 2] to set the Link Text value, enter 16 in the Page box, enter 16 in the Clear Cache box, enter P16_ID for Item 1 Name and #ID# for Item 1 Value , and click Apply Changes.
8. Click the Edit icon for the CONTENT column. Under Column Formatting, enter
DOWNLOAD:EBA_ASSET_ATTACHMENTS: CONTENT:ID::MIMETYPE:FILENAME: LAST_UPDATE::inline:Download
in the Number/Date Format box, and click Apply Changes twice.
10. Select Attachments (1) 20 , and then click Next twice.
11. Enter NEW_ATTACHMENT for both Button Name and Label , select the Action option Redirect to URL without submitting page , and click Next twice.
12. Select Region Template Position #EDIT# for Position , and click Next.
13. Enter 16 as the Page value, 16 as the Clear Cache value, P16_ASSET_ID as the Set these items value, and &P3_ID for With these values , and click Create Button.
14. Navigate back to page 1, and run the application.
Oracle Application Express makes it easy to maintain files as BLOBs in the database. The example in this column demonstrated how you can manage multiple attachments for an existing table by implementing a new related table.
Note that if you are running Oracle Application Express with Oracle Database 11g, you can also take full advantage of Oracle SecureFiles capabilities to compress, deduplicate, and encrypt BLOBs within the database.
Finally, for more information on BLOB support in Oracle Application Express, refer to “About BLOB Support in Forms and Reports” in Oracle Application Express Application Builder User’s Guide , chapter 15, “Advanced Programming Techniques.”
David Peake (firstname.lastname@example.org) is a principal product manager in Oracle’s Server Technologies division. He has been with Oracle since 1993.