My Oracle Support Banner

PL/SQL Json_object.parse Produces Wrong Results (Doc ID 2966395.1)

Last updated on OCTOBER 06, 2023

Applies to:

Oracle Database - Enterprise Edition - Version and later
Information in this document applies to any platform.


PL/SQL json_object.parse produces wrong results

After upgrading our test system from oracle 12c to oracle 19c
json_object.parse() produces json data where all string values are empty strings.

For example: json_object_t.parse('{"a":"b", "c":1, "d": ["e"]}').to_string() returns {"a":"","c":1,"d":[""]} instead of the expected {"a":"b", "c":1, "d": ["e"]}

The error only occurs after the same session tries to update or insert a CLOB into a column that
has an "is json" check contraint, using OCILobWrite(). It does not matter whether the DML operation succeeds or not,
as long as the check constraint is evaluated (i.e. even trying to insert a row with invalid JSON data will still trigger the error, but inserting NULL will not)


Customer using workaround in place where never treat any input data as CLOBs, but this limits the size of our JSON objects to 4k and cannot be used in production.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.