Skip Headers
Oracle® Application Express Application Builder User's Guide
Release 4.2

E35125-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to current chapter
Up
Go to next page
Next
PDF · Mobi · ePub

17.10 About BLOB Support in Forms and Reports

Oracle Application Express includes declarative BLOB support to enable developers to declaratively upload files in forms, and download or display files in reports. BLOB display and download can also be authored procedurally using PL/SQL. This section describes how to upload, download and display files, and how to manage additional file attributes such as MIME type and file name that are important for proper management of files stored in BLOB columns. Using this functionality you can easily extend your Oracle Application Express applications to manage files including images, documents, videos, and so on.

Topics:

17.10.1 About BLOB in Forms

If you create a Form (either using the Create Application Wizard, create page of type Form - or Report and Form, or create region of type Form) or add an item to an existing form, any item whose source is a database column of type BLOB results in an item of type File Browse. When the form is called for INSERT, the file selected by the user is loaded into the BLOB column. When the form is called for update, a download link is displayed to the right of the Browse button. Users can use this link to download the file.

17.10.1.1 Uploading and Downloading Files into a BLOB

The defaulted BLOB support does not give you all the information a typical application needs to effectively manage a BLOB. In addition to knowing that the column is a BLOB, more information about the file provides a better experience for the end-user. The File Browse page item has additional settings to facilitate managing this additional information completely declaratively.

See Also:

File Browse in Appendix A, "About Item Types", item level help for the File Browse settings

There are two different types of storage types available within the File Browse item type:

  • BLOB column specific in Item Source Attribute - Completely declarative approach that supports configuration of the additional settings discussed here. This references a BLOB in your own database table.

  • Table WWV_FLOW_FILES - Available for backwards compatibility. Do not use it in new applications.

To provide this additional information, it is recommended that you add additional columns to your base table to store and track the MIME type, file name, last updated date and character set settings. You can accomplish this by extending your table. For example:

ALTER TABLE emp ADD 
   (ATTACH_MIMETYPE     VARCHAR2(255),
    ATTACH_FILENAME     VARCHAR2(255),
    ATTACH_LAST_UPDATE  DATE,
    ATTACH_CHARSET      VARCHAR2(128));

Note:

The character set of the BLOB is not automatically set on upload. To store the character set value for your BLOB, you must provide an additional page item on your page which is bound to the column you use to store the character set, and where the user will be able to specify the character set for the document they are uploading.

If you manually create a form on a custom table, you can still take advantage of this feature. To do so, use the File Browse item type with a Storage Type setting of BLOB column specified in Item Source Attribute, on a page with a DML Process type of DML_PROCESS_ROW. This process determines the table name and primary key columns.

17.10.1.2 Displaying the BLOB

If the BLOB you are working with is an image, you can display it in the form as well. You can use the Display Image item type to handle this declaratively, see Appendix A, "About Item Types" for details. See "Working With BLOBs Procedurally" to handle procedurally.

17.10.1.3 Removing the Image Reference

Because there is no set to NULL when using File Browse, if you need to provide a mechanism to remove an image reference, you must include a special Remove Image button to nullify the necessary columns. Consider the following example:

UPDATE demo_product_info
   SET product_image = NULL,
       MIMETYPE = NULL,
       FILENAME = NULL,
       IMAGE_LAST_UPDATE = NULL,
       CHARSET = NULL
 WHERE product_id = :P6_PRODUCT_ID;

17.10.2 About BLOB Support in Reports

Oracle Application Express includes BLOB support for both classic and interactive reports. If you use a wizard to create a report and include a column of type BLOB, basic support is included. Additional information should be added after generation to make the download capability more user friendly.

17.10.2.1 Providing a Download Link

To facilitate the inclusion of a download link in a report, the report includes the selection of the length of the BLOB (for example, dbms_lob.getlength(RESUME)). If the length is 0, the BLOB is NULL and no download link is displayed. In the same way you specify a format mask for a date or number you can format a download link. The DOWNLOAD format is more complex however then other format masks in that you are required to specify at least three parameters, for example:

DOWNLOAD:EMP:RESUME:EMPNO

The following table describes the parameters of the DOWNLOAD format:

Position Attribute Required Description
1 DOWNLOAD Yes Identifies the DOWNLOAD report format mask.
2 Table Name Yes Case sensitive name of table containing target column of type BLOB.
3 Column containing BLOB Yes Case sensitive name of column of type BLOB.
4 Primary Key Column 1 Yes Case sensitive name of primary key column 1.
5 Primary Key Column 2 No Case sensitive name of primary key column 2.
6 MIME type Column No Case sensitive column name used to store the MIME type.
7 Filename Column No Case sensitive column name used to store the filename of the BLOB. If NULL, the column name is used as the default when a user downloads the file.
8 Last Update Column No Case sensitive column name used to store the last update date of the BLOB. If used, the HTTP header of the file download indicates the date of last modification and web browsers will be able to cache the BLOB. If not specified, the browser may not be able to cache files.
9 Character Set Column No Case sensitive column name used to store the character set of the BLOB. Most relevant for Asian languages which may need to maintain the character set encoding.
10 Content Disposition No Specify inline or attachment. All other values are ignored. If a MIME type is provided and the file is a type that can be displayed, the file is displayed. If MIME type is not provided, or the file cannot be displayed inline, the user is prompted to download.
11 Download Text No String used for the download link. If nothing is provided, Download is used. Note that this supports substitutions (useful for translated applications).

Consider the following example:

DOWNLOAD:EMP:RESUME:EMPNO::RESUME_MIMETYPE:RESUME_FILENAME:RESUME_LAST_UPDATE::attachment:Resume

To be able to enter these parameters and create the format as described above. You have to select Blob Format for Number/Date format item. Once selected, the Blob Column attributes region displays and you can fill in all parameters as described above.

17.10.2.2 Displaying the BLOB

If the BLOB you are working with is an image, you can display it in the report using the new report format mask of 'IMAGE'. Regardless of the MIME type, the report always attempts to display the BLOB. If the BLOB cannot be rendered, a broken image is displayed.

The following table describes the parameters of the IMAGE format mask:

Position Attribute Required Description
1 IMAGE Yes Identifies the IMAGE report format mask.
2 Table Name Yes Case sensitive name of table containing target column of type BLOB.
3 Column containing BLOB Yes Case sensitive name of column of type BLOB.
4 Primary Key Column 1 Yes Case sensitive name of primary key column 1.
5 Primary Key Column 2 No Case sensitive name of primary key column 2.
6 MIME type Column No Case sensitive column name used to store the MIME type.
7 Filename Column No Not used for IMAGE format but left in so that the format can easily be changed between IMAGE and DOWNLOAD.
8 Last Update Column No Case sensitive column name used to store the last update date of the BLOB. If used, the HTTP header of the file download indicates the date of last modification and web browsers will be able to cache the BLOB. If not specified, the browser may not be able to cache files.
9 Character Set Column No Not used for IMAGE format but left in so that the format can easily be changed between IMAGE and DOWNLOAD.
10 Content Disposition No Not used for IMAGE format but left in so that the format can easily be changed between IMAGE and DOWNLOAD.
11 Alt Text No String used for the alt tag associated with the image.

Consider the following example:

IMAGE:EMP:RESUME:EMPNO::RESUME_MIMETYPE:RESUME_FILENAME:RESUME_LAST_UPDATE::attachment:Resume

To be able to enter these parameters and create the format as described above. You have to select Blob Format for Number/Date format item. Once selected, a new region, Blob Column attributes, displays and you can fill in all parameters as described above.

17.10.3 Working With BLOBs Procedurally

As an alternative to using the built-in methods of providing a download link, you can use the APEX_UTIL.GET_BLOB_FILE_SRC function. One advantage of this approach, is the ability to more specifically format the display of the image (with height and width tags). Note that this approach is only valid if called from a valid Oracle Application Express session. Also, this method requires that the parameters that describe the BLOB to be listed as the format of a valid item within the application. That item is then referenced by the function.

See Also:

"GET_BLOB_FILE_SRC Function" in the Oracle Application Express API Reference