I created a table with two JSON columns.
CREATE TABLE USER.JSON_DOCS (
id RAW(16) NOT NULL,
file CLOB,
CONSTRAINT json_docs_pk PRIMARY KEY (id),
CONSTRAINT json_docs_chk CHECK (file IS JSON)
);
Then I tried to storage a JSON in that table (about 150,000 characters| 5k lines, too long to paste it here) through Toad for Oracle I got the following error: ORA-01704: string literal too long.
What is the right and best way to store a JSON file in an Oracle DB from a .NET app?
That looks like a Toad bug to me. If you keep the data types all clobs, then you'll be fine
SQL> CREATE TABLE JSON_DOCS (
2 j CLOB,
3 CONSTRAINT json_docs_chk CHECK (j IS JSON)
4 );
Table created.
SQL>
SQL> set serverout on
SQL> declare
2 c clob;
3 fragment clob :=
4 '{"ENAME":"SMITH"},
5 {"ENAME":"ALLEN"},
6 {"ENAME":"WARD"},
7 {"ENAME":"JONES"},
8 {"ENAME":"MARTIN"},
9 {"ENAME":"BLAKE"},
10 {"ENAME":"CLARK"},
11 {"ENAME":"SCOTT"},
12 {"ENAME":"KING"},
13 {"ENAME":"TURNER"},
14 {"ENAME":"ADAMS"},
15 {"ENAME":"JAMES"},
16 {"ENAME":"FORD"},
17 {"ENAME":"MILLER"},' ;
18 begin
19 c := '[';
20 for i in 1 .. 1000 loop
21 c := c||fragment;
22 end loop;
23 c := c||rtrim(fragment)||']';
24 dbms_output.put_line(dbms_lob.getlength(c));
25 insert into json_docs values ( c);
26 end;
27 /
265267
PL/SQL procedure successfully completed.
So make sure you are binding the .Net parameters as CLOB and you should good to go