1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Update BLOB

Discussion in 'SQL PL/SQL' started by Nick2005, Feb 1, 2013.

  1. Nick2005

    Nick2005 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Update BLOB [SOLVED]

    Hi to All,
    I have a table like this:
    Code (Text):
    create table temp_photo
     (
     ID    NUMBER(3)     NOT NULL,
     PHOTO_NAME      VARCHAR2(50),
     PHOTO    BLOB
     );
    This table contains my photos. How can with a query update the field PHOTO, with a new image?
    Can someone help me ?
    Thank You
    Nick
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It's a fairly easy task:

    Code (SQL):
    SQL> CREATE TABLE temp_photo
    2 (
    3 ID NUMBER(3) NOT NULL,
    4 PHOTO_NAME VARCHAR2(50),
    5 PHOTO BLOB
    6 );
     
    TABLE created.
     
    SQL>
    SQL> CREATE OR REPLACE directory photo_dir AS 'C:\yarp';
     
    Directory created.
     
    SQL>
    SQL> DECLARE
    2 v_lob BLOB;
    3 v_id NUMBER;
    4 v_name varchar2(50);
    5 l_bfile bfile;
    6 amt NUMBER;
    7 BEGIN
    8 l_bfile := bfilename( 'PHOTO_DIR', 'df.jpg' );
    9
    10 INSERT INTO temp_photo (id, photo_name, photo)
    11 VALUES ( 1, 'My Photo',empty_blob() )
    12 returning id, photo_name, photo INTO v_id, v_name, v_lob;
    13
    14 amt := dbms_lob.getlength( l_bfile );
    15 dbms_lob.fileopen( l_bfile ,dbms_lob.file_readonly);
    16 dbms_lob.loadfromfile( v_lob, l_bfile ,amt);
    17
    18 dbms_output.put_line('File length is: '||dbms_lob.getlength( l_bfile ));
    19 dbms_output.put_line('Loaded length is: '||dbms_lob.getlength(v_lob));
    20 dbms_lob.fileclose( l_bfile );
    21 END;
    22 /
    File LENGTH IS: 69560
    Loaded LENGTH IS: 69560
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT * FROM temp_photo;
     
    ID PHOTO_NAME
    ---------- --------------------------------------------------
    PHOTO
    --------------------------------------------------------------------------------
    1 My Photo
    FFD8FFE000104A46494600010101006000600000FFDB004300020101010101020101010202020202
    0403020202020504040304060506060605060606070908060709070606080B08090A0A0A0A0A0608
     
    SQL>
    To update an exising photo is even easier:

    Code (SQL):
    SQL> DECLARE
      2    v_lob     BLOB;
      3    l_bfile   bfile;
      4    amt       NUMBER;
      5  BEGIN
      6    l_bfile := bfilename( 'PHOTO_DIR', 'df_old.jpg' );
      7
      8    SELECT photo INTO v_lob FROM temp_photo WHERE id=1;
      9
     10    amt := dbms_lob.getlength( l_bfile );
     11    dbms_lob.fileopen( l_bfile ,dbms_lob.file_readonly);
     12    dbms_lob.loadfromfile( v_lob, l_bfile ,amt);
     13
     14    dbms_output.put_line('File length is: '||dbms_lob.getlength( l_bfile ));
     15    dbms_output.put_line('Loaded length is: '||dbms_lob.getlength(v_lob));
     16    dbms_lob.fileclose( l_bfile );
     17  END;
     18  /
    File LENGTH IS: 180487
    Loaded LENGTH IS: 180487
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
     
    Nick2005 likes this.
  3. Nick2005

    Nick2005 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi David,
    following Your suggestion I have this scenario:
    Code (SQL):
    CREATE TABLE ECLIPSE.STATES$
    (
      STATE_CODE           VARCHAR2(3 BYTE)         NOT NULL,
      STATE_31661C2        VARCHAR2(2 BYTE),
      STATE_31661C3        VARCHAR2(3 BYTE),
      STATE_31661N3        VARCHAR2(3 BYTE),
      STATE_31661NAME      VARCHAR2(50 BYTE),
      STATE_ALTNAME        VARCHAR2(200 BYTE),
      STATE_CAPITAL        VARCHAR2(50 BYTE),
      STATE_CEE            VARCHAR2(1 BYTE),
      STATE_REGION         VARCHAR2(50 BYTE),
      STATE_SUBREGION      VARCHAR2(50 BYTE),
      STATE_FLAG           BLOB,
      STATE_GPSLAT         VARCHAR2(10 BYTE),
      STATE_GPSLONG        VARCHAR2(10 BYTE),
      STATE_CREATED_DATE   DATE,
      STATE_CREATED_USER   VARCHAR2(40 BYTE),
      STATE_MODIFIED_DATE  DATE,
      STATE_MODIFIED_USER  VARCHAR2(40 BYTE)
    )
    The table is already filled with some value; for example these are the first 13 fields of the first record of the table:
    The field STATE_FLAG is empty.
    Now I have created this procedure:
    Code (SQL):
    CREATE OR REPLACE PROCEDURE ECLIPSE.UPDATE_BLOB
    AS
       vBlob    BLOB;
       vBfile   BFILE;
    BEGIN
       vBfile := BFILENAME ('FLAGS_DIR', 'AF.GIF');

       SELECT STATE_FLAG
         INTO vBlob
         FROM STATES$
        WHERE STATE_31661C2 = 'AF';

       DBMS_LOB.fileopen (vBfile);
       DBMS_LOB.loadfromfile (vBlob, vBfile, DBMS_LOB.getlength (vBfile));

       DBMS_LOB.fileclose (vBfile);
    END;
    /
    But when I execute the procedure in order to insert the image in the record, this error is returned :mad::
    Note that the FLAG_DIR exists on the server and the AF.GIF image is in the dir.
    Where am I going wrong?
    Can You help me
    Thank You
    Gaetano
     
  4. Nick2005

    Nick2005 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    I think that the problem is solved:)
    First, I have update the table with:
    Code (SQL):
    UPDATE STATES$ SET STATE_FLAG = EMPTY_BLOB();
    After I have modified the SELECT in the procedure in this way:
    Code (SQL):
       SELECT STATE_FLAG
         INTO vBlob
         FROM STATES$
        WHERE STATE_31661C2 = 'AF'
       FOR UPDATE;
    Executing the procedure all work fine.

    Is this correct ?
    Thank You
    Gaetano
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes. Notice the insert example in my response; after a LOB locator can be generated the update will succeed as you have seen.
     
  6. Nick2005

    Nick2005 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Re: Update BLOB [SOLVED]

    Tnank You David
    All work fine.
    Gaetano