Oracle listagg on long value turned into strange characters

Question

I have a VARCHAR2 column with the length set to 4000 CHAR. I inserted 1 row with 4000 Chinese characters, but when I run the SQL the result is very strange:

SELECT LISTAGG(VAL, ', ') WITHIN GROUP (ORDER BY SEQ) FROM TBL GROUP BY ID;

The result

The data is 一二三四五六七八九十 repeated 400 times to make 4000 characters. Since this ID has only 1 row I expect the exact same value as I inserted. The first part looks fine but after some characters (around 2000 I think) the characters turned into some strange characters.

The database version is 12c.

Answer 1

This most likely has to do with SQL Developer supporting multi-byte characters. I would try upgrading SQL Developer and the version of Java on your machine to the latest version and see if you are still having the issues. I am running SQL Developer 20.2 and Java 8 update 231 and am not seeing any issues when returning a large LISTAGG of the characters you specified.