Как создать JSON в Oracle для CLOB, который составляет> 32 тыс. (Например, 60 000 символов)?

1) Я должен сделать json из запроса выбора оракула, который может следовать за тремя подходами.

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

также я попытался с этим подходом

2) Если вы не можете исправлять/работать с этой версией, есть отличный пакет, написанный Льюисом Каннингемом и Джонасом Крогсбуэлем: PL/JSON * http://pljson.sourceforge.net/

Это отличный пакет (я использовал его в многочисленных установках базы данных).

Приведенные примеры хороши и охватывают большинство сценариев.

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

Упоминание В этом ответе тоже, но не работает для такого большого клоба. Возвращает результаты запроса sql как JSON в oracle 12c

3) Другой подход может заключаться в том, что мы можем конкатенировать строку после запроса select.

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 решить мою проблему, но я не хочу запускать цикл. Есть ли какое-либо решение в oracle, чтобы справиться с этим.

Я проверяю решение, но это не работает без цикла.

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)

Не могли бы вы рассказать мне, как это можно сделать?

Ответы

Ответ 1

В ответ на этот вопрос:

3 решить мою проблему, но я не хочу запускать цикл. Есть ли какое-либо решение в oracle, чтобы справиться с этим.

Строки могут быть объединены без циклов, используя Oracle LISTAGG function:

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 обрабатывает CLOB, но функция EXTRACT имеет побочный эффект экранирования определенных символов (например, от " до "). Вышеуказанный запрос преобразует их обратно (например, от " до ") с помощью функции dbms_xmlgen.convert - см. этот ответ для более подробной информации.

Демо-версия SQL Fiddle: http://sqlfiddle.com/#!4/5b295/40

Ответ 2

Вы можете сказать Oracle вернуть clob вместо varchar2 (4000):

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

Ответ 3

По умолчанию новые функции json_ * возвращают varchar2 (4000). Вы можете изменить это в возвращающем предложении.

Если у вас включены расширенные типы данных, вы можете изменить это на varchar2 (32767). Но только функции * agg поддерживают clob.

отсюда

SELECT length(JSON_ARRAYAGG( 
         JSON_OBJECT( 
           KEY 'object_type' VALUE object_type, 
           KEY 'object_name' VALUE object_name 
         ) 
       returning clob) 
       ) array_size
FROM   all_objects;

ARRAY_SIZE  
5772072

18c также имеет полную поддержку clobs в функциях JSON *

Ответ 4

В 12.2 функции json_ * отлично справляются с сабами. Использовать пункт "Возврат clob"

create table t( c clob, constraint t_chk check (c is json));
declare
    v_clob clob;
begin
    for i in 1..10000 loop
        v_clob := v_clob || 'asdasdadasdasdasdasdasdasdasd';
    end loop;

    insert into t(c) 
    select 
        json_object
        (
           'body' value v_clob returning clob
         )
    from
        dual;
end;