关于Oracle函数wm_concat的一些问题
前几天和DBA一起更换了数据库的服务器,把数据做了迁移,DBA说:你们原来的数据库是11.2.0.1.0版本的,我给你们升级到11.2.0.4.0这个版本吧,这个版本是现在最稳定的版本。我也没多想,就同意了。
迁移完成之后,系统某些功能开始报以下错误:
ORA-00932: 数据类型不一致: 应为 -, 但却获得 CLOB
发现是sql里的wm_concat这个函数报的错,原数据库返回结果为varchar2类型,新数据库返回结果为CLOB类型,紧急中把所有的wm_concat函数都加了一层to_char又转成了字符串类型(谢天谢地eclipse支持正则替换),算是临时解决了这个问题。
事后又检讨这个问题,发现了以下文档:
Symptoms
In releases 10.2.0.5 and 11.2.0.2, creating a view using the WMSYS.WM_CONCAT function fails.
In releases 10.2.0.4, 11.1.0.7 and 11.2.0.1, the view compiles successfully.
Cause
The datatype returned from WMSYS.WM_CONCAT function changed from VARCHAR2 to CLOB in releases 10.2.0.5 and 11.2.0.2.
In 10.2.0.4 / 11.1.0.7 / 11.2.0.1 it returns VARCHAR2
SQL> desc wmsys.wm_concat;
FUNCTION wmsys.wm_concat RETURNS VARCHAR2 <<<<<<<<<<<<<<<Argument Name Type In/Out Default?
----------------------- ------------------------ -------- ---------
P1 VARCHAR2 IN
In 10.2.0.5 / 11.2.0.2 it returns CLOB
SQL> desc wmsys.wm_concat;
FUNCTION wmsys.wm_concat RETURNS CLOB <<<<<<<<<<<<<<<Argument Name Type In/Out Default?
----------------------- ------------------------ -------- ---------
P1 VARCHAR2 IN
Solution
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.
重点有二:
一是:
The datatype returned from WMSYS.WM_CONCAT function changed from VARCHAR2 to CLOB in releases 10.2.0.5 and 11.2.0.2.
二是:
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随着版本升级对这个函数的改变不会向下兼容啊。
那么问题来了,不用这个函数,我们能用什么替代方案来实现类似的功能呢?