How to extract a json value greater than 4000 bytes with Oracle 12C JSON_VALUE?

Question

I have a table with a CLOB that stores a big JSON payload. I cannot however select certain attributes whose value is greater than 4000 bytes.

For example, take a json like this:

{
    "foo": "some string smaller than 4k",
    "bar": "some string larger than 4k"
}

The following works:

SELECT json_value(j, '$.foo' ERROR ON ERROR) FROM j;

The following fails with ORA-40478: output value too large (maximum:):

SELECT json_value(j, '$.bar' ERROR ON ERROR) FROM j;

From this 12CR1 documentation:

ORA-40478: output value too large (maximum: string)

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.

However, using the RETURNING clause fails as well, with ORA-40444: JSON processing error:

SELECT json_value(j, '$.bar' RETURNING CLOB ERROR ON ERROR) FROM j;

It also fails during PLSQL

DECLARE
    val CLOB;
BEGIN
    SELECT json_value(j, '$.bar' RETURNING CLOB ERROR ON ERROR) 
        INTO val 
    FROM j
 END;

Answer 1

Json_value function by default will return varchar2(4000), but if you add returning clause with specified varchar2 size you can force it to return much longer values.

In your example the following query should works:

SELECT json_value(j, '$.bar'  returning varchar2(32000) ERROR ON ERROR) FROM j;

PS. In Oracle 12c (SQL) varchar2 is limited to 4000 bytes unless you set MAX_STRING_SIZE parameter to EXTENDED.

32767 bytes or characters if MAX_STRING_SIZE = EXTENDED