ORA-00932: inconsistent datatypes: expected - got CLOB

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

--记得当前服务器下数据库并没有使用任何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
--Author : Leshami
--Blog   : http://blog.csdn.net/leshami
--由于返回类型不一致导致了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