I need to INSERT JSON data into the Oracle DB table BLOB column, also I need to SELECT the BLOB column value from the table?
This is my Query to INSERT:
INSERT INTO table_Name values('Test','test1',UTL_RAW.cast_to_raw ({"JSON data"}))
How can I SELECT? Also When inserting the large JSON object it's throwing Error : "PL/SQL: numeric or value error: raw variable length too long"
Try this:
create table demo
( id int primary key,
theBlob blob
);
INSERT INTO demo (id) values (1);
update demo set theBlob = utl_raw.cast_to_raw('Hello World') where id = 1;
commit;
select * from demo where id = 1;
select utl_raw.cast_to_varchar2(dbms_lob.substr(theBlob))
from demo
where ID = '1';
That cast_to_raw takes a VARCHAR2 as a parameter and your string is too large. You need to open the BLOB and write it in pieces. It's all in the DBMS_LOB package.
