How to read and insert BLOB via a sql script?

+1 vote
asked May 24, 2018 by Hitesh Garg (799 points)  

I have to update a blob value in my project, usually it is done via a UI but UI is not working for now and I want to update the value so as to make the rest of the system up and running.
I am currently using Oracle 12 and sqldeveloper and I am unable to update the value

Is there any set of queries to do that?

1 Answer

+1 vote
answered May 24, 2018 by Rahul Singh (682 points)  
selected May 24, 2018 by Hitesh Garg
Best answer

This can be achieved by the inbuild methods -

  • For String to BLOB - RAWTOHEX

    create table blobex(id integer, item blob);
    insert into blob
    ex values(2, RAWTOHEX('Test'));

  • For BLOB to String - utlraw.castto_varchar2(dbms_lob.substr(COLUMN_NAME))

    select utlraw.casttovarchar2( dbmslob.substr( item ,4000,1)) from blob_ex where id=2;