CODEWITHSUNDEEP
oracle-databaseplsqlsql-updateblob
elusive
elusive

Reputation: 35

Oracle - Update BLOB with PL/SQL

I need to update a preexisting BLOB value in the table using PL/SQL.
I'm dealing with poor table design, the column should be CLOB and that is not going to change.

The steps I want to perform:

  1. select the BLOB
  2. convert the BLOB to CLOB
  3. modify the CLOB
  4. convert the CLOB to BLOB
  5. update the BLOB column with the new value

The 4th step I don't know how to do. I was hoping the BLOB could be updated directly but the only examples I find are reading a file into a blob column or using another programming language.

Upvotes: 3

Views: 32172

Answers (2)

Ahmad Nadeem
Ahmad Nadeem

Reputation: 2144

update table_name  set column_name = utl_raw.cast_to_raw ('value')  where id = 'your_id';

Upvotes: 4

kara
kara

Reputation: 3455

I understand your question, but i think there has to be another problem.

Solution

Just update it..

UPDATE myTable SET myBlobColumn = myBlob WHERE myCondition = 1;

Complete Example

DECLARE
    myVarcharVar   VARCHAR2(1000);
    myClobVar   CLOB;
    myBlobVar   BLOB;
BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE TTEST'; -- ensure out testdata will be correct.
    INSERT INTO TTEST (myBlob, myClob, myVarchar) VALUES(utl_raw.cast_to_raw('1111'), '2222', '3333'); -- insert our data

    -- Attention: ONLY ONE ROW => NO WHERE-CONDITIONS to simplify the code!

    SELECT myVarchar INTO myVarcharVar FROM TTEST;

    UPDATE TTEST SET myClob = myVarcharVar;

    SELECT myClob INTO myClobVar FROM TTEST;

    UPDATE TTest SET myBlob = utl_raw.cast_to_raw(myClobVar);

    SELECT myBlob, myClob, myVarchar INTO myBlobVar, myClobVar, myVarcharVar FROM TTest;

    dbms_output.put_line('Blob:   ' || utl_raw.cast_to_varchar2(myBlobVar)); 
    dbms_output.put_line('Clob:   ' || myClobVar); 
    dbms_output.put_line('Varchar:' || myVarcharVar); 
END;

Upvotes: 5

Related Questions