WMSYS.WM_CONCAT函数及ORA-00932: 数据类型不一致: 应为 -, 但却获得 CLOB
RDBMS 10.2.0.4 ，在升级到11.2.0.4之后，发现应用报错ORA-00932: 数据类型不一致: 应为 -, 但却获得 CLOB。
查看了以下，是函数WMSYS.WM_CONCAT引起的。发现在不同的版本中该函数返回的类型是不一样的。
测试发现，在10.2.0.4下，返回的是varchar2类型的，在10.2.0.5下，显示的是clob类型的。在11.2.0.4下返回的是clob类型的。另外，在11.1.0.7，11.2.0.1里面返回的是varchar2类型。从12c开始，该函数已经被废弃了。
C:\Users\Administrator>sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on 星期三 6月 19 13:41:21 2019
Copyright (c) 1982, 2017, Oracle. All rights reserved.
@>conn / as sysdba
Connected.
SYS@test>desc wmsys.wm_concat
FUNCTION wmsys.wm_concat RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P1 VARCHAR2 IN
SYS@test>
[oracle@dm2 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Jun 19 13:55:03 2019
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected.
SQL> desc wmsys.wm_concat
FUNCTION wmsys.wm_concat RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P1 VARCHAR2 IN
SQL>
[oracle@DB ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 19 05:57:53 2019
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected.
SQL> desc wmsys.wm_concat
FUNCTION wmsys.wm_concat RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P1 VARCHAR2 IN
SQL> 查看Oracle官方文档，是查询不到该函数的 。
查看Oracle MOS，可以参考以下文档
Problem with WMSYS.WM_CONCAT Function after Upgrading (文档 ID 1300595.1)
WMSYS.WM_CONCAT Should Not Be Used For Customer Applications, It Is An Internal Function (文档 ID 1336219.1)
其中，文档中说，这个不是一个bug
This is not a bug.
The function WMSYS.WM_CONCAT is an internal undocumented function which is installed/uninstalled as part of the Workspace Manager feature of Oracle Database. It is internally used in a number of Workspace Manager views. It is not meant to be used by customers directly, and could be changed/updated without notice by Oracle Development. Do not use the WMSYS.WM_CONCAT view in your application.
在另一篇文档中，Oracle提到了这个是一个undocumentd function 。
This document is meant to assist both Customers and Oracle Support Analysts in handling situations and Service Requests in which the problem is related to the usage of the undocumented function WMSYS.WM_CONCAT
另外，Oracle提示，之所以有这个函数，是因为为了内部使用，有可能会因为系统性能的原因，在不同的数据库版本中废弃掉这个函数。Oracle建议使用LISTAGG函数来替代这个函数。
WMSYS.WM_CONCAT function is used internally and for this reason it is UN-documented.
Oracle Development uses internal procedure/functions like WMSYS.WM_CONCAT to support Oracle products.
Oracle suggests that customers do not use internal objects, procedures, data dictionary tables in their applications because they may be changed at any time.
If Oracle Development needs to change this function, to correct a bug or enhance performances, they make all the changes internally and the changes are NOT published.
Any non-Oracle document found on Internet referring to this function is not reliable. Anything Oracle does support, is documented in our guides and/or published documents searchable on MyOracle Support.
It should be relatively straightforward for a developer to create an aggregate function similar to WMSYS.WM_CONCAT.
Some useful links that can be used to understand how to proceed are:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402
http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
http://rwijk.blogspot.com/2008/05/string-aggregation-with-model-clause.html
Additionally, from 11.2 release onwards it might be a good idea that of using for this same purpose the analytics function named LISTAGG.
You can find a reference ans some good example inthe following documentation:
Oracle® Database SQL Language Reference 11g Release 2 (11.2)
Oracle® Database SQL Language Reference 12c Release 1 (12.1)
END