How to read and insert BLOB via a sql script?

Question

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?

Answer 1

This can be achieved by the inbuild methods -

• For String to BLOB - RAWTOHEX

  create table blobex(id integer, item blob);
  insert into blobex 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;