如何使用Oracle 12C JSON_VALUE提取大于4000字节的JSON值？

Question

我可以翻译这段内容。这是关于编程的内容，主要讲述了一个包含大量JSON数据的CLOB表格，但是无法选择某些属性，因为它们的值超过了4000个字节。下面是需要翻译的文本：

我有一张表格，其中包含一个存储大型JSON负载的CLOB。然而，我无法选择某些属性，因为它们的值大于4000个字节。

例如，像这样的JSON：

{
    "foo": "some string smaller than 4k",
    "bar": "some string larger than 4k"
}

以下工作：

SELECT json_value(j, '$.foo' ERROR ON ERROR) FROM j;

以下代码会出现ORA-40478: output value too large (maximum:)错误：

SELECT json_value(j, '$.bar' ERROR ON ERROR) FROM j;

来自 this 12CR1 文档：

ORA-40478：输出值过大（最大值：string）

原因：提供的 JavaScript 对象符号 (JSON) 运算符生成的结果超出了 RETURN 子句中指定的最大长度。

操作：增加 RETURNING 子句中数据类型的最大大小，或在 RETURNING 子句中使用 CLOB 或 BLOB。

然而，使用 RETURNING 子句也会失败，并显示 ORA-40444: JSON 处理错误：

SELECT json_value(j, '$.bar' RETURNING CLOB ERROR ON ERROR) FROM j;

它在 PLSQL 期间也失败了。

DECLARE
    val CLOB;
BEGIN
    SELECT json_value(j, '$.bar' RETURNING CLOB ERROR ON ERROR) 
        INTO val 
    FROM j
 END;

- Matthew Moisen

---

json_value在其定义中的第一个参数受到4000个字符的限制。您尝试过使用json_table吗？ - access_granted

Answer 1

Json_value函数默认返回varchar2（4000），但如果您添加带有指定varchar2大小的返回子句，则可以强制其返回更长的值。

在您的示例中，以下查询应该有效：

SELECT json_value(j, '$.bar'  returning varchar2(32000) ERROR ON ERROR) FROM j;

注：在 Oracle 12c（SQL）中，如果你不设置 MAX_STRING_SIZE 参数为 EXTENDED，则 varchar2 字段的长度限制为 4000 字节。

如果设置了 MAX_STRING_SIZE = EXTENDED，则 varchar2 字段的长度限制为 32767 字节或字符。

- anytilia