Display Image from Directory in ORACLE APEX

Display Image from Directory in ORACLE APEX.

One of my colleague had asked to me, How can i display image in Interactive/Classic Report or Interactive Grid from Directory without storing image in table. I got a solution and going to share that how i can achieve in ORACE APEX.

STEP 1 : CREATE TABLE USING BELOW QUERY.

Copy Code


CREATE TABLE PORTAL.A_TEST_IMAGES
(
  ID          NUMBER,
  IMAGE       BLOB,
  FILE_NAME   VARCHAR2(20 BYTE),
  MIMETYPE    VARCHAR2(100 BYTE),
  PRODUCT_ID  VARCHAR2(10 BYTE)
)

STEP 2 : You will have to create a directory inside the apex>image (/i/) folder which is directly accessible from the APEX.

Copy Code

CREATE OR REPLACE DIRECTORY APP_IMG AS 'E:\apex\images\APP_IMG'; 

GRANT read, write ON DIRECTORY APP_IMG TO PORTAL

STEP 3 : Crate report with form and use below query for Interactive Report.

Copy Code

SELECT "ID",
       DBMS_LOB.getlength ("IMAGE") "IMAGE",
       "FILE_NAME",
       "MIMETYPE",
       "PRODUCT_ID",
          '<Img src="/i/APP_IMG/'
       || product_id
       || '.jpg" alt="Smiley face" height="42" width="42">'
          AS img
  FROM "#OWNER#"."A_TEST_IMAGES"

for Interactive Grid use below query and add virtual column to display image.

Copy Code

SELECT id,
       DBMS_LOB.getlength (image) image,
       file_name,
       mimetype,
       product_id "DISPLAY_IMG"
  FROM a_test_images

STEP 4 : Add custom process to upload image in directory.

Copy Code

DECLARE
   l_message     VARCHAR2 (1000);

   CURSOR c_get_file
   IS
      SELECT blob_content, filename, mime_type
        FROM apex_application_temp_files;

   l_blob        BLOB;
   l_file_name   VARCHAR2 (200);
   l_mimetype    VARCHAR2 (200);
   l_seq         a_test_images.id%TYPE;
BEGIN
   SELECT NVL (MAX (id), 0) + 1 INTO l_seq FROM a_test_images;

   OPEN c_get_file;

   FETCH c_get_file INTO l_blob, l_file_name, l_mimetype;

   CLOSE c_get_file;

   INSERT INTO a_test_images (id,
                              file_name,
                              mimetype,
                              product_id)
        VALUES (l_seq,
                :p51_product_id || '.jpg',
                l_mimetype,
                :p51_product_id);

   load_blob (o_message     => l_message,
              i_file        => l_blob,
              i_file_name   => :p51_product_id || '.jpg',
              i_ext         => 'jpg');

   SELECT l_message INTO :p51_msg FROM DUAL;

   DELETE apex_application_temp_files;
EXCEPTION
   WHEN OTHERS
   THEN
      DELETE apex_application_temp_files;
END;

Copy Code

CREATE OR REPLACE PROCEDURE portal.load_blob (o_message        OUT VARCHAR2,
                                              i_file        IN     BLOB,
                                              i_file_name   IN     VARCHAR2,
                                              i_ext         IN     VARCHAR2)
AS
   l_file       UTL_FILE.file_type;

   l_buffer     RAW (32767);

   l_amount     BINARY_INTEGER := 32767;

   l_pos        INTEGER := 1;

   l_blob       BLOB := i_file;

   l_blob_len   INTEGER;
BEGIN
   -- Get LOB locator

   l_blob_len := DBMS_LOB.getlength (l_blob);

   -- Open the destination file.

   --l_file := UTL_FILE.fopen('BLOBS',i_file_name,'w', 32767);

   --  l_file := UTL_FILE.fopen('APEX_TEMP',i_file_name  ,'wb', 32767);
   l_file :=
      UTL_FILE.fopen ('APP_IMG',
                      i_file_name,
                      'wb',
                      32767);

   -- Read chunks of the BLOB and write them to the file

   -- until complete.

   WHILE l_pos <= l_blob_len
   LOOP
      DBMS_LOB.read (l_blob,
                     l_amount,
                     l_pos,
                     l_buffer);

      UTL_FILE.put_raw (l_file, l_buffer, TRUE);

      l_pos := l_pos + l_amount;
   END LOOP;

   -- Close the file.

   UTL_FILE.fclose (l_file);

   o_message := 'File loaded ';
EXCEPTION
   WHEN OTHERS
   THEN
      -- Close the file if something goes wrong.

      IF UTL_FILE.is_open (l_file)
      THEN
         UTL_FILE.fclose (l_file);
      END IF;

      o_message := SQLERRM || CHR (13) || DBMS_UTILITY.format_error_backtrace;
END load_blob;

Now Run and check the result in the Directory and report.

Display Image from Directory in ORACLE APEX.

You might also like: