LISTAGG output of NVARCHAR2 column shows as NULL

Toad for Oracle - Beta
1

Howdy,

In 12.12.0.29, I have this happy scenario against one of my 11.2.0.3 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’);
COMMIT;
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. [:)]

Thanks!
Rich

2

Hey Rich,

I put a DUMP around your 2nd statement, so it looks like this:

SELECT dump(LISTAGG(col2,’,’) WITHIN GROUP (ORDER BY 1)) FROM tester;

and the result was

Typ=1 Len=18: 0,116,0,119,0,111,0,32,0,98,0,121,0,116,0,101,0,115

I think it’s that Zero in the first spot that’s making Toad think it’s a null. Not sure if we’ll be able to do anything about that…

3

here’s another interesting tidbit:

If I do a CTRL+F9 on your 2nd LISTAGG query, I see that it’s a VARCHAR2 instead of NVARCHAR2.

I came up with this after googing LISTAGG NVARCHAR2:

select utl_raw.cast_to_nvarchar2(listagg(utl_raw.cast_to_raw(col2), utl_raw.cast_to_raw(N’,’)) within group (order by 1))
from tester;

and it gives the desired result

4

Or just “LISTAGG(TO_CHAR(col2),’,’)…” works as well, aside from any (un)expected translation issues. Not sure of the difference between the two workarounds…

I wasn’t sure if there was a feasible fix. If anything, I thought I’d mention it so in a month when it happens again, this thread will pop up on my search, I’ll scratch my head wondering if that was really me that posted, then go on my happy care-free way with the workaround.

Thanks, John!
Rich

5

I didn’t test for translation problems, but according to this post on stack exchange, casting to raw and back will prevent them.

https://dba.stackexchange.com/questions/129834/aggregate-column-of-nvarchar2-and-obtain-result-in-nvarchar2-too

6

I know this is an old discussion, but I wanted to throw cast into the mix of solutions.

 LISTAGG (cast(inv as varchar2(24)) , ',') WITHIN GROUP (ORDER BY inv)     invs