Version differences for WMSYS.WM_CONCAT functions in Oracle

Yesterday at the time of the test, it was found that a program written by a developer in the development library is good, but in the test library there will be a problem. Developers have been unable to find the cause of the problem. So it took some time to help developers find the root cause of the problem.

By some technical means, locating the problem is caused by an SQL statement.

The SQL statements are as follows:

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;

Put this SQL statement in the development library can be executed normally, but placed in the test library error. The error message is: ORA-00932: Data type inconsistent: should be-, but get clob. The thought was that there might be some tables in two libraries that have inconsistent database data structures (for example, the data type of a field), and that the tables involved in this SQL statement are identical to the data structures in both environments. At this point, it is possible that a function causes the LOB type to appear in the result. The diagnosis is caused by the Oracle function Wmsys.wm_concat. So why is this causing the problem? Both sets of environments are also used by Oracle, and the program is fully consistent. The first thought was that there might be version differences in Oracle's WMSYS.WM_CONCAT functions.

The following is a validation of the WMSYS.WM_CONCAT function in Oracle version differences. Execute SELECT * FROM v$version in the development library, display the result as: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bi; then select * FRO in the test library M v$version; Displays the result: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bi. The guesswork that explains the Oracle version differences is right.

Next look at what the Wmsys.wm_concat function does in different versions of Oracle.

Write Test 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);

The results of this SQL in the development library (that is, Oracle database 10g Enterprise Edition release 10.2.0.4.0-64bi) are:

The results of this SQL in the test library (that is, Oracle database 10g Enterprise Edition release 10.2.0.5.0-64bi) are:

The result of Oracle's WMSYS.WM_CONCAT function in Oracle Database 10g Enterprise Edition release 10.2.0.4.0-64bi is a character-type data, while the Oracle Database 10g Enterprise Edition release 10.2.0.4.0-64bi is a CLOB type. And we know that in SQL statements, if you query for LOB fields, you cannot use Distinct,union, and GROUP by keywords. This completely explains why the SQL statement will make an error in the test library.

Finally, the above SQL statements are optimized and are common in different versions of Oracle. The SQL statement is modified as follows:

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;

Through this problem, we know that when writing SQL statements, if you use the Wmsys.wm_concat function, you should try not to distinct,union the query results, and group by operation, to avoid the Oracle version of the difference.

Reprint please indicate article from: http://www.cnblogs.com/Automation_software/archive/2013/03/29/2988333.html

Version differences for WMSYS.WM_CONCAT functions in Oracle