How to read and insert BLOB via a sql script?

+1 vote
10 views
asked May 25 by Hitesh Garg (343 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 25 by Rahul Singh (518 points)  
selected May 25 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;

Welcome to Codingeek Q&A, where you can ask questions and receive answers from other members of the community.
...