WMSYS.WM_CONCAT函数有些版本返回结果为CLOB引起视图无法创建
现象描述：
在做exp/imp，新库imp时发现1个视图异常（IMP-00041: Warning: object created with compilation warnings）
手工创建，创建不了，报：ORA-00932: inconsistent datatypes: expected - got CLOB
但是旧库是正常的。
新库（imp导入的库）环境：
$ uname -a
Linux 601cluster 2.6.18-92.el5 #1 SMP Tue Apr 29 13:16:15 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux
SQL> select * from V$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> select action,comments from registry$history;
ACTION COMMENTS
------------------------------ -----------------
VIEW RECOMPILE view recompilation
UPGRADE Upgraded from 10.2.0.1.0
APPLY PSU 10.2.0.5.4
旧库（exp导出的库）环境：
$ uname -a
Linux pera205 2.6.18-274.el5 #1 SMP Fri Jul 8 17:36:59 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux
SQL> select * from V$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
问题SQL如下：
SQL较长，为了可读性，减掉了许多，但是报错效果是一样的。
create or replace view xxxx01 as
select
distinct( k.KNOWLEDGE_ID),
k.AUTHORID,
(select WMSYS.WM_CONCAT(kwt.workbag_id)
from km_workbag_knowledge kwt where kwt.object_type = 3
and kwt.knowledge_id=k.knowledge_id) as topicID
from KM_Knowledge K
where
k.param4 = 2
and k.param5 = 0 and k.current_version='1'
order by k.knowledge_id
;
去掉含有distinct的列，可以创建成功，如下：
create or replace view xxxx02 as
select
k.AUTHORID,
(select WMSYS.WM_CONCAT(kwt.workbag_id)
from km_workbag_knowledge kwt where kwt.object_type = 3
and kwt.knowledge_id=k.knowledge_id) as topicID
from KM_Knowledge K
where
k.param4 = 2
and k.param5 = 0 and k.current_version='1'
order by k.knowledge_id
;
去掉有CLOB列也可以创建成功，如下：
create or replace view xxxx03 as
select
distinct( k.KNOWLEDGE_ID),
k.AUTHORID
from KM_Knowledge K
where
k.param4 = 2
and k.param5 = 0 and k.current_version='1'
order by k.knowledge_id
;
表中的数据：
SQL> select count(*) from km_workbag_knowledge;
COUNT(*)
----------
1536
SQL> select count(*) from KM_Knowledge;
COUNT(*)
----------
64449
单独执行以下SQL：
select WMSYS.WM_CONCAT(kwt.workbag_id)
from km_workbag_knowledge kwt,KM_Knowledge K where kwt.object_type = 3
and kwt.knowledge_id=k.knowledge_id
通过PL/SQL执行，从结果的显示来看，旧库中该结果并没有转换成CLOB型，新库转换成了CLOB
在旧库中执行以下SQL：
将后面字段手工变为CLOB
create or replace view xxxx04 as
select
distinct( k.KNOWLEDGE_ID),
k.AUTHORID,
to_clob(
(select WMSYS.WM_CONCAT(kwt.workbag_id)
from km_workbag_knowledge kwt where kwt.object_type = 3
and kwt.knowledge_id=k.knowledge_id)) as topicID
from KM_Knowledge K
where
k.param4 = 2
and k.param5 = 0 and k.current_version='1'
order by k.knowledge_id
;
同样报下面的错：
ORA-00932: 数据类型不一致: 应为 -, 但却获得 CLOB
结论：
1. 创建视图时，不能同时有distinct和clob列
2. WMSYS.WM_CONCAT函数在不同的版本结果类型不一样，具体哪些版本暂时不知道
问题：
1.上面结论是否正确
2.WMSYS.WM_CONCAT 函数还会在哪些版本里结果会变成CLOB
3.写完以上内容后发现衣钩大师早有警示：http://www.eygle.com/archives/2012/10/wmsys_wm_concat.html 只是只列出了11.2.0.3 和10.2.0.5 ，是不是在这之后版本都会变成CLOB
我在10205上遇到过你这个问题，10204没有，应该就是从10205开始的 具体可以看一下这份文章：
Problem with WMSYS.WM_CONCAT Function after Upgrading 在不同版本下查看该函数的返回值类型
10.2.0.5我没有，别的数据基本齐了，结合其他资料，应该是10g的10.2.0.5和11g的11.2.0.3将该函数修改，返回值由varchar2变成了clob，所以在跨越这些版本迁移视图时出现了问题
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
SQL> desc WMSYS.WM_CONCAT
Object WMSYS.WM_CONCAT does not exist.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> desc WMSYS.WM_CONCAT
Parameter Type Mode Default?
--------- -------- ---- --------
(RESULT) VARCHAR2
P1 VARCHAR2 IN
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> desc WMSYS.WM_CONCAT
Parameter Type Mode Default?
--------- -------- ---- --------
(RESULT) VARCHAR2
P1 VARCHAR2 IN
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> desc WMSYS.WM_CONCAT
Parameter Type Mode Default?
--------- -------- ---- --------
(RESULT) CLOB
P1 VARCHAR2 IN
Large objects (LOBs) are not supported in comparison conditions. However, you can use PL/SQL programs for comparisons on CLOB data. call dbms_lob package ,If your clob values are always less than 4k, you can simple use distinct to_char(clob) to avoid the error. 而问题ORA-00932: inconsistent datatypes: expected - got CLOB
是由于distinct与clob一起使用，这是非法的
分别在11.2.0.3和10.2.0.4下测试均报错
SQL> select distinct send_text from t_bank_text where rownum<=3;
ORA-00932: inconsistent datatypes: expected - got CLOB
SQL> select distinct ZNOTE from CC_E_RESCUEINFO where rownum<=3;
ORA-00932: inconsistent datatypes: expected - got CLOB ODM FINDING:
Upgraded database to 10.2.0.5 or 11.2.0.2 ( and above ).
The definition of WMSYS.WM_CONCAT function has changed in 11.2.0.2 and 10.2.0.5 onward to
use CLOB.
10.2.0.5, 11.2.0.2+
FUNCTION wm_concat( P1 VARCHAR2 IN ) RETURNS CLOB.
10.2.0.4-, 11.1.0.7-, 11.2.0.1
FUNCTION wm_concat( P1 VARCHAR2 IN ) RETURNS VARCHAR2
WMSYS.WM_CONCAT using CLOBs has serious performance impact when it is accessed excessively.
Solution
Since WMSYS.WM_CONCAT is undocumented and not intended to be used by customer's application,
it is suggested that customers write their own customized functions serving the purpose.
依据文档 总结2点：
1、10.2.0.5, 11.2.0.2+ 以及今后 wm_concat返回 CLOB
10.2.0.4-, 11.1.0.7-, 11.2.0.1 之前返回VARCHAR2
2 、 WMSYS.WM_CONCAT本身不该开发人员使用，只适合ORACLE内部使用。Oracle对其的态度很明确，出了问题 不负责帮你解决， 就是让你改不用WMSYS.WM_CONCAT
了解了，谢谢大家
页:
[1]