sql - 如何在 Oracle 中为大于 32k(例如 60,000 个字符)的 CLOB 生成 JSON？

Question

1) 我必须从 oracle select 查询中生成 json，它有三种我可以遵循的方法。

SELECT JSON_ARRAY(json_object('id'         VALUE employee_id, 
                   'data_clob'    VALUE data_clob
                     )) from tablename;

我也尝试过这种方法

2) 如果您无法修补/使用该版本，则可以使用 Lewis Cunningham 和 Jonas Krogsboell 编写的优秀软件包:PL/JSON * http://pljson.sourceforge.net/

这是一个很好的包(我在许多数据库安装中使用过它)。

包含的示例很好，涵盖了大多数场景。

declare 
  ret json;
begin
  ret := json_dyn.executeObject('select * from tab');
  ret.print;
end;
/

在这个答案中也提到但不适用于如此大的clob。
Return results of a sql query as JSON in oracle 12c

3) 另一种方法可以是我们可以在选择查询之后连接字符串。

FOR rec IN (SELECT employee_id, data_clob
                FROM tablename) LOOP
      IF i <> 1 THEN
        v_result := v_result || ',';
      END IF;

      v_result := v_result || '{"employee_id":' || to_char(rec.employee_id) || ',"data_clob": ' || rec.data_clob || '}';

      i := i + 1;
    END LOOP;
    v_result := v_result || ']}'; 

3 种方法解决了我的问题，但我不想运行 for 循环 . oracle 中是否有任何解决方案来处理这个问题。

我检查了解决方案，但没有 for 循环就行不通。

https://technology.amis.nl/2015/03/13/using-an-aggregation-function-to-query-a-json-string-straight-from-sql/

url 提供了一些解决方案，我试过这个但没有工作。同样的问题即将到来。

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 57416, maximum: 4000)

你能告诉我怎么做吗？

Answer 1

回答这个问题:

3 approach solve my problem but i don't want to run for loop . Is there is any solution in oracle to handle this .

可以使用 Oracle 的 LISTAGG 连接字符串而无需循环。功能:

SELECT '{"employees":[' || LISTAGG('{"employee_id":' || to_char(employee_id)
                      || ',"data_clob":"' || data_clob || '"}', ',')
              WITHIN GROUP (ORDER BY employee_id) || ']}' AS json
FROM tablename;

但是，正如您在评论中指出的那样， LISTAGG限制为 4000 个字符。以下内容更复杂/繁琐，但应超出此限制:

SELECT '{"employees":[' || dbms_xmlgen.convert(
         RTRIM(XMLAGG(XMLELEMENT(E,'{"employee_id":' || to_char(employee_id)
                                 || ',"data_clob":"' || data_clob || '"}',',')
                      .EXTRACT('//text()') ORDER BY employee_id).GetClobVal(),',')
       , 1) || ']}' AS json
FROM tablename;

XMLAGG Handlebars CLOB s 但 EXTRACT 函数具有转义某些字符的副作用(例如从 " 到 " )。上面的查询使用 " 将这些转换回(例如从 " 到 dbms_xmlgen.convert )功能 - 见 this answer了解更多详情。

SQL Fiddle 演示: http://sqlfiddle.com/#!4/5b295/40