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  

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;  

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; 

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;