Understanding BLOB Support in Forms and Reports

8.11 Understanding BLOB Support in Forms and Reports

Oracle APEX 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 APEX applications to manage files including images, documents, videos, and so on.

About BLOB Support in Reports
Oracle APEX includes BLOB support for both classic and interactive reports.
Including a Downoad Link
Include a download link in a report by adding columns to your report query.
Displaying a BLOB Image in a Report
Display a BLOB image in a report by adding columns to your report query.
About Working With BLOBs Procedurally
As an alternative to using the built-in methods of providing a download link, use the APEX_UTIL.GET_BLOB_FILE_SRC function.

Parent topic: Developing Reports

8.11.1 About BLOB Support in Reports

Oracle APEX 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, this column will not be included in the report definition. To include a BLOB download link, additional attributes have to be configured after generation to provide the download capability.

Parent topic: Understanding BLOB Support in Forms and Reports

8.11.2 Including a Downoad Link

Include a download link in a report by adding columns to your report query.

By default, BLOB columns do not display in a report. Reports that include a BLOB column will display the text [unsupported data type] for the column. To include a download link in a report, you need to add a number column to your report query and select the display type Download BLOB.

To include a download link:

View the page containing the report in Page Designer.
If your report’s Source is based on a Table / View, change this attribute to SQL Query.
The SQL Query attribute automatically generates the corresponding query.
Add a new numeric/integer column expression to your query, typically the size of the file referenced by the download link. If the length is 0, the BLOB is NULL, and no download link is displayed.
To calculate the file size use an expression such as:
```
...
   col_n,
   sys.dbms_lob.getlength([blob_column]) as download
from ...
```
Find Identification of your download column. From Type, select Download BLOB.
Find BLOB Attributes. Configure the following:
1. Table Name - Enter the table name, which stores the BLOB content you want to display. You can type in the table name or pick from the list of available tables.
2. BLOB Column - Select the BLOB column from the table, which stores the BLOB content you want to display.
3. Primary Key Column(s) - Select the primary key column(s) from the table, which stores the BLOB content you want to display. If the primary key of this table has more than two columns, you must select the ROWID column.
4. Mime Type Column – Optionally, select the mime type column from the table, which stores the BLOB content you want to display.
5. Filename Column – Optionally, select the filename column from the table, which stores the BLOB content you want to display.
6. Last Updated Column – Optionally, select the last updated column from the table, which stores the BLOB content you want to display. Ideally, this column represents the update of the file, not other column’s values.
7. Character Set Column – Optionally, select the character set column from the table, which stores the BLOB content you want to display.
Find Appearance:
1. Download Text - Enter the text to use for the download link on the page.
2. Content Disposition – Choose between Attachment and Inline.
  Attachment forces a download of the file, while Inline will try to render the file in the browser, if possible. The inline behavior is depending on browser capabilities and configuration with respect to the file’s mime type.
Click Save or Save and Run Page.

Parent topic: Understanding BLOB Support in Forms and Reports

8.11.3 Displaying a BLOB Image in a Report

Display a BLOB image in a report by adding columns to your report query.

If the BLOB you are working with is an image, you can display it in a report using the display type Display Image.

To display a BLOB that is an image in a report:

View the page containing the report in Page Designer.
If your report’s Source is based on a Table / View, change this attribute to SQL Query.
The SQL Query attribute automatically generates the corresponding query.
Add a new numeric/integer column expression to your query, typically the size of the image file. If the length is 0, the BLOB is NULL and no image is displayed.
To calculate the file size use expression such as:
```
...
   col_n,
   sys.dbms_lob.getlength([blob_column]) as image
from ...
```
Find Identification. From Type, select Display Image.
Find BLOB Attributes. Configure the following:
1. Table Name - Enter the table name, which stores the BLOB content you want to display. You can type in the table name or pick from the list of available tables.
2. BLOB Column - Select the BLOB column from the table, which stores the BLOB content you want to display.
3. Primary Key Column(s) - Select the primary key column(s) from the table, which stores the BLOB content you want to display. If the primary key of this table has more than two columns, you must select the ROWID column.
4. Mime Type Column – Optionally, select the mime type column from the table, which stores the BLOB content you want to display.
5. Filename Column – Optionally, select the filename column from the table, which stores the BLOB content you want to display.
6. Last Updated Column – Optionally, select the last updated column from the table, which stores the BLOB content you want to display. Ideally, this column represents the update of the file, not other column’s values.
If the BLOB column contains a file other than an image and depending on how the browser handles the file type, the content will not display.

The image will be rendered as-is, meaning the image displays with its original height and width in pixels.
To add some custom CSS to your page so that the images fits in your report:
1. Find Advanced. Add a Static ID to your report’s image column (for example, my_report_image_column).
2. Find CSS. Add CSS code to the Inline Attribute. For example, to display the image with a height of 25 pixels you would add:
```
td[headers="my_report_image_column"] img {
    height: 25px;
}
```
Click Save or Save and Run Page.

Parent topic: Understanding BLOB Support in Forms and Reports

8.11.4 About Working With BLOBs Procedurally

As an alternative to using the built-in methods of providing a download link, use the APEX_UTIL.GET_BLOB_FILE_SRC function.

One advantage of using the APEX_UTIL.GET_BLOB_FILE_SRC function 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.