The ORA-00932: inconsistent datatypes: expected.

Some objects cannot be compiled after the database is upgraded from 10.2.0.3 to 10.2.0.5 recently. The results show that these objects are mainly manifested in SQL syntax that is not strictly written before, which leads to the failure of compilation of these packages, such as the failure to use confusing column names in select query columns. Another outstanding result is the return of the ORA-00932: inconsistent datatypes: expected-got CLOB error, that is, the inconsistent data type, to obtain the CLOB data type. The following are the symptoms and Countermeasures for this problem.

 

1. Fault

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 CLOB30/13    PL/SQL: SQL Statement ignored898/13   PL/SQL: ORA-00932: inconsistent datatypes: expected - got CLOB898/13   PL/SQL: SQL Statement ignored

2. Analysis and Solution

-- Remember that no CLOB data type is used in the database under the current server, but the CLOB type is returned. I recommend it! -- Whether the database exists or not, and no SQL> select * from v $ version where rownum <2; BANNER export Oracle Database 10g Release 10.2.0.3.0-64bit ProductionSQL> select data_type from dba_tab_columns where data_type like '% LOB %' and owner = 'goex _ admin '; no rows selected -- if the wm_concat function is used for the select query column is found in the 30 rows, and the Pckage is compiled successfully, it seems that this function is the culprit -- the result of this function in 10.2.0.3 is The return value is of the VARCHAR2 data type. For example, SQL> select * from v $ version where rownum <2; BANNER implements Oracle Database 10g Release 10.2.0.3.0-64bit ProductionSQL> desc wmsys. wm_concatFUNCTION wmsys. wm_concat RETURNS VARCHAR2 Argument Name Type In/Out Default? ------------------------------ --------------------- ------ -------- P1 VARCHAR2 IN -- and 10.2.0.5 indicates that the returned CLOB data type SQL> select * from v $ version where rownum <2; BANNER ---------------------------------------------------------------- Oracle Database 10g Release 10.2.0.5.0-64bit ProductionSQL> desc wmsys. wm_concatFUNCTION wmsys. wm_concat returns clob Argument Name Type In/Out Default? ------------------------------ --------------------- ------ -------- P1 VARCHAR2 IN -- Author: Leshami -- Blog: distribute by, while group by is used IN union to filter duplicate records, so the solution to compilation failure is as follows: a. The to_char function is used to convert the select query column (wm_concat (col_name )) b. Modify union to union all. The following example is provided for your reference (in the 10.2.0.5 Environment). Only SQLSQL> select * from t8 is executed; id val ---------- -------------------- 1 LINUX 1 SOLARIS 2 ORACLE 2 MYSQLSQL> select * from t9; id val ---------- -------------------- 3 OFFICE -- no exception SQL when used independently> select id, wm_concat (val) new_val from t8 group by id; ID NEW_VAL ---------- ------------------------------ 1 LINUX, SOLARIS 2 ORACLE, MYSQL -- ORA-00932 error occurs when union is used 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 -- Solve SQL> select id by modifying union to union all or using to_char class, 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 OFFICESQL> 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. Related Documents on Metalink (ID 1300595.1, ID 1336219.1)
-- Is an internal function and is not recommended.

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 uninitialized ented 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 MERs directly, and cocould be changed/updated without notice by Oracle Development. do not use the WMSYS. WM_CONCAT view in your application.

 

More references

For more information about Oracle RAC, see
Use crs_setperm to modify the resource owner and permissions of RAC.
Use crs_profile to manage RAC resource configuration files
RAC database startup and Shutdown
Oracle RAC services
Services in Oracle Database 10g
Migrate datbase from single instance to Oracle RAC
Connect Oracle RAC to a specified instance
Oracle RAC load balancing test (combined with server and client)
Oracle RAC server connection Load Balance)
Load Balance)
Non-Default port listening configuration in oracle rac (listener. ora tnsnames. ora)
Oracle rac listener Configuration (listener. ora tnsnames. ora)
Configure RAC load balancing and Failover
CRS-1006, CRS-0215 fault case
Installing Oracle 10g RAC Based on Linux (RHEL 5.5)
Use runcluvfy to verify the Oracle RAC installation environment

For more information about the basics and concepts of Oracle network configuration, see:
Configure dynamic service registration for non-default ports
Configure sqlnet. ora to restrict IP Access to Oracle
Configure and manage Oracle listener logs
Set the Oracle LISTENER password (LISTENER)
Configure the ORACLE client to connect to the database

For more information about user-managed backup and recovery, see
Oracle cold backup
Oracle Hot Backup
Concept of Oracle backup recovery
Oracle instance recovery
Oracle recovery based on user management
SYSTEM tablespace management and Backup Recovery
SYSAUX tablespace management and recovery
Oracle backup control file recovery (unsing backup controlfile)

For information on RMAN backup recovery and management, see
RMAN overview and architecture
RMAN configuration, Monitoring and Management
Detailed description of RMAN backup
RMAN restoration and recovery
Create and use RMAN catalog
Create RMAN storage script based on catalog
Catalog-based RMAN backup and recovery
RMAN backup path confusion
Use RMAN for recovery from different machine backups (WIN platform)
Use RMAN to migrate a file system database to ASM
Linux RMAN backup shell script
Use RMAN to migrate the database to a different machine

For the ORACLE architecture, see
Oracle tablespace and data files
Oracle Password File
Oracle parameter file
Oracle ONLINE redo LOG FILE)
Oracle Control File)
Oracle archiving logs
Oracle ROLLBACK and UNDO)
Oracle database instance startup and Shutdown Process
Automated Management of Oracle 10g SGA
Oracle instances and Oracle databases (Oracle Architecture)