BLOB Support in Forms and Reports in Oracle Apex

Understanding BLOB Support in Forms and Reports

Topic Introduction: Oracle APEX provides built-in declarative support for working with Binary Large Objects (BLOBs), making it easy for developers to incorporate file management features into their applications. This functionality allows users to upload files through forms, and display or download them in reports, without the need for complex coding. 

In addition to the declarative approach, Oracle APEX also enables procedural management of BLOBs using PL/SQL, providing flexibility for developers who prefer to control file operations programmatically. This includes the ability to upload files, download them, and render them in the browser as needed.

Managing essential file attributes such as MIME type and file name is also possible within APEX, ensuring that files are handled properly in a variety of use cases. These attributes are crucial for ensuring that files are correctly identified, served, and displayed in the application, especially for media like images, videos, and documents.

This BLOB support in Oracle APEX makes it easier to integrate file management into your applications, from simple document storage to complex image or video handling, by offering both declarative and procedural ways to interact with files stored in BLOB columns.

BLOB Support in Oracle APEX Reports

Oracle APEX provides powerful capabilities for handling BLOB (Binary Large Object) data within both classic and interactive reports. This functionality enables developers to easily integrate various types of content, including files, images, and other BLOBs, into their reports, enhancing the user experience with rich media and file accessibility.

Enabling File Downloads in Oracle APEX Reports

To provide users with an easy way to download files directly from an Oracle APEX report, developers can integrate download links for Binary Large Object (BLOB) content. By adjusting the report queries and configuring specific attributes, you can create an interactive report that allows users to seamlessly download associated files.

Step 1: Modifying the Report Query for File Download

To add a download link in your report, you need to include a "number" type column in your query that will trigger the file download. The column should be set to "Download BLOB," which enables a clickable link for the user.

Here’s an example query to include in your report:

SELECT 
   COLUMN_N,
   DBMS_LOB.GETLENGTH([BLOB_COLUMN]) AS DOWNLOAD
FROM BLOB_TEST_TABLE

Step 2: Configuring the Report After Creation

When using the Oracle APEX wizard to generate a report that includes a BLOB column, the BLOB column is not automatically added. To enable the download functionality, you must make the following configurations:

• Download Identification: After adding the BLOB column, ensure that its display type is set to "Download BLOB."
• BLOB Attributes: Configure the necessary details to allow file download functionality.

Here’s what to configure:

• Table Name: Enter the name of the table containing the BLOB content.
• BLOB Column: Select the column that holds the BLOB content.
• Primary Key Columns: Choose the primary key column(s) to uniquely identify each row. If the primary key consists of more than two columns, select the ROWID column.
• Mime Type Column: Optionally, select a column to specify the file's MIME type.
• Filename Column: Optionally, select the column that contains the file name.
• Last Updated Column: Optionally, choose a column that reflects the file’s last modification timestamp.
• Character Set Column: Optionally, specify the character set used by the BLOB data.

Step 3: Customize Appearance and Behavior

Further personalize the download link's behavior in the report’s appearance settings:

• Download Text: Define the text that users will see as the download link.
• Content Disposition: Choose between two options:
  • Attachment: Forces the file to be downloaded to the user’s device.
  • Inline: Attempts to display the file in the browser, depending on the file type and the browser's capabilities.

Step 4: Save and Deploy the Report

Once you've configured the report and ensured the proper download attributes are set, click Save or Save and Run to finalize the report and make the download functionality available to users.

Displaying BLOB Images in Oracle APEX Reports

Oracle APEX simplifies the process of displaying images stored as BLOBs in reports. Developers can easily render these images by including the necessary columns in the report query. This approach allows for a dynamic and visually rich report experience, where images stored as BLOBs are seamlessly displayed, enhancing the overall user interface.

Displaying images stored as BLOBs in Oracle APEX reports is straightforward and involves adjusting your report query and settings. Follow these simple steps to show BLOB images in your report:

Steps to Display a BLOB Image in a Report:

1. Access Page Designer:

   • Open the page containing the report in Page Designer.

2. Modify the Source:

   • If your report's source is based on a Table/View, change it to an SQL Query. This allows more flexibility and control over the query.

3. Add a Numeric Column:

   • Add a new numeric column to your SQL query to check the image file size. If the length is zero, it means the BLOB is empty (NULL), and no image will be shown.

   Example SQL Query:

   SELECT 
      COLUMN_N,
      DBMS_LOB.GETLENGTH([BLOB_COLUMN]) AS IMAGE
   FROM BLOB_TEST_TABLE
   

4. Configure Image Display:

   • Set the Type of the new column to Display Image. This will tell Oracle APEX to render the BLOB content as an image.

5. BLOB Attributes Configuration:

   • Table Name: Enter the name of the table storing the BLOB content.
   • BLOB Column: Select the column that contains the BLOB data.
   • Primary Key Column(s): Choose the primary key column(s). If there are multiple columns in the primary key, select the ROWID column.
   • Optional Columns: You can also choose optional columns such as Mime Type, Filename, or Last Updated if needed.

   Note: If the BLOB is not an image, it may not display correctly, depending on the browser and file type.

6. Image Display Behavior:

   • The image will display with its original dimensions (height and width in pixels).

Customize Image Size with CSS:

To control the size of the displayed images in the report, you can add custom CSS. Here’s how to do it:

1. Assign a Static ID:

   • In Advanced settings, give your image column a Static ID (e.g., my_report_image_column).

2. Add CSS Code:

   • Go to CSS and add the following code in the Inline Attribute section. For example, to set the image height to 25 pixels:

   td[headers="my_report_image_column"] img {
       height: 25px;
   }
   

3. Save Changes:

   • Click Save or Save and Run Page to apply the changes.

With these steps, you can easily display and customize BLOB images in your Oracle APEX reports, providing a better user experience and more control over the visual presentation.

Managing BLOBs Programmatically in Oracle APEX

Beyond the declarative approach, Oracle APEX offers programmatic options for working with BLOBs. Developers can use the APEX_UTIL.GET_BLOB_FILE_SRC function to retrieve the source of a BLOB file. This allows for more granular control over how BLOB data is retrieved and displayed within reports or across different parts of the application.

This flexibility enables the creation of highly interactive reports and applications that can handle and present BLOB content efficiently, making it easier to manage files and images within your Oracle APEX applications.