Is there a way to continue on the next row after listagg() runs out of space (>4k)?

Question

I would like to change a table that has a number of values per week, to having all values in one row per week. But when I use listagg() I run out of space. I can't change the maximum string size (ALTER SYSTEM SET max_string_size=extended SCOPE= SPFILE), and I don't want to truncate the resulting string. However I could just continue on the next line, but I'm not sure how to. For example, if my input is:

Week                   SKU
202001                 598441
202001                 846541
202001                 77557
202001                 57813
202001                 5741651
202001                 21684135
202001                 5451516
202001                 545129
202001                 98754123
202001                 5644242
202002                 68454155
...

Assuming the input file is long enough that it will trip the error :

01489. 00000 -  "result of string concatenation is too long"
*Cause:    String concatenation result is more than the maximum size.
*Action:   Make sure that the result is less than the maximum size.

Then I want a piece of code similar to this:

select 
  weeknr, 
  ''''||listagg(sku, ''',''', ON OVERFLOW NEXT LINE) within group(order by weeknr)||'''' sku_numbers
from 
  input_table
group by 
  weeknr

So that my output will look like this:

Week                   SKU
202001                 '598441','846541','77557','57813','5741651','21684135'
202001                 '5451516','545129','98754123','5644242'
202002                 '68454155',...
...

Is that possible? Any help is greatly appreciated!

Answer 1

You can use the analytical function to find the SUM of the length of the strings and then use it for sub-grouping as follows:

SELECT WEEKNR,
       '''' || LISTAGG(SKU, ''',''') WITHIN GROUP(ORDER BY WEEKNR) || '''' SKU_NUMBERS
  FROM (
    SELECT WEEKNR,
           FLOOR(SUM(LENGTH(SKU) + 3) 
                   OVER(PARTITION BY WEEKNR ORDER BY SKU) / 4000) AS GROUP_NUMBER,
           SKU
      FROM INPUT_TABLE
)
 GROUP BY WEEKNR, GROUP_NUMBER