6 Replies Latest reply: Oct 19, 2010 2:43 PM by User13019948-Oracle RSS

    update a blob column

    User13019948-Oracle
      Hi,
      I need to update a blob column with a value in another column but not sure of the syntax.

      I tried update table table_name set col= (select col from table_name where id =1)

      But this returns error "illegal use of long datatype"

      Can u please help me with the correct syntax.

      Thanks
      Deepak
        • 1. Re: update a blob column
          Saubhik
          Want to update a BLOB ? See this example
          SQL> CREATE TABLE myblob (id NUMBER(2), my_xls BLOB);
           
          Table created.
           
          SQL> CREATE OR REPLACE DIRECTORY TEST_DIR as 'C:\';
           
          Directory created.
          SQL> DECLARE
            2    v_src_loc BFILE := BFILENAME('TEST_DIR', 'saubhik.xls');
            3    v_amount  INTEGER;
            4    v_b       BLOB;
            5  BEGIN
            6    DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY);
            7    v_amount := DBMS_LOB.GETLENGTH(v_src_loc);
            8    INSERT INTO myblob VALUES (1, EMPTY_BLOB()) RETURNING my_xls INTO v_b;
            9    DBMS_LOB.LOADFROMFILE(v_b, v_src_loc, v_amount);
           10    DBMS_LOB.CLOSE(v_src_loc);
           11  END;
           12  / 
           
          PL/SQL procedure successfully completed.
          SQL> commit;
          • 2. Re: update a blob column
            792754
            why don't use
            UPDATE your_table
            SET blob_col_a = blob_col_b
            WHERE ...;
            or do you want to update from another table? this should work also, please post the entire code here.
            • 3. Re: update a blob column
              User13019948-Oracle
              Hi Thanks for the update.
              I do not wish to insert a value. I am updating an row's col. with a value of another row. Can u gv a code snippet for such a erquirement.

              Thanks Deepak
              • 4. Re: update a blob column
                User13019948-Oracle
                Hi All,
                This is what I am trying to do

                UPDATE BS_AP_COMPANY_INFOS_EXT SET LOGO = (SELECT LOGO FROM BS_AP_COMPANY_INFOS_EXT
                WHERE ACCOUNTING_BALANCING_SEGMENT = 001
                )
                • 5. Re: update a blob column
                  MichaelS
                  Updating blobs works the same as for any other datatype:
                  SQL> create table t1
                  as
                     select 1 id, to_blob (utl_raw.cast_to_raw ('Hello World')) bl from dual
                  /
                  Table created.
                  
                  SQL> create table t2
                  as
                     select 1 id, to_blob (utl_raw.cast_to_raw ('Again Hello World')) bl
                       from dual
                  /
                  Table created.
                  
                  SQL> update t1
                     set bl =
                            (select bl
                               from t2
                              where id = 1)
                   where id = 1
                  /
                  1 row updated.
                  
                  SQL> select * from t1
                  /
                               ID BL               
                  --------------- -----------------
                                1 Again Hello World
                  1 row selected.
                  But this returns error "illegal use of long datatype"
                  I suspect you not using a BLOB but a LONG datatype, right?
                  • 6. Re: update a blob column
                    User13019948-Oracle
                    Hi ,
                    Thaks for your reply. The datatype is Long Raw. Is the syntax to update it different.

                    Thanks