In 126.96.36.199, I have this happy scenario against one of my 188.8.131.52 databases I’m prepping for an upgrade:
CREATE TABLE tester (col1 VARCHAR2(16), col2 NVARCHAR2(16));
INSERT INTO tester VALUES (‘one byte’,n’two bytes’);
SELECT LISTAGG(col1,’,’) WITHIN GROUP (ORDER BY 1) FROM tester;
SELECT LISTAGG(col2,’,’) WITHIN GROUP (ORDER BY 1) FROM tester;
The result of the first SELECT is correct, but the second one shows up as NULL in the grid. Yes, this is likely due to Oracle BUG 19461687, but the output should show the same-yet-incorrect results as SQL*Plus, with the extra space between each character of the “two bytes” data. If I had to guess, the leading zero-byte in the LISTAGG output is triggering the NULL (and why are the Oracle internal datatype codes for VARCHAR2 and NVARCHAR2 the same???).
Phew! If I had had hair before I started debugging my original LISTAGG that this example was cooked down from…well, I’d still be baldish-like, I guess. Oh well. [:)]