Skip to Main Content
  • Questions
  • Oracle ERP SaaS BI Publisher - concatenating string too long

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Olga.

Asked: June 02, 2022 - 11:24 am UTC

Last updated: June 10, 2022 - 12:54 pm UTC

Version: Cloud ERP 22B

Viewed 100+ times

You Asked

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

and Chris said...

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

For 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 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>
 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 instead

If 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/

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.