Manually Image Insert on Oracle Database Table

Topic Introduction: As usual inserting an image on Oracle Apex by the wizard is very easy. If we want to make a manual process to insert data with an image or any kind of blob file we should follow this technique. In this tutorial, we will discuss how to insert an image into a database table, show inserted image, and the option to download that image.

1. Table Creation for Image Store: 

CREATE TABLE IMAGE_TEST

(

  ID                 NUMBER,

  DESCRIPTION        VARCHAR2(100 BYTE),

  ITEM_IMAGE         BLOB,

  MIN_TYPE           VARCHAR2(32 BYTE),

  FILENAME           VARCHAR2(250 BYTE),

  FILE_CHAR_SET      VARCHAR2(250 BYTE),

  FILE_LAST_UPDATED  DATE,

  CLOBCOLUMN         CLOB

)

2. Manually Image Insert by PL/SQL: 

DECLARE

A NUMBER; B NUMBER;

l_image_id     IMAGE_TEST.ID%type;

l_file         blob;

l_mime_type    APEX_200100.wwv_flow_file_objects$.mime_type%type;

l_name         APEX_200100.wwv_flow_file_objects$.name%type;

l_file_ext     varchar2(255) := regexp_substr(:P600_ITEM_IMAGE, '\..*$');

begin  

begin

select   name, mime_type, blob_content

into   l_name, l_mime_type, l_file

from   APEX_200100.wwv_flow_file_objects$

where   name = :P600_ITEM_IMAGE;

EXCEPTION 

WHEN NO_DATA_FOUND THEN NULL;

WHEN OTHERS THEN NULL;

end ;

    IF :P600_ID IS NULL THEN

             SELECT NVL(MAX(ID),0)+1 INTO :P600_ID FROM IMAGE_TEST;

             insert into IMAGE_TEST(ID,DESCRIPTION,ITEM_IMAGE,MIN_TYPE,FILENAME,FILE_CHAR_SET,FILE_LAST_UPDATED)  

             values (:P600_ID,:P600_DESCRIPTION,l_file,l_mime_type,l_name,NULL,:P600_FILE_LAST_UPDATED);                      

     --P600_ID,P600_DESCRIPTION,P600_ITEM_IMAGE,P600_MIN_TYPE,P600_FILENAME,P600_FILE_CHAR_SET,P600_FILE_LAST_UPDATED     

     

    ELSE 

         

             update IMAGE_TEST 

           set  DESCRIPTION=:P600_DESCRIPTION,

ITEM_IMAGE= NVL(:P600_ITEM_IMAGE,NULL,ITEM_IMAGE,l_file),

MIN_TYPE=l_mime_type,

FILENAME=l_name,

FILE_CHAR_SET=NULL,

FILE_LAST_UPDATED=:P600_FILE_LAST_UPDATED

   where ID  = :P600_ID;  

        --apex_error.add_error(p_message => 'ERROR', p_display_location => apex_error.c_inline_in_notification);            

    END IF;

end;

3. Query For Image Show and Download: 

select ID,

       DESCRIPTION,

       dbms_lob.getlength(ITEM_IMAGE) ITEM_IMAGE, --DISPLAY IMAGE

       MIN_TYPE,

       FILENAME,

       FILE_CHAR_SET,

       FILE_LAST_UPDATED

       ,dbms_lob.getlength(ITEM_IMAGE)  downfile --DOWNLOAD BLOB & FULLFILL OTHER PROPERTIES SUCHAS MIN_TYPE,FILENAME ETC

       ,clobcolumn

  from IMAGE_TEST