Listagg函数在遇到nvarchar2数据类型时触发的BUG

来源：互联网发布：远程传输软件编辑：程序博客网时间：2024/06/05 14:18
今天在帮助开发人员解决一个SQL问题时，触发了oracle的一个bug。
下面对此次遇到的问题，演示如下,希望下次遇到相似问题的人可以少走弯路。
创建测试表：CREATE TABLE SCOTT.EMP_TEMP AS  SELECT * FROM SCOTT.EMP;查看表结构：SQL> desc SCOTT.EMP_TEMP; Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- EMPNO                                              NUMBER(4) ENAME                                              VARCHAR2(10) JOB                                                VARCHAR2(9) MGR                                                NUMBER(4) HIREDATE                                           DATE SAL                                                NUMBER(7,2) COMM                                               NUMBER(7,2) DEPTNO                                             NUMBER(2)查看表的数据：SQL> set lines 200 pages 1000SQL> select * from SCOTT.EMP_TEMP;     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------      7369 SMITH      CLERK           7902 1980:12:17 00:00:00        800                    20      7499 ALLEN      SALESMAN        7698 1981:02:20 00:00:00       1600        300         30      7521 WARD       SALESMAN        7698 1981:02:22 00:00:00       1250        500         30      7566 JONES      MANAGER         7839 1981:04:02 00:00:00       2975                    20      7654 MARTIN     SALESMAN        7698 1981:09:28 00:00:00       1250       1400         30      7698 BLAKE      MANAGER         7839 1981:05:01 00:00:00       2850                    30      7782 CLARK      MANAGER         7839 1981:06:09 00:00:00       2450                    10      7788 SCOTT      ANALYST         7566 1987:04:19 00:00:00       3000                    20      7839 KING       PRESIDENT            1981:11:17 00:00:00       5000                    10      7844 TURNER     SALESMAN        7698 1981:09:08 00:00:00       1500          0         30      7876 ADAMS      CLERK           7788 1987:05:23 00:00:00       1100                    20      7900 JAMES      CLERK           7698 1981:12:03 00:00:00        950                    30      7902 FORD       ANALYST         7566 1981:12:03 00:00:00       3000                    20      7934 MILLER     CLERK           7782 1982:01:23 00:00:00       1300                    1014 rows selected.正常的查询如下：SQL> COL DENAME FOR A40SQL> SELECT DEPTNO, LISTAGG(ENAME,',') WITHIN GROUP(ORDER BY DEPTNO) AS DENAME  2    FROM SCOTT.EMP_TEMP  3   GROUP BY DEPTNO;    DEPTNO DENAME---------- ----------------------------------------        10 CLARK,KING,MILLER        20 ADAMS,FORD,JONES,SCOTT,SMITH        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD发现显示OK，因为ename是varchar2类型。下面把其数据类型修改为nvarchar2：SQL> ALTER TABLE  SCOTT.EMP_TEMP MODIFY ENAME NVARCHAR2(10);Table altered.继续查询：a. SQLPLUS查询结果如下：SQL> SELECT DEPTNO, LISTAGG(ENAME,',') WITHIN GROUP(ORDER BY DEPTNO) AS DENAME  2    FROM SCOTT.EMP_TEMP  3   GROUP BY DEPTNO;    DEPTNO DENAME---------- ----------------------------------------        10  C L A R K, K I N G, M I L L E R        20  A D A M S, F O R D, J O N E S, S C O T           T, S M I T H        30  A L L E N, B L A K E, J A M E S, M A R           T I N, T U R N E R, W A R Db. PL/SQL查询没有结果。现在对其类型进行转换：SQL> SELECT DEPTNO,  2         LISTAGG(TO_CHAR(ENAME), ',') WITHIN GROUP(ORDER BY DEPTNO) AS DENAME  3    FROM SCOTT.EMP_TEMP  4   GROUP BY DEPTNO;    DEPTNO DENAME---------- ----------------------------------------        10 CLARK,KING,MILLER        20 ADAMS,FORD,JONES,SCOTT,SMITH        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD显示正常。注意:TO_CHAR函数或者CASR函数均可.从上面的现象说明，在数据库设计阶段，就应该设计好字段数据类型。不然说不定哪天就被坑给埋了。
0 0