9.9 Understanding 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.

Parent topic: Developing Reports

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

Parent topic: Understanding BLOB Support in Forms and Reports

9.9.2 About 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).
12	Table Owner	Yes	Identifies name of table owner containing target column of type BLOB.

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.

Parent topic: Understanding BLOB Support in Forms and Reports

9.9.3 About 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.
12	Table Owner	Yes	Identifies name of table owner containing target column of type BLOB.

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.

Parent topic: Understanding BLOB Support in Forms and Reports

9.9.4 About 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.