Update CLOB JSON column with case gives ORA-00932: inconsistent datatypes: expected CHAR got CLOB

user_5507535 0
Viewed 181

I have a CLOB column containing JSON, I want to update the values of the JSON using json_mergepatch. If the column is NULL then json_mergepatch doesn't work, so in this case I just want to add the new JSON as given.

I must use CLOB to avoid this issue: Oracle Update - JSON Merge Patch with more than 4000 characters

This works and updates the column:

UPDATE my_table SET data =
  json_mergepatch(data, '{ "user": "Tony" }' RETURNING CLOB)
where id = '1';

This gives ORA-00932: inconsistent datatypes: expected CHAR got CLOB:

UPDATE my_table SET data =
   case
      when (data is NULL) then '{ "user": "Tony" }'
      else json_mergepatch(data, '{ "user": "Tony" }' RETURNING CLOB)
   end
where id = '1';

Why doesn't it work with case? How can I fix it?

oracle19c clob oracle sql json
1 Answers

The problem is most probably in the fact, that the CASE returns a not expected data type.

Contrary to your claim, the json_mergepatch works fine also on a NULL JSON column, but the result remains NULL (which you do not expects).

Not sure is this is the most elegant solution, but it apparently works by changing the NULL to an empty JSON with NVL

UPDATE tab SET data =
  json_mergepatch(nvl(data,'{}'), '{ "user": "Tony" }' RETURNING CLOB)
where id = 1;
user_48081220
Related