WMSYS.WM_CONCAT函数有些版本返回结果为CLOB引起视图无法创建

bamuta

现象描述：

在做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







          
          

lsz_qh

我在10205上遇到过你这个问题，10204没有，应该就是从10205开始的

lsz_qh

具体可以看一下这份文章：
Problem with WMSYS.WM_CONCAT Function after Upgrading [ID 1300595.1]

雪影舞剑

在不同版本下查看该函数的返回值类型
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  

anbob

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

Liu Maclean(刘相兵

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

bamuta

了解了，谢谢大家