Search code examples
jsonmergeoracle19c

Oracle Update - JSON Merge Patch with more than 4000 characters

I'm trying to update JSON value to a column (CLOB data type) in an oracle table. The JSON value is more than 4000 characters and the number of characters is dynamic based on the data received from the application. JSON_MERGEPATCH is used to merging the value to the existing JSON content already present in the table.

I'm using java as the programming and doing a batch update (ojdbc8 jar) to write updates to oracle table. I notice that while using JSON_MERGEPATCH when the number of characters exceeds 40000, the CLOB is updated to NULL and I lose the pre-existing data in the table. Is there anyway to resolve this issue and merge the new fields to the existing JSON?

Solution

  • The syntax for JSON_MERGEPATCH is in the documentation:

    JSON_MERGEPATCH syntax

    If you do not include the "returning clause" then JSON_MERGEPATCH will default to returning a VARCHAR2 value and if this exceeds 4000 bytes then the default "on error clause" will be applied which is NULL ON ERROR.

    If you want to return a CLOB value then you need to specify the "returning clause":

    UPDATE table_name
SET   value = JSON_MERGEPATCH(value, :newvalue RETURNING CLOB)
WHERE id = 1;

    db<>fiddle here