ORA-00932: inconsistent datatypes: expected - got CLOB

olop

    最近数据库从10.2.0.3升级到了10.2.0.5之后，一些对象无法编译通过。查看了这些对象主要表现在之前写法不严格的SQL语法导致了这些package无法成功编译，诸如select查询列中不能使用混淆的列名称等。另外一个比较表现突出的是返回ORA-00932: inconsistent datatypes: expected - got CLOB错误，即不一致的数据类型，获得CLOB数据类型。下面是这个问题的症状及对策。



1、故障现象


    SQL> alter package bo_trd_trade_relink_pkg compile body;  
      
    Warning: Package Body altered with compilation errors.  
      
    SQL> show errors;  
    Errors for PACKAGE BODY BO_TRD_TRADE_RELINK_PKG:  
      
    LINE/COL ERROR  
    -------- -----------------------------------------------------------------  
    30/13    PL/SQL: ORA-00932: inconsistent datatypes: expected - got CLOB  
    30/13    PL/SQL: SQL Statement ignored  
    898/13   PL/SQL: ORA-00932: inconsistent datatypes: expected - got CLOB  
    898/13   PL/SQL: SQL Statement ignored  

2、分析与解决


    --记得当前服务器下数据库并没有使用任何CLOB数据类型，却返回CLOB类型了，我懵！  
    --还是搜索了数据库中是否存在，一个也没有找到  
    SQL> select * from v$version where rownum<2;  
      
    BANNER  
    ----------------------------------------------------------------  
    Oracle Database 10g Release 10.2.0.3.0 - 64bit Production  
      
    SQL> select data_type from dba_tab_columns where data_type like '%LOB%' and owner='GOEX_ADMIN';  
      
    no rows selected  
      
    --在错误提示地方，如30行处发现了为select 查询列使用了wm_concat函数，尝试注视该列，Pckage编译成功，看来是这个函数是罪魁祸首  
    --关于这个函数在10.2.0.3的表现为返回为VARCHAR2数据类型，如下:   
    SQL> select * from v$version where rownum<2;  
      
    BANNER  
    ----------------------------------------------------------------  
    Oracle Database 10g Release 10.2.0.3.0 - 64bit Production  
      
    SQL> desc wmsys.wm_concat  
    FUNCTION wmsys.wm_concat RETURNS VARCHAR2  
     Argument Name                  Type                    In/Out Default?  
     ------------------------------ ----------------------- ------ --------  
     P1                             VARCHAR2                IN  
      
    --而10.2.0.5表现为返回的CLOB数据类型   
    SQL> select * from v$version where rownum<2;  
      
    BANNER  
    ----------------------------------------------------------------  
    Oracle Database 10g Release 10.2.0.5.0 - 64bit Production  
      
    SQL> desc wmsys.wm_concat  
    FUNCTION wmsys.wm_concat RETURNS CLOB  
     Argument Name                  Type                    In/Out Default?  
     ------------------------------ ----------------------- ------ --------  
     P1                             VARCHAR2                IN  

    --由于返回类型不一致导致了package在新环境中无法成功编译  
      
    原因：lob字段不能用做group by，而union中需要使用group by过滤重复记录，所以无法编译成功  
      
    解决方案：  
    a、为这个select 查询列使用了to_char函数来进行转换(wm_concat(col_name))  
    b、或者修改union 为union all  
      
    --下面给一个示例供大家参考(10.2.0.5环境)，仅仅是执行SQL  
    SQL> select * from t8;  
      
            ID VAL  
    ---------- --------------------  
             1 LINUX  
             1 SOLARIS  
             2 ORACLE  
             2 MYSQL  
      
    SQL> select * from t9;  
      
            ID VAL  
    ---------- --------------------  
             3 OFFICE  
      
    --单独使用时没有任何异常  
    SQL> select id,wm_concat(val) new_val from t8 group by id;  
      
            ID NEW_VAL  
    ---------- ------------------------------  
             1 LINUX,SOLARIS  
             2 ORACLE,MYSQL      
      
    --使用union时出现ORA-00932错误  
    SQL> select id,wm_concat(val) new_val from t8 group by id  
      2  union   
      3  select id,wm_concat(val) new_val from t9 group by id;  
    select id,wm_concat(val) new_val from t8 group by id  
              *  
    ERROR at line 1:  
    ORA-00932: inconsistent datatypes: expected - got CLOB  
      
    --通过修改union为union all或者使用to_char类解决  
    SQL> select id,wm_concat(val) new_val from t8 group by id  
      2  union all  
      3  select id,wm_concat(val) new_val from t9 group by id;  
      
            ID NEW_VAL  
    ---------- ------------------------------  
             1 LINUX,SOLARIS  
             2 ORACLE,MYSQL  
             3 OFFICE  
      
    SQL> select id,to_char(wm_concat(val)) new_val from t8 group by id  
      2  union select id, to_char(wm_concat(val)) new_val from t9 group by id;  
      
            ID NEW_VAL  
    ---------- ------------------------------  
             1 LINUX,SOLARIS  
             2 ORACLE,MYSQL  
             3 OFFICE  

                  
3、Metalink上的相关文档(ID 1300595.1，ID 1336219.1)
--是一个内部函数，不建议使用

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.