Starting with release 3.1, Oracle Application Express includes declarative BLOB support to enable developers to declaratively uploaded files in forms, and downloaded 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 in this section include:
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 items whose source is a database column of type BLOB will result in an item of type File Browse. When the form is called for INSERT, the file selected by the user will be 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.
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 will provide a better experience for the end-user. To facilitate managing this additional information, the 'Source' attribute of an item has been extended to have additional components (in addition to DB_COLUMN):
| Position | Attribute | Required | Description |
|---|---|---|---|
| 1 | Column containing BLOB |
Yes | Case sensitive name of column of type BLOB. |
| 2 | MIME type Column | No | Case sensitive column name used to store the MIME type. |
| 3 | 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 user downloads the file. |
| 4 | 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 will indicate the date of last modification and browsers will be able to cache the BLOB. If not specified, the browser may not be able to cache files. |
| 5 | 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. |
| 6 | Content Disposition | No | Specify inline or attachment. All other values ignored. attachment is the default, inline can only be respected if a MIME type is specified. |
| 7 | Download Text | No | String used for the download link. If nothing is provided, Download will be used. Note this will support substitutions (useful for translated applications). |
Consider the following example:
RESUME:RESUME_MIMETYPE:RESUME_FILENAME:RESUME_LAST_UPDATE::attachment:Resume
If you have an item of type File Browse, whose source is Database Column that contains at least one colon (for example, RESUME:), you will see a link below the Source BLOB Download Format Mask. This popup assists in entering all the parameters necessary for the BLOB format.
At a minimum, you must specify the database BLOB column with at least one trailing colon (for example, RESUME:). This preserves compatibility with Oracle Application Express release 3.0.1.
Tip:
To see a real example, go to Sample Application, page 6 (Add/Modify Products), and review the item P6_PRODUCT_IMAGE. See "Running a Demonstration Application".To provide this additional information, Oracle recommends you add additional columns to your base table to store and track the MIME type and file name attributes. You can accomplish this simply by extending your table, for example:
ALTER TABLE emp ADD
(ATTACH_MIMETYPE VARCHAR2(255),
ATTACH_FILENAME VARCHAR2(255),
ATTACH_LAST_UPDATE DATE);
If you manually create a form, you can still take advantage of this feature. You simply use the format described within an item of type File (File Browse) on a page with a DML Process of type DML_PROCESS_ROW. This process will determine the table name and primary key columns.
If the BLOB you are working with is an image, you can display it in the form as well. See "Working With BLOBs Procedurally".
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;
Tip:
To see a real example, go to Sample Application, page 6 (Add/Modify Products), Delete Image process. See "Running a Demonstration Application".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 will be included. Additional information should be added after generation to make the download capability more user friendly.
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 parameters of the DOWNLOAD format are described in the following table:
| 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 ignored. If a MIME type is provided and the file is a type that can be displayed, the file will be displayed. If MIME type is not provided, or the file cannot be displayed inline, the user will be prompted to download. |
| 11 | Download Text | No | String used for the download link. If nothing provided, Download is used. Note that this will support substitutions (useful for translated applications). |
Consider the following example:
DOWNLOAD:EMP:RESUME:EMPNO::RESUME_MIMETYPE:RESUME_FILENAME:RESUME_LAST_UPDATE::attachment:Resume
If you have a report column with a format mask that begins with DOWNLOAD:, you will see a link below the format 'BLOB Download Format Mask'. This popup assists in entering all the parameters necessary for the DOWNLOAD format.
Tip:
To see a real example, go to Sample Application, page 29 (Master Detail), Items for Order #&P29_ORDER_ID. region, Product Image column. See "Running a Demonstration Application".If the BLOB you are working with is an image, you can display it in the report as well. To do this, you use the new report format mask of 'IMAGE'. Regardless of the MIME type, the report will always attempt to display the BLOB. If the BLOB cannot be rendered, a broken image will be displayed.
The parameters of the IMAGE format mask are described in the following table:
| 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
If you have a report column with a format mask that begins with 'IMAGE:', you will see a link below the format 'BLOB Download Format Mask'. This popup assists in entering all the parameters necessary for the IMAGE format.
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). Please 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 Oracle Application Express API Reference