0

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"

ksrider 148
  • 55
  • 1
  • 11
  • https://stackoverflow.com/questions/828650/how-do-i-get-textual-contents-from-blob-in-oracle-sql – go.. Sep 26 '18 at 13:02
  • If you can, avoid to store JSON data in one column, create a DB structure instead. – Mynsk Sep 26 '18 at 15:22
  • What is the DB structure? This is my requirement I want to store JSON into DB. – ksrider 148 Sep 27 '18 at 13:16
  • Possible duplicate of [How do I get textual contents from BLOB in Oracle SQL](https://stackoverflow.com/questions/828650/how-do-i-get-textual-contents-from-blob-in-oracle-sql) –  Oct 03 '18 at 13:59

2 Answers2

0
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';
Rnsthota
  • 11
  • 3
-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.

JOTN
  • 6,120
  • 2
  • 26
  • 31