ORACLE SQL LISTAGG not returning expected result

I am running Oracle Database 11g Enterprise 11.2.0.4.0, PL/SQL Release 11.2.0.4.0 and I am having some some issues getting the LISTAGG function to return the dataset I am expecting.

Here is the scenario:

I have a data set that looks like this

I want to return the values in the MOD_CODE column in a one column on one row e.g.:

AR4001,AR4002

I have been trying to complete this using the LISTAGG function e.g:

SELECT LISTAGG(MOD_CODE,',') WITHIN GROUP (ORDER BY MOD_CODE) 
FROM XOTEST_A
WHERE MOD_CODE IN ('AR4001','AR4002')

But it is not returning any data (see screenshot)

What am I doing wrong? This is part of a wider scenario so I ideally want to use LISTAGG or similar (I tried using the wm_concat function, but its returning HUGECLOB data type and also not working)

I have written the SQL to build the scenario for repro

 -- CREATE TABLE 
    CREATE TABLE XOTEST_A (
      MOD_CODE   NVARCHAR2(12),
      DOM_CODE   NVARCHAR2(12),
      MOD_SNAM   NVARCHAR2(15),
      MOD_NAME   NVARCHAR2(120),
      SCH_CODE   NVARCHAR2(6),
      LEV_CODE   NVARCHAR2(6),
      PRS_CODE   NVARCHAR2(12),
      MOT_CODE   NVARCHAR2(6),
      MOD_CRDT   NUMBER(5,2),
      MOD_HOURS  NUMBER(4),
      MAP_CODE   NVARCHAR2(12),
      MOD_EREF   NVARCHAR2(12),
      MKS_CODE   NVARCHAR2(6),
      FPT_CODE   NVARCHAR2(12),
      ESB_CODE   NVARCHAR2(6),
      MOD_NPER   NUMBER(2),
      MOD_MOAS   NVARCHAR2(1),
      MOD_PMOA   NVARCHAR2(1),
      MEP_CODE   NVARCHAR2(6),
      DPT_CODE   NVARCHAR2(12),
      ELV_CODE   NVARCHAR2(3),
      ECL_CODE   NVARCHAR2(3),
      MOD_UTYC   NVARCHAR2(3),
      MOD_COGC   NVARCHAR2(6),
      MOD_STAT   VARCHAR2(1 BYTE),
      MOD_UPDD   DATE,
      MOD_KEYW   NVARCHAR2(100),
      MOD_IUSE   VARCHAR2(1 BYTE),
      MOD_TOCC   NVARCHAR2(12),
      MOD_DVNC   NVARCHAR2(12),
      MOD_UDF1   NVARCHAR2(15),
      MOD_UDF2   NVARCHAR2(15),
      MOD_UDF3   NVARCHAR2(15),
      MOD_UDF4   NVARCHAR2(15),
      MOD_UDF5   NVARCHAR2(15),
      MOD_UDF6   NVARCHAR2(15),
      MOD_UDF7   NVARCHAR2(15),
      MOD_UDF8   NVARCHAR2(15),
      MOD_UDF9   NVARCHAR2(15),
      MOD_UDFA   NVARCHAR2(15),
      MOD_UDFB   NVARCHAR2(15),
      MOD_UDFC   NVARCHAR2(15),
      MOD_UDFD   NVARCHAR2(15),
      MOD_UDFE   NVARCHAR2(15),
      MOD_UDFF   NVARCHAR2(15),
      MOD_UDFG   NVARCHAR2(15),
      MOD_UDFH   NVARCHAR2(15),
      MOD_UDFI   NVARCHAR2(15),
      MOD_UDFJ   NVARCHAR2(100),
      MOD_UDFK   NVARCHAR2(100),
      MOD_VALC   NVARCHAR2(12),
      MOD_APRT   NUMBER(5,2),
      MOD_LANG   NVARCHAR2(1),
      MOD_ERFM   NVARCHAR2(1),
      MOD_MUSE   VARCHAR2(1 BYTE),
      MOD_FACC   NVARCHAR2(6),
      MOD_ISGX   VARCHAR2(1 BYTE),
      MOD_REGM   NVARCHAR2(1),
      MOD_FRID   VARCHAR2(1 BYTE),
      MOD_WEEI   NVARCHAR2(1),
      MOD_MODE   NVARCHAR2(1)--,
      --MOD_NOTE   NCLOB
      )
    --INSERT VALUES INTO THE TABLE  
    INSERT INTO XOTEST_A (MOD_CODE,DOM_CODE,MOD_SNAM,MOD_NAME,SCH_CODE,LEV_CODE,PRS_CODE,MOT_CODE,MOD_CRDT,MOD_HOURS,MAP_CODE,MOD_EREF,MKS_CODE,FPT_CODE,ESB_CODE,MOD_NPER,MOD_MOAS,MOD_PMOA,MEP_CODE,DPT_CODE,ELV_CODE,ECL_CODE,MOD_UTYC,MOD_COGC,MOD_STAT,MOD_UPDD,MOD_KEYW,MOD_IUSE,MOD_TOCC,MOD_DVNC,MOD_UDF1,MOD_UDF2,MOD_UDF3,MOD_UDF4,MOD_UDF5,MOD_UDF6,MOD_UDF7,MOD_UDF8,MOD_UDF9,MOD_UDFA,MOD_UDFB,MOD_UDFC,MOD_UDFD,MOD_UDFE,MOD_UDFF,MOD_UDFG,MOD_UDFH,MOD_UDFI,MOD_UDFJ,MOD_UDFK,MOD_VALC,MOD_APRT,MOD_LANG,MOD_ERFM,MOD_MUSE,MOD_FACC,MOD_ISGX,MOD_REGM,MOD_FRID,MOD_WEEI,MOD_MODE/*,MOD_NOTE*/)
    VALUES ('AR4001',   'SEN',      'AR4001',   'DESIGN STUDIO 1A', 'UL',       '1',        'HASGRA1',  'A',        15,         NULL,       'AR4001',   NULL,       'AMOD',     NULL,       NULL,       1,          'M',        'M',        NULL,       'SENDES',   NULL,       NULL,       'F',        NULL,       NULL,       NULL,       NULL,       'Y',        NULL,       NULL,       NULL,       '15/05/2008',   'V',        'P',        NULL,       'N',        NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       'N',        NULL,       NULL,       NULL,       NULL,       NULL,       NULL/*,     ''*/)
    COMMIT;
    INSERT INTO XOTEST_A (MOD_CODE,DOM_CODE,MOD_SNAM,MOD_NAME,SCH_CODE,LEV_CODE,PRS_CODE,MOT_CODE,MOD_CRDT,MOD_HOURS,MAP_CODE,MOD_EREF,MKS_CODE,FPT_CODE,ESB_CODE,MOD_NPER,MOD_MOAS,MOD_PMOA,MEP_CODE,DPT_CODE,ELV_CODE,ECL_CODE,MOD_UTYC,MOD_COGC,MOD_STAT,MOD_UPDD,MOD_KEYW,MOD_IUSE,MOD_TOCC,MOD_DVNC,MOD_UDF1,MOD_UDF2,MOD_UDF3,MOD_UDF4,MOD_UDF5,MOD_UDF6,MOD_UDF7,MOD_UDF8,MOD_UDF9,MOD_UDFA,MOD_UDFB,MOD_UDFC,MOD_UDFD,MOD_UDFE,MOD_UDFF,MOD_UDFG,MOD_UDFH,MOD_UDFI,MOD_UDFJ,MOD_UDFK,MOD_VALC,MOD_APRT,MOD_LANG,MOD_ERFM,MOD_MUSE,MOD_FACC,MOD_ISGX,MOD_REGM,MOD_FRID,MOD_WEEI,MOD_MODE/*,MOD_NOTE*/)
    VALUES ('AR4002',   'SEN',      'AR4002',   'DESIGN STUDIO 1A', 'UL',       '1',        'HASGRA1',  'A',        15,         NULL,       'AR4002',   NULL,       'AMOD',     NULL,       NULL,       1,          'M',        'M',        NULL,       'SENDES',   NULL,       NULL,       'F',        NULL,       NULL,       NULL,       NULL,       'Y',        NULL,       NULL,       NULL,       '15/05/2008',   'V',        'P',        NULL,       'N',        NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       NULL,       'N',        NULL,       NULL,       NULL,       NULL,       NULL,       NULL/*,     ''*/)
    COMMIT;

    -- NOW RUN THE SELECT STATEMENT

    SELECT LISTAGG(MOD_CODE,',') WITHIN GROUP (ORDER BY MOD_CODE) LISTAGG_OUTPUT
    FROM XOTEST_A
    WHERE MOD_CODE IN ('AR4001','AR4002')

回答1:

This seems to be related to bug 19461687, and this previous question. If you dump the aggregated value from your query in 11gR2 or 12cR1 you see:

LISTAGG_OUTPUT
--------------------------------------------------------------------------------------------------
Typ=1 Len=25 CharacterSet=AL32UTF8: 0,41,0,52,0,34,0,30,0,30,0,31,2c,0,41,0,52,0,34,0,30,0,30,0,32

In SQL*Plus and SQL Developer the actual value is displayed as:

LISTAGG_OUTPUT
----------------------------------------
 A R 4 0 0 1, A R 4 0 0 2

and you can't copy the value from SQL Developer. (In 12cR2 the zeros no longer appear in the dump, the value is displayed without the spacing, and you can copy it, so the bug seems to have been fixed.)

Those null bytes seem to be causing Toad to not display the value at all, presumably because it sees the first null byte and treats it as a string terminator (or something along those lines anyway).

SQL Fiddle seems to cope with this, but db<>fiddle also seems to have a problem with it, and doesn't return anything for the whole fiddle when that query is present.

You could redefine your table column as varchar2 instead of nvarchar2, but I assume it is that data type for a reason, so that probably isn't practical.

So you could cast it as part of the query instead:

SELECT LISTAGG(CAST(MOD_CODE AS VARCHAR2(12)),',')
  WITHIN GROUP (ORDER BY MOD_CODE) LISTAGG_OUTPUT
FROM XOTEST_A
WHERE MOD_CODE IN ('AR4001','AR4002');

LISTAGG_OUTPUT
----------------------------------------
AR4001,AR4002

Or see if the patch for bug 19461687 fixes the problem for you.

回答2:

But it is not returning any data (see screenshot)

Your sample data produces the expected result. here is a SQL Fiddle. So the problem isn't the syntax or the database. That leaves data.

The most likely explanation is that your values of MOD_CODE do not match what you have in the WHERE clause. It's hard to tell from the screen shot, but if your data has invisible values, such as trailing spaces ('AR4001 ', 'AR4002 ') then your WHERE clause will return no rows.

A couple of ways to test this. Run your query without the WHERE clause. Test your data for length - vsize(mod_code) - or dumping the contents - dump(mod_code).