Updating BLOB column

709491

Dear all,
I have made a database procedure for inserting BLOB to database, using this code
-----
INSERT INTO CUST_SIGNATURE
(CUSTOMERID, SIG_NAME, MIME_TYPE, SIG_IMG)
VALUES
(p_custid, v_image_name, v_mime_type, empty_blob()) RETURN sig_img INTO b_lob;

f_lob := BFILENAME('SIG_SOURCE_DIR', p_filename);

dbms_lob.fileopen(f_lob, dbms_lob.file_readonly);

dbms_lob.loadfromfile(b_lob, f_lob, dbms_lob.getlength(f_lob));
dbms_lob.fileclose(f_lob);
COMMIT;
-----

Now i need to update the blob column with a new image.
The primary key for the table is combination of (CUSTOMERID, SIG_NAME).

I need to update the image for the selected (CUSTOMERID, SIG_NAME)

I searched in net. But couldnt get a proper answer.
Need something like this
-----
upate CUST_SIGNATURE
set SIG_IMG = to a blob
where customerid=p_custid, and sig_name= p_filename;
-----
Thanks in advance

€$ħ₪

Create a pointer and then you can update your table

declare
file_ptr Bfile;
....
.....
.......

In the body of the program
begin
for cur is cursorname
loop
filename := cur.column || '.jpeg' ;
file_ptr := Bfilename('dirname','filename'); ---------dirname(IN) varchar2(20);
update table
set vedio=file_ptr
where current of cursor;
...
end loop;
end;



-Ek

Hope this helps....

709491

Now i just made the following procedure

-----
PROCEDURE update_sig(p_custid VARCHAR2, p_filename VARCHAR2) IS
file_ptr BFILE;
CURSOR update_cur IS
SELECT *
FROM CUST_SIGNATURE
WHERE CUSTOMERID = p_custid AND sig_name = p_filename
FOR UPDATE;

BEGIN
FOR cur IN update_cur LOOP
file_ptr := BFILENAME('SIG_SOURCE_DIR', 'testimage.jpg');
UPDATE CUST_SIGNATURE
SET SIG_IMG = file_ptr
WHERE CURRENT OF update_cur;
END LOOP;
END update_sig;
-----

But while compiling m getting this error:

ORA-00932: inconsistent datatypes: expected NUMBER got FILE
Text: UPDATE CUST_SIGNATURE SET SIG_IMG = file_ptr WHERE CURRENT OF update_cur;
*

please help

709491

Here is a better method for updating BLOB.
-----
PROCEDURE update_image(p_newfilename VARCHAR2) IS
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
src_file := bfilename('SIG_SOURCE_DIR', p_newfilename);

-- lock record
SELECT BLOB_IMG
INTO dst_file
FROM tablename
WHERE condition
FOR UPDATE;

-- open the file
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);

-- determine length
lgh_file := dbms_lob.getlength(src_file);

-- read the file
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

-- update the blob field
UPDATE tablename
SET BLOB_IMG = dst_file
WHERE condition

-- close file
dbms_lob.fileclose(src_file);

END update_image;
-----

More details in this link [http://www.psoug.org/reference/dbms_lob.html]

Edited by: WarFox on Nov 17, 2009 3:53 AM