How to Upload, Preview and Download Image blob Files in Oracle APEX

Introduction

Managing binary large object (BLOB) files, such as images, is a common requirement in applications that involve file storage. Oracle APEX offers powerful tools to facilitate the uploading, previewing, and downloading of images, ensuring an efficient and user-friendly experience. This guide provides a step-by-step approach to implementing these features in your Oracle APEX application.

Step 1: Prepare Your Database

Create a Table for Image Storage

Define a table to store images along with metadata such as file name, MIME type, and upload date:

 

SQL

xxxxxxxxxx

 

1

CREATE TABLE image_files (

2

    id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

3

    file_name VARCHAR2(255),

4

    mime_type VARCHAR2(100),

5

    upload_date DATE DEFAULT SYSDATE,

6

    image_blob BLOB

7

);

Grant Necessary Privileges

Ensure that the APEX_PUBLIC_USER has the appropriate privileges to access the table:

 

Plain Text

xxxxxxxxxx

 

1

GRANT SELECT, INSERT, UPDATE, DELETE ON image_files TO APEX_PUBLIC_USER;

Step 2: Create an APEX Application

Create a New Application

• Log in to Oracle APEX.
• Create a new application or use an existing one.

Add a Table Form

• Add a new page of type Form > Editable Interactive Grid.
• Use the image_files table as the data source.

Step 3: Enable Image Upload

Add a File Browse Item

• On the form page, add an item of type File Browse to allow users to upload images.
• Map the item to the image_blob column in the database.

Set the Storage Type

• In the settings of the File Browse item, set the Storage Type to BLOB Column specified in Item Source.
• Map the following columns:
• Filename Column: file_name
• MIME Type Column: mime_type

Step 4: Preview Uploaded Images

Add a Display Item

• Add a Display Only item on the same page to preview uploaded images.
• Set its source to a SQL query that retrieves the image:

 

SQL

xxxxxxxxxx

 

1

SELECT dbms_lob.getlength(image_blob) 

2

FROM image_files 

3

WHERE id = :P1_ID;

Configure Blob Content

• In the item settings, link the item to the image_blob column.
• Use the following mappings:
• Filename Column: file_name
• MIME Type Column: mime_type
• Set the Display as option to Image.

Step 5: Enable Image Download

Add a Download Button or Link

• In the interactive grid or report, add a column to display a Download button or link for each image.

Set Up a Download Process

Use the following PL/SQL process to handle the file download:

 

SQL

xxxxxxxxxx

 

1

BEGIN

2

    htp.init;

3

    owa_util.mime_header(:MIME_TYPE, FALSE);

4

    htp.p('Content-Length: ' || DBMS_LOB.getlength(:IMAGE_BLOB));

5

    htp.p('Content-Disposition: attachment; filename="' || :FILE_NAME || '"');

6

    owa_util.http_header_close;

7

    WPG_DOCLOAD.download_file(:IMAGE_BLOB);

8

END;

Conclusion

By following these steps, you’ve built a robust Oracle APEX application that allows users to:

• Upload image files.
• Preview uploaded images directly in the application.
• Download images with ease.

This method not only ensures efficient handling of BLOB files but also provides a seamless and intuitive interface for users. Whether for document management, image galleries, or file storage systems, these features are essential for modern applications.

Start implementing these features in your Oracle APEX applications today to enhance functionality and user experience!

 {fullWidth}