Ограничьте функцию listagg до первых 4000 символов

У меня есть запрос, который использует функцию listagg для получения всех строк в виде строки с разделителями-запятыми, которая в конечном итоге будет отправлена ​​​​в большое текстовое поле. Я получаю следующее исключение:

ORA-01489: result of string concatenation is too long

Я знаю, что проблема в том, что запрос, выполняемый для агрегирования данных, возвращает так много строк, что конкатенация строк, которую выполняет listagg, нарушает ограничение в 4000 символов. Однако для моего варианта использования вполне приемлемо урезать до первых 4000 символов.

Как изменить этот пример запроса из здесь, чтобы ограничить столбец "значение" значением быть не более 4000 символов?

SELECT LISTAGG(product_name, ', ') WITHIN GROUP( ORDER BY product_name DESC) "Product_Listing" FROM products

Вы не можете обернуть substr вокруг вызова listagg' becauselistaggthrows the exception beforesubstr`, когда-либо вызываемого.

Я видел много вопросов на SO о том, как обойти ограничение в 4000 символов, но не ограничивать результирующее значение.


sql oracle string-concatenation oracle11g varchar2
person spots    schedule 03.02.2015    source источник

Ответы (1)


arrow_upward
20
arrow_downward

12.2 и выше

Опция ON OVERFLOW позволяет легко обрабатывать более 4000 символов:

select listagg(product_name, ',' on overflow truncate) within group (order by product_name)
from products;

11.2 to 12.1

Аналитическая функция может генерировать промежуточную общую длину агрегации строк. Затем встроенное представление может удалить любые значения, длина которых превышает 4000.

В реальном запросе вам может понадобиться добавить partition by к аналитическим функциям, чтобы считать только по какой-то группе.

--The first 4000 characters of PRODUCT_NAME.
select
    --Save a little space for a ' ...' to imply that there is more data not shown.
    case when max(total_length) > 3996 then
        listagg(product_name, ', ') within group (order by product_name)||
            ' ...'
    else
        listagg(product_name, ', ') within group (order by product_name)
    end product_names
from
(
    --Get names and count lengths.
    select
        product_name,
        --Add 2 for delimiters.
        sum(length(product_name) + 2) over (order by product_name) running_length,
        sum(length(product_name) + 2) over () total_length
    from products
    order by product_name
)
where running_length <= 3996

Вот скрипт SQL, демонстрирующий запрос.

person Jon Heller    schedule 04.02.2015