Hi,
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.
SELECT customer_trx_id
,transaction_note
FROM (SELECT arpsa.customer_trx_id
,(SELECT listagg(t1.note_txt, '||- ') within group (ORDER BY t1.creation_date)
FROM (SELECT note_txt
,source_object_code
,source_object_uid
,creation_date
FROM zmm_notes
WHERE source_object_code IN ('AR_TRANSACTION')
AND source_object_uid = arpsa.customer_trx_id
) t1
) AS transaction_note
FROM ar_payment_schedules_all arpsa
WHERE arpsa.status = 'OP'
AND arpsa.customer_trx_id = 4800413
)
GROUP BY customer_trx_id
,transaction_note
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?
thanks!
Olga
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 clauseFor example:
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 data
Split the string over multiple rowsYou 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>
MEASURES
match_number() AS mho
ALL ROWS PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN ( init str )
DEFINE
str as lengthb ( init.note_txt ) +
SUM (
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 insteadIf you're on 18c or higher, you can return a JSON array as a CLOB, overcoming the VARCHAR2 limit:
JSON_ARRAYAGG (
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/