Oracle SQL Developer 3.1.07 extra spaces between characters using listagg

Question

I am using SQL Developer 3.1.07 on an 11g database When I use listagg to extract multiple values from a field I get a space between each character in the results for the listagg column. The query returns all the values I expect to see, it's just the extra spaces that are driving me nuts. Any thoughts? Here is one query that I have used, but it happens everytime I use listagg in a query: ``` select a.personnum Emp_ID , a.personfullname Name , a.companyhiredtm Hire_Date , a.employmentstatus Status , a.employmentstatusdt Status_Date , h.Supervisor, h.Agency from vp_employeev42 a left outer join (select f.personid , listagg (g.personcstmdatatxt, ',') within group (order by g.customdatadefid) Supervisor from vp_employeev42 f left outer join personcstmdata g on f.personid = g.personid where f.personnum like 'T%' and f.homelaborlevelnm3 = '1872' and (g.customdatadefid = '1' or g.personcstmdatatxt is null) group by f.personid) h on a.personid = h.personid left outer join (select f.personid ,…

Accepted Answer

are you using `UTF-16` + `NVARCHAR2` by any chance? eg this:

```
SQL> select * from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16

SQL> drop table test;

Table dropped.

SQL> create table test(a nvarchar2(10));

Table created.

SQL> insert into test values ('test');

1 row created.

SQL> insert into test values ('test 2');

1 row created.

SQL> select listagg(a, ',') within group (order by 1) from test group by 1;

LISTAGG(A,',')WITHINGROUP(ORDERBY1)
--------------------------------------------------------------------------------
 t e s t, t e s t   2
```

you could cast to a char to get round this. IF this is not acceptible, you need to raise a ticket with Oracle support.

```
SQL> select listagg(to_char(a),',') within group (order by 1) from test group by 1;

LISTAGG(TO_CHAR(A),',')WITHINGROUP(ORDERBY1)
--------------------------------------------------------------------------------
test,test 2

SQL>
```

Oracle SQL Developer 3.1.07 extra spaces between characters using listagg

Solution

Problem