JSON_MERGEPATCH is sneaky

The Oracle JSON functions are very useful for generating JSON from a query, and developing using these functions requires understanding the limitations of the string data types they return.

Unless otherwise specified, they return a VARCHAR2 with a maximum of 4000 bytes. If your query might return more than this, you must either specify a larger length, e.g. RETURNING VARCHAR2(32767), or request a CLOB, e.g. RETURNING CLOB.

If the data exceeds the limit, calls to JSON_OBJECT, JSON_OBJECTAGG, JSON_ARRAYAGG, and JSON_TRANSFORM will fail at runtime with the following exception:

select
    json_object(
        'name-is-twenty-chars' : rpad('x',3974,'x')
    )
from dual;

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

The error occurs here because the representation of the entire JSON object requires more than 4000 bytes. No-one likes to see errors, but it’s better than the alternative because it is more likely to alert you to the problem so you can fix it.

You may have noticed I missed one of the JSON functions from the list above – JSON_MERGEPATCH. By default, this function does not raise an exception if the size limit is exceeded. Instead, it merely returns NULL at runtime. This behaviour can cause confusion when debugging a complex query, so it’s something to be aware of.

select
    json_mergepatch(
        json_object(
            'part1' : rpad('x',3973,'x')
            returning clob
        ),
        json_object(
            'part2' : rpad('x',3973,'x')
            returning clob
        )
    )
from dual;

(NULL)

Note that even though both the JSON objects specified RETURNING CLOB, this was missed for JSON_MERGEPATCH; which means it is limited to the default 4000 bytes, causing it to return NULL. The fix is to add RETURNING CLOB to the JSON_MERGEPATCH:

select
    json_mergepatch(
        json_object(
            'part1' : rpad('x',3973,'x')
            returning clob
        ),
        json_object(
            'part2' : rpad('x',3973,'x')
            returning clob
        )
        returning clob
    )
from dual;

{"part1":"xxx...xxx","part2":"xxx...xxx"}

If you don’t like this option, there are others. Refer to the links below for information about the TRUNCATE and ERROR ON ERROR clauses.

JSON_MERGEPATCH is sneaky

Further Reading

About Jeffrey Kemp

Leave a Reply Cancel reply