When a 4000-char limit breaks your REST call

Yesterday, a developer messaged me:

“What’s the max size of this VARCHAR2 column?”

Turns out, it was 4000 characters. But here’s where it gets interesting…

They were sending text longer than that to a PUT REST endpoint and receiving a response with status 'ok'—but the column ended up empty in the database.

Here’s a simplified version of the query in the procedure. In this example, the clan field has a 60-character value:

WITH characters AS (
  SELECT '{
    "name": "Naruto",
    "village": "Konoha",
    "rank": "Hokage"
    "clan" : "Kazehana-no-Tenshi-no-Kyōmei-no-Ketsueki-no-Ikari-Ryuu"
  }' AS character_json
  FROM dual
)
SELECT *
FROM characters,
     JSON_TABLE(
         character_json,
         '$'
         COLUMNS (
             name     VARCHAR2(50) PATH '$.name',
             village  VARCHAR2(50) PATH '$.village',
             rank     VARCHAR2(50) PATH '$.rank',
             clan     VARCHAR2(50) PATH '$.clan'
         )
     ) jt;        

The procedure logs showed that data was indeed being sent. But nothing was saved. Digging deeper, I added a log after the JSON_TABLE call—and much to my surprise: the data was missing.

What happened?

Oracle silently ignores JSON parsing errors by default. If a value is too large, the query fails silently and returns NULL. Not only is an error happening silently with an 'ok' message to the user, but the database is also updated to NULL.

The fix: ERROR ON ERROR

By adding ERROR ON ERROR, Oracle throws an ORA-40478 exception when the JSON value doesn’t fit. Now we can catch and respond with a proper error message:

clan     VARCHAR2(50) PATH '$.clan' error on error        

Satisfied with the fix I returned to working on other stories. But then I got another call...

Now the developer got an unhandled exception—this time when the clan field was simply missing from the input. The new error: ORA-40462: JSON_VALUE evaluated to no value.

WITH characters AS (
  SELECT '{
    "name": "Naruto",
    "village": "Konoha",
    "rank": "Hokage"
    -- Note: no "clan" field here
  }' AS character_json
  FROM dual
)
SELECT *
FROM characters,
     JSON_TABLE(
         character_json,
         '$'
         COLUMNS (
             name     VARCHAR2(50) PATH '$.name',
             village  VARCHAR2(50) PATH '$.village',
             rank     VARCHAR2(50) PATH '$.rank',
             clan     VARCHAR2(50) PATH '$.clan'
         )
     ) jt;        

That’s when I learned about another important clause: NULL ON EMPTY

By combining the two, we catch real issues without failing on missing optional fields:

clan     VARCHAR2(50) PATH '$.clan' error on error null on empty        

What do you think?

Was this the right fix?

How do you handle JSON parsing quirks in Oracle?

What other clauses or tricks do you use with JSON_TABLE?