Oracle 12.1使用JSON_TABLE处理大型JSON文档报ORA-01460问题咨询

• Oracle 12.1（12cR1）版本的JSON函数原生实现存在两个已知局限，该问题在12.2版本已被官方修复：
  1. 隐式类型转换限制：JSON_TABLE返回FORMAT JSON的字段时，默认推导类型为VARCHAR2(4000)，当单个jevents节点的JSON字符串长度超过4000字节时，就会触发未实现的转换逻辑，抛出ORA-01460错误。
  2. CLOB输入处理BUG：12.1对绑定变量传入的CLOB类型JSON串，在嵌套路径（NESTED PATH）下的解析逻辑存在缺陷，内部会强制尝试将返回结果转为短字符串类型，超过长度阈值就报错。

方案1：显式指定返回字段为CLOB类型（推荐） #

直接修改JSON_TABLE的字段定义，将返回的JSON对象字段类型声明为CLOB即可适配大长度场景，无需修改数据库参数：

SELECT * FROM JSON_TABLE(:jsonstr,
                       '$' COLUMNS(NESTED PATH '$.jevents[*]'
                                    COLUMNS(  
                                            jsonobjstr  CLOB format json path '$'                                            
                                            )
                                  )
          );

方案2：显式指定VARCHAR2最大长度（仅适用于短字符串场景） #

如果确认所有jevents节点的JSON长度不超过32767字节，且你的数据库已经开启了MAX_STRING_SIZE = EXTENDED参数，可以显式指定VARCHAR2的长度：

SELECT * FROM JSON_TABLE(:jsonstr,
                       '$' COLUMNS(NESTED PATH '$.jevents[*]'
                                    COLUMNS(  
                                            jsonobjstr  VARCHAR2(32767) format json path '$'                                            
                                            )
                                  )
          );

方案3：兼容旧版本的替代写法 #

如果上述方案仍不生效，可以用JSON_VALUE配合XMLTABLE的写法绕开JSON_TABLE的嵌套路径限制，兼容性更高，仅性能略低于原生JSON_TABLE：

SELECT JSON_VALUE(:jsonstr, '$.jevents[' || (rownum-1) || ']' FORMAT JSON) AS jsonobjstr
FROM XMLTABLE(('1 to ' || JSON_VALUE(:jsonstr, '$.jevents.size()')));

内容的提问来源于stack exchange，提问作者Matteo S.