Home » Developer & Programmer » Forms » Saving an IMAGE of BLOB type from Oracle forms to the database (Oracle Forms : 12c (12.2.1.4.0);Fusion Middleware: fmw_12.2.1.4.0 and O/S: Windows 10 pro 64 bit)
Saving an IMAGE of BLOB type from Oracle forms to the database [message #680638] Wed, 27 May 2020 16:02
buggleboy007
Messages: 256
Registered: November 2010
Location: Canada
Senior Member
I am running into a situation where in I am uploading an image from a local directory and then inserting it into the database via a procedure (located in database side). Neither does the form say that it 'SAVED' or committed nor does the image show up in the database (though the record commits itself). Here's how the whole structure is set up:

Table is called as IMAGES_2 and it's structure is below and I want to save the image in IMAGE_BLOB column:

SQL> desc images_2  
Name            Type            Nullable Default Comments   
--------------- --------------- -------- ------- --------   
IMAGE_ID        NUMBER(10)                                  
DESCRIPTION     VARCHAR2(120)   Y                           
ORIGINAL_NAME   VARCHAR2(250)   Y                           
ORIGINAL_PATH   VARCHAR2(200)   Y                           
SOURCE          VARCHAR2(20)    Y                           
PUBLIC_IND      VARCHAR2(1)                                 
STATUS          VARCHAR2(10)                                
CREATED_BY      VARCHAR2(30)                                
CREATED_DATE    DATE                                        
MODIFIED_BY     VARCHAR2(30)    Y                           
MODIFIED_DATE   DATE            Y                           
IMAGE_FILE      ORDSYS.ORDIMAGE Y                           
SESSION_ID      VARCHAR2(256)   Y                           
IMAGE_THUMBNAIL ORDSYS.ORDIMAGE Y                           
TYPE            VARCHAR2(10)                                
IMAGE_BLOB      BLOB            Y  
The code in the forms is as follows (WHEN BUTTON PRESSED TRIGGER for IMAGE UPLOADING)
DECLARE  
  
v_file varchar2(4000):=client_get_file_name ('','', 'C:\|*.TIFF|*.gif|', 'Please Select Image File', open_file, TRUE);  
  
it_image_id ITEM:=FIND_ITEM('IMAGES.IMAGE_FILE_SOURCE_LOCALDATA');  
BEGIN  
  
IF v_file IS NOT NULL THEN  
:GLOBAL.v_file:=v_file;  
CLIENT_IMAGE.READ_IMAGE_FILE(:GLOBAL.v_file ,'', it_image_id);  
 END IF;  
END;  
Code in POST-INSERT:
DECLARE  
 lv_color_id    STYLE_COLORS.color_id%TYPE;  
 lv_NbrImageSeq NUMBER(5);  
 lv_BlbImageNm  BLOB;  
BEGIN  
lv_BlbImageNm:=utl_raw.cast_to_raw(SUBSTR(:GLOBAL.v_file, instr(:GLOBAL.v_file, '\', -1) + 1));  
     
  
 IF :GLOBAL.v_file IS NOT NULL THEN   
    insert_in_images_2(:STLS.DESCRIPTION,  
    SUBSTR(:GLOBAL.v_file, instr(:GLOBAL.v_file, '\', -1) + 1),  
    SUBSTR(:GLOBAL.v_file,1,(INSTR(:GLOBAL.v_file,'\',-1,1)-1)),  
    'SCREEN',  
    'Y',  
    'ACTIVE',  
   USER,  
  SYSDATE,  
    NULL,  
    NULL,  
    NULL,  
    NULL,  
    NULL,  
  'DEFAULT',  
    lv_NbrImageSeq  
    );  
    insert_color_id(lv_NbrImageSeq);  
ELSIF :GLOBAL.v_file IS NULL THEN  
     insert_in_images_2(:STLS.DESCRIPTION,  
    NULL,  
    NULL,  
    'SCREEN',  
    'Y',  
    'ACTIVE',  
   USER,  
  SYSDATE,  
    NULL,  
    NULL,  
    NULL,  
    NULL,  
    NULL,  
  'DEFAULT',  
  lv_NbrImageSeq  
    );  
    insert_color_id(lv_NbrImageSeq);  
  
  END IF;  
END; 
Finally the database procedure INSERT_IN_IMAGES_2
CREATE OR REPLACE PROCEDURE insert_in_images_2(p_CharStyleDesc IN VARCHAR2,  
                                              p_CharFileName  IN VARCHAR2,  
                                              p_CharFilePathName IN VARCHAR2,  
                                              p_CharSource IN VARCHAR2,  
                                              p_CharPublicInd IN VARCHAR2,  
                                              p_CharStatus IN VARCHAR2,  
                                              p_charCreatedBy IN VARCHAR2,  
                                              p_DtCreatedDate IN DATE,  
                                              p_CharModBy IN VARCHAR2,  
                                              p_DtModDate IN DATE,  
                                              p_BlobImgFile IN ORDSYS.ORDIMAGE,  
                                              p_CharSessionId IN VARCHAR2,  
                                              p_BlobImgThmb IN ORDSYS.ORDIMAGE,  
                                              p_CharType IN VARCHAR2,  
                        p_lv_NbrImageSeq OUT NUMBER  
                                              )  
  
  
  
  
  
  
    IS  
      lv_count NUMBER(3);  
      lv_NbrImage_id NUMBER(5);  
      --lv_NbrImageSeq  NUMBER(5);  
      --lv_NbrSeqCapture NUMBER(5);  
    BEGIN  
        --Checking to see if the image was already created today (Sysdate)  
               SELECT COUNT(*)  
               INTO lv_count  
               FROM images_2  
               WHERE description = p_CharStyleDesc  
               AND created_date > trunc(sysdate);  
  
  
              IF lv_count > 0 THEN  
                 SELECT image_id  
                 INTO lv_NbrImage_id  
                 FROM images_2 
                 WHERE description = p_CharStyleDesc  
                 AND created_date > trunc(sysdate);  
  
  
             p_lv_NbrImageSeq:= lv_NbrImage_id;  
  
  
                UPDATE images_2  
                SET original_name = p_CharFileName,  
                    description = p_CharStyleDesc,  
                    image_id = lv_NbrImage_id,  
                    modified_by =  p_charCreatedBy,  
                    modified_date = SYSDATE,  
                    source =p_CharSource,  
                    public_ind =p_CharPublicInd,  
                    status =p_CharStatus,  
                    original_path = p_CharFilePathName,  
          image_blob=utl_raw.cast_to_raw(p_CharFileName)  
                WHERE description = p_CharStyleDesc  
                AND created_date > trunc(sysdate);  
  
  
              ELSIF lv_count = 0 THEN  
                     p_lv_NbrImageSeq:=image_id.nextval;  
                INSERT INTO images_2(image_id,  
                   description,  
                   original_name,  
                   original_path,  
                   source,  
                   public_ind,  
                   status,  
                   created_by,  
                   created_date,  
                   modified_by,  
                   modified_date,  
                   image_file,  
                   session_id,  
                   image_thumbnail,  
                   type,  
                   image_blob  
                 )  
                VALUES  
                  (p_lv_NbrImageSeq,  
                   p_CharStyleDesc,  
                   p_CharFileName,  
                   p_CharFilePathName,  
                   p_CharSource,  
                   p_CharPublicInd,  
                   p_CharStatus,  
                   p_charCreatedBy,  
                   p_DtCreatedDate,  
                   p_CharModBy,  
                   p_DtModDate,  
                   p_BlobImgFile,  
                   p_CharSessionId,  
                   p_BlobImgThmb,  
                   p_CharType,  
                   utl_raw.cast_to_raw(p_CharFileName)  
                  );  
                END IF;  
       COMMIT;  
    EXCEPTION  
      WHEN OTHERS THEN  
       log_output('Error in insert_in_images2 procedure'||'-'||SQLERRM||'-'||SQLCODE);  
       raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);  
    END insert_in_images_2;  
All it does is inserts a record and does not say whether it COMMITTED or not in the form and when I query the database against the column IMAGE_BLOB I see no image at all.

What is that I am doing wrong or missing that is causing this issue?

[Updated on: Wed, 27 May 2020 16:35]

Report message to a moderator

Previous Topic: Setting text label property
Next Topic: Issue while assigning summary calculated item to db item when-remove-record
Goto Forum:
  


Current Time: Thu Jul 09 23:45:21 CDT 2020