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:
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
WHERE description = p_CharStyleDesc
AND created_date > trunc(sysdate);
IF lv_count > 0 THEN
SELECT image_id
INTO lv_NbrImage_id
FROM images
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?
Please help me out.