Forum Stats

  • 3,852,382 Users
  • 2,264,100 Discussions
  • 7,905,056 Comments

Discussions

Updating BLOB column

709491
709491 Member Posts: 15
edited Nov 17, 2009 6:53AM in SQL & PL/SQL
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

Answers

  • €$ħ₪
    €$ħ₪ Member Posts: 935
    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
    709491 Member Posts: 15
    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
    709491 Member Posts: 15
    edited Nov 17, 2009 6:53AM
    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
This discussion has been closed.