Oracle - Update BLOB with PL/SQL

Question

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:

select the BLOB
convert the BLOB to CLOB
modify the CLOB
convert the CLOB to BLOB
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.

kara · Accepted Answer · 2018-04-25 06:55:05Z

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;

Ahmad Nadeem · Accepted Answer · 2019-12-29 07:37:44Z

4

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

answered Dec 29, 2019 at 7:37

Ahmad Nadeem

2,1441 gold badge18 silver badges21 bronze badges

Collectives™ on Stack Overflow

Oracle - Update BLOB with PL/SQL

2 Answers 2

Solution

Complete Example

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Solution

Complete Example

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related