Display Image from Directory in ORACLE APEX

Table Of Contents

Display Image from Directory in ORACLE APEX.

Preview

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.


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.

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

GRANT read, write ON DIRECTORY APP_IMG TO PORTAL

Preview

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

Preview

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.

select ID, 
dbms_lob.getlength(IMAGE) IMAGE,
FILE_NAME,
MIMETYPE,
PRODUCT_ID "DISPLAY_IMG"
from A_TEST_IMAGES 

Preview

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

Preview

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;

Preview

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.

Preview

Preview

You might also like:


comments powered by Disqus