oracle中WMSYS.WM_CONCAT函数的版本差异

来源：互联网发布：单片机上拉电阻原理图编辑：程序博客网时间：2024/06/10 15:59

昨天在测试的时候发现，开发人员写的一段程序放在开发库中是好的，但是放在测试库中就会有问题。开发人员一直找不到问题的原因在哪里。于是就花了点时间协助开发人员来找问题的根本原因。

通过一些技术手段，定位到问题是由一个SQL语句引起的。

SQL语句如下：

WITH PXQ_TASK_TEMP AS (SELECT A.DISTRIBUTE_DATE,         A.APP_NO,         A.TASK_ID,         A.TASK_NO,         A.ACTUAL_DISTRIBUTE_DATE,         A.ACTUAL_RETURN_DATE,         A.RELA_ID RELAID,         A.VEHICLE_SERIAL,         A.STATUS_CODE,         A.MONTH,         A.MADE_DPET_NO,         A.DIST_TYPE_CODE,         A.TASK_TYPE,         A.RETURN_DATE,         B.SOURCE_NODE_ID,         B.RCV_NODE_NAME,         B.RCV_NODE_ID,         B.RCV_ID,         C.DET_ID,         C.EQUIP_CATEG,         C.TASK_NUM,         D.RELA_ID    FROM D_DISTRIBUTE_TASK        A,         D_RCV_TASK               B,         D_DIST_TASK_DET          C,         ECMS.D_VEHICLE_TASK_RELA D   WHERE A.TASK_ID = B.TASK_ID(+)     AND B.RCV_ID = C.RCV_ID(+)     AND A.TASK_ID = D.TASK_ID(+)),PXQ_TASK_CATEG AS (SELECT DISTINCT TASK_ID, RCV_ID, DET_ID, EQUIP_CATEG, TASK_NUM    FROM PXQ_TASK_TEMP),PXQ_TASK_SUM AS (SELECT V.NAME || ':' || SUM(TASK_NUM) TASK_SUM, MAX(PXQ.TASK_ID) TASK_ID    FROM V_SP_CODE V, PXQ_TASK_CATEG PXQ   WHERE CODE_TYPE LIKE 'equipCateg'     AND VALUE = EQUIP_CATEG   GROUP BY PXQ.TASK_ID, V.NAME)SELECT DISTINCT AA.DISTRIBUTE_DATE PLAN_DATE,                AA.APP_NO,                AA.MONTH,                AA.TASK_ID,                AA.TASK_NO,                AA.ACTUAL_DISTRIBUTE_DATE,                AA.ACTUAL_RETURN_DATE,                AA.RELAID,                AA.VEHICLE_SERIAL EV_NO,                AA.STATUS_CODE,                AA.DIST_TYPE_CODE,                AA.TASK_TYPE,                AA.RETURN_DATE,                (SELECT REPLACE(WMSYS.WM_CONCAT(NODE_NAME), ',', ';')                   FROM ECMS.D_DISTRIBUTE_NODE                  WHERE NODE_ID IN (SELECT DISTINCT SOURCE_NODE_ID                                      FROM PXQ_TASK_TEMP                                     WHERE TASK_ID = AA.TASK_ID)) SOURCE_NODE,                (SELECT REPLACE(WMSYS.WM_CONCAT(NODE_NAME), ',', ';')                   FROM ECMS.D_DISTRIBUTE_NODE                  WHERE NODE_ID IN (SELECT DISTINCT RCV_NODE_ID                                      FROM PXQ_TASK_TEMP                                     WHERE TASK_ID = AA.TASK_ID)) RCV_NODE,                (SELECT REPLACE(WMSYS.WM_CONCAT(TASK_SUM), ',', '/')                   FROM PXQ_TASK_SUM                  WHERE TASK_ID = AA.TASK_ID) PLAN_NUM,                '' EV_NUM,                '' SETTED_EV  FROM PXQ_TASK_TEMP AA WHERE 1 = 1;

把这个SQL语句放在开发库可以正常执行，但是放在测试库就报错了。报错信息为：ORA-00932:数据类型不一致:应为 -，但却获得CLOB。当时想到的就是两个库中可能有些表的数据库数据结构不一致（如：字段的数据类型），经比较这个SQL语句中涉及到的表在两个环境中的数据结构完全一致。这时想到的就是可能是某个函数导致了结果出现了LOB类型。经诊断是由oracle函数WMSYS.WM_CONCAT引起的。那么为什么会引起这样的问题呢？两套环境同样是使用的oracle，程序也完全一致。首先想到的是可能oracle的WMSYS.WM_CONCAT函数存在版本差异。

下面就进行了WMSYS.WM_CONCAT函数在oracle版本差异的验证。在开发库中执行SELECT * FROM v$version;显示结果为：Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi；再在测试库中执行SELECT * FROM v$version;显示结果为：Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi。说明oracle版本差异的猜测是对的。

接下来看WMSYS.WM_CONCAT函数在不同oracle版本中到底存在什么差异。

编写测试SQL：

SELECT REPLACE(WMSYS.WM_CONCAT(NODE_NAME), ',', ';')  FROM ECMS.D_DISTRIBUTE_NODE A WHERE NODE_ID IN (SELECT DISTINCT SOURCE_NODE_ID FROM D_RCV_TASK B);

该SQL在开发库中（即oracle版本为：Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi）执行结果为：

该SQL在测试库中（即oracle版本为：Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi）执行结果为：

oracle的WMSYS.WM_CONCAT函数在Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi版本中的结果是一个字符型的数据，而在Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi版本中是一个CLOB类型。而我们知道在SQL语句中若查询了LOB字段是不能使用distinct，union，和group by等关键字的。这样就完全解释了那个SQL语句会在测试库中报错的原因了。

最后将上面的SQL语句做了相关优化，且在不同oracle版本中通用。SQL语句修改后如下：

SELECT A.DISTRIBUTE_DATE PLAN_DATE,       A.APP_NO,       A.MONTH,       A.TASK_ID,       A.TASK_NO,       A.ACTUAL_DISTRIBUTE_DATE,       A.ACTUAL_RETURN_DATE,       A.RELA_ID,       A.VEHICLE_SERIAL EV_NO,       A.STATUS_CODE,       A.DIST_TYPE_CODE,       A.TASK_TYPE,       A.RETURN_DATE,       (SELECT REPLACE(WMSYS.WM_CONCAT(NODE_NAME), ',', ';')          FROM ECMS.D_DISTRIBUTE_NODE         WHERE NODE_ID IN (SELECT DISTINCT SOURCE_NODE_ID                             FROM D_RCV_TASK                            WHERE TASK_ID = A.TASK_ID)) SOURCE_NODE,       (SELECT REPLACE(WMSYS.WM_CONCAT(NODE_NAME), ',', ';')          FROM ECMS.D_DISTRIBUTE_NODE         WHERE NODE_ID IN (SELECT DISTINCT RCV_NODE_ID                             FROM D_RCV_TASK                            WHERE TASK_ID = A.TASK_ID)) RCV_NODE,       (SELECT REPLACE(WMSYS.WM_CONCAT(V.NAME || ':' || SUM(C.TASK_NUM)),                       ',',                       '/')          FROM V_SP_CODE V, D_DIST_TASK_DET C         WHERE C.TASK_ID = A.TASK_ID           AND V.CODE_TYPE LIKE 'equipCateg'           AND V.VALUE = C.EQUIP_CATEG         GROUP BY V.NAME) PLAN_NUM,       '' EV_NUM,       '' SETTED_EV  FROM D_DISTRIBUTE_TASK A, ECMS.D_VEHICLE_TASK_RELA B WHERE A.TASK_ID = B.TASK_ID(+) GROUP BY A.DISTRIBUTE_DATE,          A.APP_NO,          A.MONTH,          A.TASK_ID,          A.TASK_NO,          A.ACTUAL_DISTRIBUTE_DATE,          A.ACTUAL_RETURN_DATE,          A.RELA_ID,          A.VEHICLE_SERIAL,          A.STATUS_CODE,          A.DIST_TYPE_CODE,          A.TASK_TYPE,          A.RETURN_DATE;

通过这次问题的排查，我们知道在写SQL语句的时候，若使用了WMSYS.WM_CONCAT函数，应尽量不要对查询结果进行distinct，union，和group by操作，避免oracle版本的差异化。

0 0