ORA-00932: inconsistent datatypes: expected - got CLOB

我会带着你远行 2022-08-27 07:56 473阅读 0赞

最近数据库从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
    --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

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.

[![Oracle&nbsp;牛鹏社][Oracle_nbsp]][Oracle_nbsp_Oracle_nbsp]    [![Oracle DBsupport][Oracle_nbsp]][Oracle DBsupport_Oracle_nbsp]

更多参考

有关Oracle RAC请参考  
     [使用crs\_setperm修改RAC资源的所有者及权限  
][crs_setperm_RAC_]     [使用crs\_profile管理RAC资源配置文件  
][crs_profile_RAC_]     [RAC 数据库的启动与关闭  
][RAC _]     [再说 Oracle RAC services  
][Oracle RAC services]     [Services in Oracle Database 10g  
][Services in Oracle Database 10g]     [Migrate datbase from single instance to Oracle RAC  
][Migrate datbase from single instance to Oracle RAC]     [Oracle RAC 连接到指定实例  
][Oracle RAC _]     [Oracle RAC 负载均衡测试(结合服务器端与客户端)  
][Oracle RAC _ 1]     [Oracle RAC 服务器端连接负载均衡(Load Balance)  
][Oracle RAC _Load Balance_]     [Oracle RAC 客户端连接负载均衡(Load Balance)  
][Oracle RAC _Load Balance_ 1]     [ORACLE RAC 下非缺省端口监听配置(listener.ora tnsnames.ora)][ORACLE RAC _listener.ora tnsnames.ora]  
     [ORACLE RAC 监听配置 (listener.ora tnsnames.ora)  
][ORACLE RAC _ _listener.ora tnsnames.ora_]     [配置 RAC 负载均衡与故障转移  
][RAC _ 1]     [CRS-1006 , CRS-0215 故障一例][CRS-1006 _ CRS-0215]   
     [基于Linux (RHEL 5.5) 安装Oracle 10g RAC][Linux _RHEL 5.5_ _Oracle 10g RAC]  
     [使用 runcluvfy 校验Oracle RAC安装环境][runcluvfy _Oracle RAC]

有关Oracle 网络配置相关基础以及概念性的问题请参考：  
     [配置非默认端口的动态服务注册][Link 1]  
     [配置sqlnet.ora限制IP访问Oracle  
][sqlnet.ora_IP_Oracle]     [Oracle 监听器日志配置与管理][Oracle]  
     [设置 Oracle 监听器密码(LISTENER)  
][Oracle _LISTENER_]     [配置ORACLE 客户端连接到数据库][ORACLE]

有关基于用户管理的备份和备份恢复的概念请参考  
     [Oracle 冷备份  
][Oracle _]     [Oracle 热备份  
][Oracle _ 1]     [Oracle 备份恢复概念  
][Oracle _ 2]     [Oracle 实例恢复  
][Oracle _ 3]     [Oracle 基于用户管理恢复的处理  
][Oracle _ 4]     [SYSTEM 表空间管理及备份恢复  
][SYSTEM _]     [SYSAUX表空间管理及恢复  
][SYSAUX_]     [Oracle 基于备份控制文件的恢复(unsing backup controlfile)][Oracle _unsing backup controlfile]

有关RMAN的备份恢复与管理请参考  
     [RMAN 概述及其体系结构  
][RMAN _]     [RMAN 配置、监控与管理  
][RMAN _ 1]     [RMAN 备份详解  
][RMAN _ 2]     [RMAN 还原与恢复  
][RMAN _ 3]     [RMAN catalog 的创建和使用  
][RMAN catalog _]     [基于catalog 创建RMAN存储脚本  
][catalog _RMAN_]     [基于catalog 的RMAN 备份与恢复  
][catalog _RMAN _]     [RMAN 备份路径困惑  
][RMAN _ 4]     [使用RMAN实现异机备份恢复(WIN平台)  
][RMAN_WIN_]     [使用RMAN迁移文件系统数据库到ASM  
][RMAN_ASM]     [linux 下RMAN备份shell脚本  
][linux _RMAN_shell_]     [使用RMAN迁移数据库到异机][RMAN]

有关ORACLE体系结构请参考  
     [Oracle 表空间与数据文件  
][Oracle _ 5]     [Oracle 密码文件  
][Oracle _ 6]     [Oracle 参数文件  
][Oracle _ 7]     [Oracle 联机重做日志文件(ONLINE LOG FILE)  
][Oracle _ONLINE LOG FILE_]     [Oracle 控制文件(CONTROLFILE)  
][Oracle _CONTROLFILE_]     [Oracle 归档日志  
][Oracle _ 8]     [Oracle 回滚(ROLLBACK)和撤销(UNDO)  
][Oracle _ROLLBACK_UNDO_]     [Oracle 数据库实例启动关闭过程  
][Oracle _ 9]     [Oracle 10g SGA 的自动化管理  
][Oracle 10g SGA _]     [Oracle 实例和Oracle数据库(Oracle体系结构)][Oracle _Oracle_Oracle]

[Oracle_nbsp]: http://pub.idqqimg.com/wpa/images/group.png
[Oracle_nbsp_Oracle_nbsp]: http://wp.qq.com/wpa/qunwpa?idkey=63ed41c9a94b48ebc4d3d956537065ba6d18a649c353b09bfdac284518b7b8d8
[Oracle DBsupport_Oracle_nbsp]: /images/20220824/6b7bb7e79e6a43b097995d5f139ecc99.png
[crs_setperm_RAC_]: http://blog.csdn.net/robinson_0612/article/details/8219242
[crs_profile_RAC_]: http://blog.csdn.net/robinson_0612/article/details/8192571
[RAC _]: http://blog.csdn.net/robinson_0612/article/details/8129506
[Oracle RAC services]: http://blog.csdn.net/robinson_0612/article/details/8124232
[Services in Oracle Database 10g]: http://blog.csdn.net/robinson_0612/article/details/8089812
[Migrate datbase from single instance to Oracle RAC]: http://blog.csdn.net/robinson_0612/article/details/8090095
[Oracle RAC _]: http://blog.csdn.net/robinson_0612/article/details/8073620
[Oracle RAC _ 1]: http://blog.csdn.net/robinson_0612/article/details/8072367
[Oracle RAC _Load Balance_]: http://blog.csdn.net/robinson_0612/article/details/8064599
[Oracle RAC _Load Balance_ 1]: http://blog.csdn.net/robinson_0612/article/details/8060634
[ORACLE RAC _listener.ora tnsnames.ora]: http://blog.csdn.net/robinson_0612/article/details/8053007
[ORACLE RAC _ _listener.ora tnsnames.ora_]: http://blog.csdn.net/robinson_0612/article/details/8020800
[RAC _ 1]: http://blog.csdn.net/robinson_0612/article/details/6791667
[CRS-1006 _ CRS-0215]: http://blog.csdn.net/robinson_0612/article/details/7926241
[Linux _RHEL 5.5_ _Oracle 10g RAC]: http://blog.csdn.net/robinson_0612/article/details/7906282
[runcluvfy _Oracle RAC]: http://blog.csdn.net/robinson_0612/article/details/6533094
[Link 1]: http://blog.csdn.net/robinson_0612/article/details/5925526
[sqlnet.ora_IP_Oracle]: http://blog.csdn.net/robinson_0612/article/details/6629141
[Oracle]: http://blog.csdn.net/robinson_0612/article/details/6629043
[Oracle _LISTENER_]: http://blog.csdn.net/robinson_0612/article/details/6629249
[ORACLE]: http://blog.csdn.net/robinson_0612/article/details/5917103
[Oracle _]: http://blog.csdn.net/robinson_0612/article/details/5791597
[Oracle _ 1]: http://blog.csdn.net/robinson_0612/article/details/5816012
[Oracle _ 2]: http://blog.csdn.net/robinson_0612/article/details/5791585
[Oracle _ 3]: http://blog.csdn.net/robinson_0612/article/details/5768233
[Oracle _ 4]: http://blog.csdn.net/robinson_0612/article/details/6041217
[SYSTEM _]: http://blog.csdn.net/robinson_0612/article/details/6041207
[SYSAUX_]: http://blog.csdn.net/robinson_0612/article/details/5810732
[Oracle _unsing backup controlfile]: http://blog.csdn.net/robinson_0612/article/details/7962187
[RMAN _]: http://blog.csdn.net/robinson_0612/article/details/6032525
[RMAN _ 1]: http://blog.csdn.net/robinson_0612/article/details/6032597
[RMAN _ 2]: http://blog.csdn.net/robinson_0612/article/details/6032739
[RMAN _ 3]: http://blog.csdn.net/robinson_0612/article/details/6041247
[RMAN catalog _]: http://blog.csdn.net/robinson_0612/article/details/6043649
[catalog _RMAN_]: http://blog.csdn.net/robinson_0612/article/details/6073020
[catalog _RMAN _]: http://blog.csdn.net/robinson_0612/article/details/6073069
[RMAN _ 4]: http://blog.csdn.net/robinson_0612/article/details/6414338
[RMAN_WIN_]: http://blog.csdn.net/robinson_0612/article/details/6639064
[RMAN_ASM]: http://blog.csdn.net/robinson_0612/article/details/6453954
[linux _RMAN_shell_]: http://blog.csdn.net/robinson_0612/article/details/8029245
[RMAN]: http://blog.csdn.net/robinson_0612/article/details/8076841
[Oracle _ 5]: http://blog.csdn.net/robinson_0612/article/details/5611738
[Oracle _ 6]: http://blog.csdn.net/robinson_0612/article/details/5611672
[Oracle _ 7]: http://blog.csdn.net/robinson_0612/article/details/5559174
[Oracle _ONLINE LOG FILE_]: http://blog.csdn.net/robinson_0612/article/details/5749556
[Oracle _CONTROLFILE_]: http://blog.csdn.net/robinson_0612/article/details/5737527
[Oracle _ 8]: http://blog.csdn.net/robinson_0612/article/details/5784713
[Oracle _ROLLBACK_UNDO_]: http://blog.csdn.net/robinson_0612/article/details/5731158
[Oracle _ 9]: http://blog.csdn.net/robinson_0612/article/details/5542983
[Oracle 10g SGA _]: http://blog.csdn.net/robinson_0612/article/details/5534832
[Oracle _Oracle_Oracle]: http://blog.csdn.net/robinson_0612/article/details/5529239