Oracle APEX中执行带returning clob的json_mergepatch语句失败如何解决

你遇到的问题是Oracle APEX资源模板的SQL解析器对JSON_MERGEPATCH函数的returning子句支持存在兼容性问题，才会出现无提示报错失效的情况。以下两种方案均可解决该问题：

方案1：去掉returning clob参数，显式转换入参为CLOB #

该方案无需保留returning clob参数，通过将所有入参显式转为CLOB类型，让JSON_MERGEPATCH默认返回CLOB类型结果，即可绕过4000字符限制，同时适配APEX的SQL解析规则。
改写后的SQL如下：

update
  my_table
set
  my_col = json_mergepatch(
    nvl(to_clob(my_col), to_clob('{}')), 
    to_clob(:patch)
  )
where
  id = '1';

原理：JSON_MERGEPATCH的返回值类型由入参类型决定，只要两个入参都是CLOB类型，函数默认返回CLOB，不需要额外加returning clob声明。

方案2：使用PL/SQL匿名块封装更新逻辑 #

如果方案1不符合你的业务场景需要保留returning子句，可以将SQL逻辑封装在PL/SQL匿名块中，APEX资源模板对PL/SQL块的语法支持更完整，不会出现解析异常：

declare
  l_patch_clob clob := :patch;
begin
  update
    my_table
  set
    my_col = json_mergepatch(
      nvl(my_col, '{}'), 
      l_patch_clob 
      returning clob
    )
  where
    id = '1';
end;

注意：请提前确认my_col字段的类型为CLOB（或带IS JSON约束的CLOB），如果字段为VARCHAR2类型，即使SQL逻辑正确，也无法存储超过4000字符的JSON内容。

内容的提问来源于stack exchange，提问作者user5507535