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

- 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

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/