Business requirement is to generate excel/csv BIP output of concatenated transaction notes (on oracle cloud ERP).
The identified source table is ZMM_NOTES, column NOTE_TXT (CLOB data type).
Development team is using the listagg() function to concatenate multiple "note" entries for specific customer, code snippet below.
FROM (SELECT arpsa.customer_trx_id
,(SELECT listagg(t1.note_txt, '||- ') within group (ORDER BY t1.creation_date)
FROM (SELECT note_txt
WHERE source_object_code IN ('AR_TRANSACTION')
AND source_object_uid = arpsa.customer_trx_id
) AS transaction_note
FROM ar_payment_schedules_all arpsa
WHERE arpsa.status = 'OP'
AND arpsa.customer_trx_id = 4800413
GROUP BY customer_trx_id
The following errors are being encountered:
- ORA-01489: result of string concatenation is too long
- ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1
It appears the concatenated string is then too long for an excel or cvs format.
Any solution perhaps we are not aware of?
LISTAGG only returns VARCHAR2, so the length of the string it returns must be less than the limit for this (4,000 or 32,767 bytes; depending on whether you've enabled extended data types).
It seems you're hitting this limit, so there are a few options to get around this.Use the ON OVERFLOW TRUNCATE clause
LISTAGG ( t1.note_txt, '||- ' ON OVERFLOW TRUNCATE )
This stops the error, but trims the string so the end is chopped off => you've lost some dataSplit the string over multiple rows
You can use MATCH_RECOGNIZE or MODEL to cap the maximum length of the string and duplicate the row if it exceeds this.
This looks something like:
SELECT ... FROM MATCH_RECOGNIZE (
PARTITION BY <group by cols>
ORDER BY <listagg order cols>
match_number() AS mho
ALL ROWS PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN ( init str )
str as lengthb ( init.note_txt ) +
lengthb ( str.note_txt ) +
lengthb( '||- ' )
) <= 4000
One pro of this is you can choose a limit smaller than 4,000 if you. Duplicating the rest of the columns may be a non-starter though.
For more on this method, see https://blogs.oracle.com/datawarehousing/post/managing-overflows-in-listagg Construct a JSON array instead
If you're on 18c or higher, you can return a JSON array as a CLOB, overcoming the VARCHAR2 limit:
note_txt ORDER BY t1.creation_date RETURNING CLOB
You'll then need to trim off the start/end brackets (). It looks like you're not creating a CSV, so you'll also need to replace the commas with your separator.
If the source column can contain commas it may not be possible to do this safely.
In which case you can do similar with XMLAGG (which also works on earlier versions).
Stew Ashton discusses this further at https://stewashton.wordpress.com/2019/08/19/making-longer-lists/