Oracle SQL Developer 3.1.07 extra spaces between characters using listagg

Question

I am using SQL Developer 3.1.07 for 11g database. When I use listagg to retrieve 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 ​​that I expect to see, these are just extra spaces that make me nuts. Any thoughts?

Here is one request that I used, but this happens every time I use listagg in the request:

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 , listagg (g.personcstmdatatxt, ',') within group (order by g.customdatadefid) Agency from vp_employeev42 f left outer join personcstmdata g on f.personid = g.personid where f.personnum like 'T%' and homelaborlevelnm3 = '1872' and (g.customdatadefid = '3' or g.personcstmdatatxt is null) group by f.personid) h on a.personid = h.personid where personnum like 'T%' and homelaborlevelnm3 = '1872' order by personnum; 

Here are the results I get:

 EMP_ID,NAME,HIRE_DATE,STATUS,STATUS_DATE,SUPERVISOR,AGENCY T98999,Lxxxxm, Lxxxn,20-SEP-12,Active,20-SEP-12,, S taffmark T98989,Fxxxxn, Dxxxxa,10-DEC-12,Active,10-DEC-12,, S taffmark T99989,Hxxxs, Cxxxxxa,02-OCT-12,Active,02-OCT-12,, S taffmark T99999,Hxxxs, Dxxxn,30-JAN-12,Terminated,21-MAY-12, C xxxxxxxxxr T xxxxr, PROLOGISTIX 

Answer 1

Are you using UTF-16 + NVARCHAR2 ? eg:

 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) -------------------------------------------------------------------------------- test, test 2 

you can use char to get around this. If this is not acceptable, 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> 

Answer 2

This is currently a known bug without a fix:

Error 13501087 11.2.0.3 RDBMS 11.2.0.3 SQL EXECUTOR PRODID-5 PORTID-226

Summary: LISTAGG RETURN STRANGE DATA

 *** 12/14/11 05:12 am *** (ADD: Impact/Symptom->WRONG RESULTS ) SubComponent: SQL Analytics =========================== DETAILED PROBLEM DESCRIPTION ============================ When using LISTAGG function with NVARCHAR , data is returned with spaces between characters 

Answer 3

In Oracle 12c, I had a problem with the LISTAGG function. I used ASCIISTR() to get around this:

 SELECT LISTAGG(TRIM(ASCIISTR(NVL(Name,''))), ';') WITHIN GROUP (ORDER BY NAME) AS Names 

Works well here.