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

Last updated on JULY 20, 2024

Applies to:

Symptoms

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)

Changes

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.

Cause

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