I recently worked on a fun project involving JSON and Oracle Database 23ai. I had to store rather large JSON documents, individual field lengths exceeding what I usually see. No problem with that, the database didn’t even so much as flinch. However, when I queried the table I used to store the JSON documents using JSON_TABLE I got NULL back for the very-long-field’s value. Even though I knew I stored the entire document. What gives?
TL;DR
When working with large JSON documents you should consider the length of each individual field, 4000 bytes are often the magic default. You can use a session-modifiable initialisation parameter, JSON_BEHAVIOUR, to force an error to be thrown in case the field length exceeds this limit, or specify additional attributes in JSON_TABLE like error on error and each column’s data type. These mostly apply to other JSON_% functions as well.
Doc links are embedded in the following paragraphs.
Example
When encountering small issues like this one, creating a small, reproducible test case is key. I’m using Oracle Database 23ai Free 23.9 on Linux x86-64 by the way.
The JSON document mentioned in the introduction is returned by a REST API. I really like the In-Database JavaScript fetch() API (documented on GitHub), it’s the perfect tool to interact with REST endpoints. It’s so simple to use! Here’s the simplified test case consisting of
- The table to store the JSON
- The procedure inserting the JSON into the table (line 11 is veeeery long!)
CREATE TABLE t1 (
id NUMBER
GENERATED ALWAYS AS IDENTITY
PRIMARY KEY,
data JSON NOT NULL
);
create or replace procedure insert_json
as mle language javascript
{{
const input = { 'a': 1, 'b': 'some extra text, but shorter', 'c':'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' };
session.execute(
'insert into t1 (data) values (:data)',
{ data: { dir: oracledb.BIND_IN, val: input, type: oracledb.DB_TYPE_JSON } }
);
}};
/
In a nutshell I simulate the JSON I received from the REST API, and insert it into a table. Let’s run the example in the database:
SQL> select
2 count(*)
3 from
4* t1;
COUNT(*)
___________
0
SQL> exec insert_json;
PL/SQL procedure successfully completed.
SQL> select
2 count(*)
3 from
4* t1;
COUNT(*)
___________
1
It does what it says on the tin, a single row has been inserted. The problem I ran into is related to the query, as shown here:
SQL> select
2 jt.*
3 from
4 t1,
5 json_table(
6 t1.data,
7 '$'
8 columns (
9 a,
10 b,
11 c
12 )
13* ) jt;
A B C
____ _______________________________ ____
1 some extra text, but shorter
That’s interesting – nothing showing up for c. Let’s try the simple dot notation:
SQL> select
2 t.data.c
3 from
4* t1 t;
C
____________________________________________________________________________________
"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
Right, so the string has been stored, but … something is odd, it’s not returned in its entirety. And this is where it really pays off to read the documentation.
When the return data type is VARCHAR2(4000), if the selected value exceeds 4000 bytes then NULL is returned by default. To raise an error instead, you can use parameter JSON_BEHAVIOR:
ALTER SESSION SET JSON_BEHAVIOR="ON_ERROR:ERROR";
Hmmmm! So it was a PEBKAC error…
The Fix
This is when it dawned on me that I needed to add some extra details to JSON_QUERY. First of all, I can raise an error if the output doesn’t fit into 4000 characters:
SQL> select
2 jt.*
3 from
4 t1,
5 json_table(
6 t.data,
7 '$'
8 error on error
9 columns (
10 a,
11 b,
12 c
13 )
14* ) jt;
Error starting at line : 1 in command -
select
jt.*
from
t1,
json_table(
t1.data,
'$'
error on error
columns (
a,
b,
c
)
) jt
Error at Command Line : 4 Column : 5
Error report -
SQL Error: ORA-40478: output value too large (maximum: 4000)
https://docs.oracle.com/error-help/db/ora-40478/40478. 00000 - "output value too large (maximum: %s)"
*Cause: The provided JavaScript Object Notation (JSON) operator generated a
result which exceeds the maximum length specified in the RETURN
clause.
*Action: Increase the maximum size of the data type in the RETURNING clause
or use a CLOB or BLOB in the RETURNING clause.
More Details :
https://docs.oracle.com/error-help/db/ora-40478/
But that doesn’t solve my problem – I want all the characters from c. That’s simple enough to do – just specify the output type to be a CLOB:
SQL> select
2 jt.*
3 from
4 t1,
5 json_table(
6 t1.data,
7 '$'
8 columns (
9 a,
10 b,
11 c clob
12 )
13* ) jt;
A B C
____ _______________________________ ___________________________________________________________________________________
1 some extra text, but shorter aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
Rest assured that all the characters are returned, I hadn’t modified my SQLcl session though to properly display them all.
Summary
JSON is awesome, and Oracle Database gives you a great many ways to work with it effectively. As with every technology you should invest some time into writing unit tests and making sure you know your data. Inadvertently inserting NULL might not be in your best interest, it surely wasn’t in mine.
Happy coding!
Martins Blog 