As Published In January/February 2009

DEVELOPER: Browser-Based

Easy Application Attachments

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

• A MIME-type column ensures that the application correctly displays the file.
• A filename column provides the correct name when the file is being downloaded.
• A last-update column enables the application to cache the image until it is updated.

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.
2. Install the application in your local Oracle Application Express instance or in the public instance at apex.oracle.com. In Oracle Application Express (Home), select Application Builder -> Import -> Application , click Browse , select the asset_manager_installer_1.0.sql file from the downloaded application files, click Open , and then click Next to import the file.
3. Click Next and Install to install the application.
4. After the installation is complete, you will be prompted to install supporting objects. Select Yes , and click Next.

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.
2. Click Object Browser . A list of your existing tables appears.
3. Click Create.
4. Click Table.
5. Enter EBA_ASSET_ATTACHMENTS for Table Name , and set up the following columns:

• id. Select NUMBER as the type.
• asset_id. Select NUMBER as the type.
• description. Select VARCHAR2 as the type, and enter 100 as the scale value.
• content. Select BLOB as the type.
• mimetype. Select VARCHAR2 as the type, and enter 255 as the scale value.
• filename. Select VARCHAR2 as the type, and enter 255 as the scale value.
• last_update. Select DATE as the type, and then click Next.

6. Select Populated from a new sequence , select ID(NUMBER) for Primary Key , and click Next.
7. Select Cascade Delete , select ASSET_ID from the Select Key Column(s) list, click the right arrow (pointing to the Key Column(s) list), select EBA_ASSET_ASSETS from the References Table list, select ID from the Select Reference Column(s) list, click the right arrow, and click Add . Click Next.
8. Click Finish.
9. Click Create.

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.
2. Click Asset Manager 1.0.
3. Click Create Page.
4. Click Form , and click Next.
5. Click Form on a Table or View , and click Next.
6. Select the table/view owner, and click Next.
7. Select EBA_ASSET_ATTACHMENTS for Table/View Name , and click Next.
8. Change the page name to Maintain Attachments , change the region title to Maintain Attachment , select Breadcrumb from the Breadcrumb list, and click Next.
9. Click Use an existing tab set and reuse an existing tab within that tab set , and click Next.
10. For Use Tab , select T_ASSET_REPORT ; click Next.
11. Click Next.
12. For Source Type , select Existing Sequence ; for Sequence , select EBA_ASSET_ATTACHMENTS_SEQ ; and click Next.
13. In the Select Column(s) box, while depressing the Shift key, click ASSET_ID, DESCRIPTION, CONTENT , and MIMETYPE ; click Next.
14. Click Next.
15. Select 3 Asset Details (3) for both branches, and click Next.
16. Click Finish . A Success page allows you to run or edit the new page.
17. Click Edit Page . The new page should be page 16; if it is not, substitute your page number in subsequent steps.
18. Under Items, click P16_ASSET_ID , and under Name for Display As , select Hidden and Protected ; click the right arrow.
19. For P16_DESCRIPTION , under Element, change the Width value to 100; click the right arrow.
20. For P16_CONTENT , under Source, click BLOB Download Format Mask and enter and select values as follows:

BLOB column: CONTENT
MIME Type column: MIMETYPE
Filename column: FILENAME
BLOB Last Updated column: LAST_UPDATE
Content Disposition: Inline
Download Link Text: Download
Click Apply , and click the right arrow.

21. For P16_MIMETYPE , under Name for Display As , select Hidden ; click Apply Changes.
22. Click the create (region) icon (the rightmost button at the top of the Regions area).
23. Click Report , and click Next.
24. Click SQL Report , and click Next.
25. Enter Image for Title , and click Next.
26. Enter the following in the SQL Query box:

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.
28. In the Regions area, click Image and the Report Attributes tab. Change the headings type to Custom , and uncheck the Show check box for the ID column.
29. To remove the heading of the CONTENT column, click the Edit icon for the CONTENT column; in the Column Formatting section, enter

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.
2. Click the create (region) icon (the rightmost button at the top of the Regions area).
3. Click Report , and click Next.
4. Click SQL Report , and click Next.
5. Enter Attachments as the title, change the sequence to 20 , and click Next.
6. Enter the following in the SQL Query box:

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.

Conclusion

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.”
 

Send us your comments