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

1. Introduction

Welcome to a new blog post. In this post, I will show you how to display an image in a report within Oracle APEX after uploading images to an existing table. Additionally, I will add a button that, when clicked, will display the image in a modal page within the browser. Furthermore, I will add another button in the report that allows downloading the respective image. I will show you how all of this works step by step.

2. Preparing the Table

If you already have a table, you can start with that. Otherwise, you can use the Department table for testing.

2.1. Creating the Sequence and Table

Here is the code to create the Department table. First, a sequence is created, and then the table.

 

SQL

xxxxxxxxxx

32

 

1

-- Drop the old sequence if it exists

2

drop sequence apno_depa_seq;

3

​

4

-- Create a new sequence for departments

5

create sequence apno_depa_seq 

6

start with 100 

7

increment by 1 

8

nocache 

9

nocycle;

10

​

11

-- Drop the old department table if it exists

12

drop table apno_department;

13

​

14

-- Create a new department table

15

create table apno_department(

16

    depa_id                number              default apno_depa_seq.nextval primary key,

17

    depa_name              varchar2(4000)      not null,

18

    depa_location          varchar2(4000)      not null,

19

    -- Picture

20

    depa_image             blob                default null,

21

    depa_image_name        varchar2(4000)      default null,

22

    depa_image_mimetype    varchar2(4000)      default null,

23

    -- Audit

24

    depa_remark            clob                default null,

25

    depa_created           timestamp           default sysdate not null,

26

    depa_created_by        varchar2(4000)      default (coalesce(sys_context('apex$session', 'app_user'), sys_context('userenv', 'os_user'), sys_context('userenv', 'session_user')) ) not null,

27

    depa_updated           timestamp           default sysdate not null,

28

    depa_updated_by        varchar2(4000)      default (coalesce(sys_context('apex$session', 'app_user'), sys_context('userenv', 'os_user'), sys_context('userenv', 'session_user')) ) not null,

29

    depa_valid_from        timestamp           default sysdate not null,

30

    depa_valid_to          timestamp           default to_date('31.12.2999', 'dd.mm.yyyy') not null,

31

    depa_deleted_yn        varchar2(4000)      default 'NO'

32

);

Explanation:

• Sequence: A sequence is created to generate unique depa_id values.
• Table: The apno_department table is created to store department information. It includes columns for department ID, name, location, image (as BLOB), image name, image MIME type, and some audit fields.

2.2. Inserting Records

Next, we will insert some records into the Department table.

 

SQL

xxxxxxxxxx

12

 

1

begin

2

    insert into apno_department (depa_id, depa_name, depa_location) values (1, 'Accounting', 'Frankfurt');

3

    insert into apno_department (depa_id, depa_name, depa_location) values (2, 'Research', 'Munich');

4

    insert into apno_department (depa_id, depa_name, depa_location) values (3, 'Sales', 'Hamburg');

5

    insert into apno_department (depa_id, depa_name, depa_location) values (4, 'Operations', 'Berlin');

6

    insert into apno_department (depa_id, depa_name, depa_location) values (5, 'Human Resources', 'Düsseldorf');

7

    insert into apno_department (depa_id, depa_name, depa_location) values (6, 'IT', 'Stuttgart');

8

    insert into apno_department (depa_id, depa_name, depa_location) values (7, 'Customer Service', 'Leipzig');

9

    insert into apno_department (depa_id, depa_name, depa_location) values (8, 'Production', 'Dresden');

10

    insert into apno_department (depa_id, depa_name, depa_location) values (9, 'Legal Department', 'Hannover');

11

    insert into apno_department (depa_id, depa_name, depa_location) values (10, 'Strategy and Development', 'Nuremberg');

12

end;

Explanation:

• Records: Example records are inserted into the apno_department table to represent various departments with their locations.

3. Creating a Report and Form

Create a report to display all Department entries and a form page to add new Department entries or edit existing ones and upload images.

3.1. Creating the Report

Navigate to the Oracle APEX Page Designer and create a new report based on the apno_department table.

• Report Type: Interactive Report
• SQL Query:

 

SQL

xxxxxxxxxx

15

 

1

select DEPA_ID,

2

       DEPA_NAME,

3

       DEPA_LOCATION,

4

       dbms_lob.getlength(DEPA_IMAGE) as DEPA_IMAGE,

5

       DEPA_IMAGE_NAME,

6

       DEPA_IMAGE_MIMETYPE,

7

       DEPA_REMARK,

8

       DEPA_CREATED,

9

       DEPA_CREATED_BY,

10

       DEPA_UPDATED,

11

       DEPA_UPDATED_BY,

12

       DEPA_VALID_FROM,

13

       DEPA_VALID_TO,

14

       DEPA_DELETED_YN

15

  from APNO_DEPARTMENT

Explanation:

• Interactive Report: Allows users to browse, filter, and sort the data.
• SQL Query: Retrieves all relevant information from the apno_department table.

3.2. Creating the Form

Create a form page to add new departments or edit existing ones.

• Form Type: Form on a Table with Report
• Table: apno_department

Explanation:

• Form: Allows users to create, update, or delete data in the apno_department table.

4. Creating a Procedure

The next step is to create a procedure to download or preview files.

 

SQL

xxxxxxxxxx

73

 

1

create or replace PROCEDURE pr_download_file_from_depa(

2

   pi_primary_ID          IN NUMBER,       -- primary ID

3

   pi_view_type           IN VARCHAR2      -- Type of view, D for 'DOWNLOAD'  or P for 'PREVIEW'

4

)

5

IS 

6

  c_primary_ID            CONSTANT VARCHAR2(30) := pi_primary_ID;

7

  c_view_type             CONSTANT VARCHAR2(30) := pi_view_type;

8

​

9

  v_blob_content          BLOB;

10

  v_mime_type             VARCHAR2(500);

11

  v_filename              VARCHAR2(500); 

12

  v_check_data_in_db      number; 

13

BEGIN 

14

​

15

    -- check if db have any data

16

    select count(*)

17

      into v_check_data_in_db

18

      from dual

19

      where exists(select DEPA_ID

20

                     from APNO_DEPARTMENT

21

                    where DEPA_IMAGE is not null

22

                      and DEPA_ID = c_primary_ID

23

                  )

24

    ;

25

​

26

    -- if data found

27

    if v_check_data_in_db = 1 then 

28

​

29

        -- get blob file

30

        select DEPA_IMAGE

31

             , DEPA_IMAGE_NAME

32

             , DEPA_IMAGE_MIMETYPE

33

          into v_blob_content

34

             , v_filename

35

             , v_mime_type

36

          from APNO_DEPARTMENT

37

         where DEPA_ID = pi_primary_ID

38

        ;

39

​

40

        -- Set initial HTTP header

41

        sys.HTP.init;

42

        sys.OWA_UTIL.mime_header(v_mime_type, FALSE);

43

​

44

        -- Set content length

45

        sys.HTP.p('Content-Length: ' || DBMS_LOB.getlength(v_blob_content));

46

​

47

        -- Extract and set the MIME type

48

        v_mime_type := SUBSTR(v_mime_type, INSTR(v_mime_type, '/') + 1);

49

50

        -- for download file

51

        if c_view_type = 'D' then

52

            -- Set Content-Disposition to attachment to force download

53

            sys.HTP.p('Content-Disposition: attachment; filename="' || v_filename || '"');

54

        elsif c_view_type = 'P' then

55

            -- Set null Content-Disposition to preview

56

            sys.HTP.p('Content-Disposition: filename="' || v_filename || '"');

57

        end if;

58

​

59

        -- Send the headers

60

        sys.OWA_UTIL.http_header_close;

61

​

62

        -- Send the BLOB data to the client

63

        sys.WPG_DOCLOAD.download_file(v_blob_content);

64

65

        -- Stop the APEX engine to prevent further processing

66

        apex_application.stop_apex_engine;

67

68

    end if;

69

​

70

   EXCEPTION WHEN apex_application.e_stop_apex_engine THEN NULL;

71

72

END pr_download_file_from_depa;

73

/

Explanation:

• pi_primary_ID: The primary ID of the file to be downloaded or previewed.
• pi_view_type: The type of view, 'D' for download and 'P' for preview.
• v_blob_content: Variable to store the binary content of the file.
• v_mime_type: Variable to store the MIME type of the file.
• v_filename: Variable to store the file name.
• v_check_data_in_db: Variable to check if the file exists in the database.

The procedure first checks if the file exists in the database. If the file exists, it retrieves the file content, name, and MIME type. Based on the pi_view_type parameter, it either downloads the file or previews it inline.

5. Creating Application Items

We need to create two application items to store the file ID and view type. These items will be used to pass parameters to the pr_download_file procedure.

• P_DOWNLOAD_FILE_ID: Stores the ID of the file to be downloaded or previewed.
• P_DOWNLOAD_TYPE: Stores the type of view, 'D' for download and 'P' for preview.

6. Creating an Application Process

Create an application process that calls the pr_download_file procedure. This process will be executed when the user clicks on the download or preview link.

 

SQL

xxxxxxxxxx

 

1

BEGIN

2

    pr_download_file_from_depa(

3

       pi_primary_ID => :P_DOWNLOAD_FILE_ID,

4

       pi_view_type  => :P_DOWNLOAD_TYPE

5

    );

6

END;

Explanation:

• :P_DOWNLOAD_FILE_ID: The ID of the file to be downloaded or previewed.
• :P_DOWNLOAD_TYPE: The type of view, 'D' for download and 'P' for preview.

7. Creating an Oracle APEX Page

Navigate to the Oracle APEX Page Designer to create a report based on the file_uploads table and a form to upload various files. Make sure to upload some files initially so that your table has data for testing purposes.

Additionally, create another page as a modal drawer. This drawer page will be used to preview any data when the user clicks on a preview link.

In the report, create columns for file preview and download. For the download column, set the link to trigger the download process without redirecting the page. For the preview column, set the link to open the modal drawer page, allowing the user to view the file.

You will need to set two application items to link the file ID and the view type (P for preview, D for download). Also, configure the application process accordingly.

8. Creating a Report with SQL Statement

Create a report with the following SQL statement to display the report on the Oracle APEX page.

Button HTML:

 

SQL

xxxxxxxxxx

21

 

1

select DEPA_ID,

2

       DEPA_NAME,

3

       DEPA_LOCATION,

4

       dbms_lob.getlength(DEPA_IMAGE) as DEPA_IMAGE,

5

       case when DEPA_IMAGE is not null then

6

       '<button type="button" title="" aria-label="" class="t-Button t-Button--noLabel t-Button--icon t-Button--noUI"><span aria-hidden="true" class="t-Icon fa fa-download"></span></button>'

7

       end as Download,

8

       case when DEPA_IMAGE is not null then

9

       '<button type="button" title="" aria-label="" class="t-Button t-Button--noLabel t-Button--icon t-Button--noUI"><span aria-hidden="true" class="t-Icon fa fa-eye"></span></button>'

10

       end as Preview,

11

       DEPA_IMAGE_NAME,

12

       DEPA_IMAGE_MIMETYPE,

13

       DEPA_REMARK,

14

       DEPA_CREATED,

15

       DEPA_CREATED_BY,

16

       DEPA_UPDATED,

17

       DEPA_UPDATED_BY,

18

       DEPA_VALID_FROM,

19

       DEPA_VALID_TO,

20

       DEPA_DELETED_YN

21

  from APNO_DEPARTMENT

Explanation:

• Button HTML: Adds download and preview buttons to the report when an image is present.

9. Adding Custom CSS (Optional)

Add the following CSS to the Oracle APEX page and assign the report a static ID: Department_Report. This will ensure that only images in the report are displayed smaller.

Custom CSS:

 

CSS/SCSS

xxxxxxxxxx

 

1

# Department_Report img {

2

    border: 0;

3

    width: 50px;

4

    height: 50px;

5

    border-radius: 100px;

6

}

Explanation:

• CSS: Ensures that images in the report are displayed smaller and round.

10. Configuring the Preview and Download Columns

To allow users to preview and download files directly from the report, you need to add two columns with links for these actions. Follow these steps to set it up:

10.1. Preview Column

• Add a new column to your report.
• Set the column type to "Link".
• Configure the link to set the application item P_DOWNLOAD_FILE_ID to the file ID and P_DOWNLOAD_TYPE to 'P'.
• Set the target to the modal drawer page.

10.2. Download Column

• Add another new column to your report.
• Set the column type to "Link".
• Configure the link to set the application item P_DOWNLOAD_FILE_ID to the file ID and P_DOWNLOAD_TYPE to 'D'.
• Set the target to the current page to avoid redirection.

 

SQL

xxxxxxxxxx

 

1

APPLICATION_PROCESS=PR_DOWNLOAD_FILE

Explanation:

• Preview Column: When the user clicks on this link, the file will be previewed in the browser using the modal drawer.
• Download Column: When the user clicks on this link, the file will be downloaded directly.

11. Supported File Formats for Preview in Chrome

Here is a list of file formats that can be uploaded and previewed directly in Chrome:

Text-Based Formats

• HTML (.html, .htm): HyperText Markup Language files.
• Text (.txt): Plain text files.
• JavaScript (.js): JavaScript files.
• CSS (.css): Cascading Style Sheets files.
• CSV (.csv): Comma-separated values files.
• JSON (.json): JavaScript Object Notation files.

Image Formats

• JPEG (.jpeg, .jpg): Joint Photographic Experts Group files.
• PNG (.png): Portable Network Graphics files.
• GIF (.gif): Graphics Interchange Format files.
• SVG (.svg): Scalable Vector Graphics files.
• WebP (.webp): Web Picture format files.

Video Formats

• MP4 (.mp4): MPEG-4 Part 14 files.
• WebM (.webm): Web Media files.
• Ogg (.ogg): Ogg multimedia files.

Audio Formats

• MP3 (.mp3): MPEG-1 Audio Layer III files.
• WAV (.wav): Waveform Audio File Format files.
• Ogg (.ogg): Ogg Vorbis audio files.

Document Formats

• PDF (.pdf): Portable Document Format files.
• Microsoft Word (.doc, .docx): Microsoft Word document files.
• Microsoft Excel (.xls, .xlsx): Microsoft Excel spreadsheet files.
• Microsoft PowerPoint (.ppt, .pptx): Microsoft PowerPoint presentation files.

For other file types, the default behavior will be to download the file.

12. Implementing Bulk Download Functionality

To allow users to download all files at once, you can create a button that triggers a dynamic action to initiate a bulk download. This feature is available starting from Oracle APEX 24.1. When the button is clicked, all files specified in the SQL query will be zipped and offered as a single download.

Creating the Bulk Download Button

• Add a Button:
• Go to the Page Designer and add a new button to your report page.
• Label the button as "Download All Files".

Configuring the Dynamic Action

• Create a new dynamic action for the button click event.
• Set the action to "Download".
• Use the following PL/SQL code to fetch all files and zip them:

 

SQL

xxxxxxxxxx

 

1

select file_content

2

      , file_content_name 

3

   from file_uploads 

4

 ;

Explanation:

• The dynamic action will execute the PL/SQL code to fetch all files from the file_uploads table.
• The files will be zipped and offered as a single download.

13. Conclusion

By following these steps, you can easily set up a system in Oracle APEX to preview or download files. This setup ensures that users have a seamless experience when interacting with files in your application. Whether it's text files, images, videos, or documents, Oracle APEX makes it easy to manage and display files.

I hope this guide helps you extend your Oracle APEX application. Good luck!

 {fullWidth}