ORACLE SQL LISTAGG 未返回预期结果

我正在运行 Oracle Database 11g Enterprise 11.2.0.4.0、PL/SQL Release 11.2.0.4.0，并且在获取 LISTAGG 函数返回我期望的数据集时遇到一些问题。

这是场景：

我有一个看起来像这样的数据集

我想在一行的一列中返回 MOD_CODE 列中的值，例如：

AR4001,AR4002

我一直在尝试使用 LISTAGG 函数来完成此操作，例如：

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

但它没有返回任何数据（见截图）

我究竟做错了什么？这是更广泛场景的一部分，所以我理想地希望使用 LISTAGG 或类似的（我尝试使用 wm_concat 函数，但它返回 HUGECLOB 数据类型并且也不起作用）

我已经编写了 SQL 来构建重现场景

 -- 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')

这似乎与 bug 19461687 有关，并且上一个问题 https://stackoverflow.com/q/28812445/266304。如果您将查询中的聚合值转储到 11gR2 或 12cR1 中，您会看到：

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

在 SQL*Plus 和 SQL Developer 中，实际值显示为：

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

并且您无法从 SQL Developer 复制该值。 （在 12cR2 中，转储中不再出现零，该值显示时没有空格，并且您可以复制它，因此该错误似乎已得到修复。）

这些空字节似乎导致 Toad 根本不显示该值，大概是因为它看到第一个空字节并将其视为字符串终止符（或无论如何）。

SQL Fiddle 似乎可以解决这个问题，但 dbfiddle 似乎也有问题，并且当该查询存在时，不会为整个 fiddle 返回任何内容。

您可以将表格列重新定义为varchar2代替nvarchar2，但我认为它是该数据类型是有原因的，所以这可能不切实际。

因此，您可以将其转换为查询的一部分：

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

或者查看 bug 19461687 的补丁是否可以解决您的问题。