ORA-00932: inconsistent datatypes: expected - got CLOB

来源：互联网发布：java中毫秒微秒纳秒编辑：程序博客网时间：2024/06/07 04:27

今天要对一张大表进行分表，大表的定义大致如下（省略了一些）：

create table RUNLOG_DETAIL(  DETAIL_ID NUMBER(10) not null,  RECORD_ID        VARCHAR2(20),  FIELD_VALUE          VARCHAR2(4000),  FIELD_VALUE_CLOB     CLOB);create table RUNLOG_DETAIL_HIS(  DETAIL_ID NUMBER(10) not null,  RECORD_ID        VARCHAR2(20),  FIELD_VALUE          VARCHAR2(4000),  FIELD_VALUE_CLOB     CLOB);

在进行下列操作的时候出现ORA-00932的错误

select * from RUNLOG_DETAILminusselect * from RUNLOG_DETAIL_HIS;

在oracle 的联机文档中查到minus的限制：

Restrictions on the Set Operators The set operators are subject to the following restrictions:

The set operators are not valid on columns of type BLOB, CLOB,BFILE,VARRAY, or nested table.

想再深入的了解一下,执行下列语句同样报这个错

select distinct FIELD_VALUE_CLOB from RUNLOG_DETAIL;
揣测是distinct 的问题，union、minus、INTERSECT 用到了distinct, union all没有用到，实验下列语句没有错

select * from RUNLOG_DETAILunion allselect * from RUNLOG_DETAIL_HIS;

当实验union、INTERSECT 则同样的报错，结论：不能进行distinct clob。

另：union、minus、INTERSECT 用到了distinct, union all没有用到实验：

SQL> create table test1(a varchar2(10)); Table createdSQL> create table test2(a varchar2(10)); Table createdSQL> insert into test1 values(1); 1 row insertedSQL> insert into test1 values(1); 1 row insertedSQL> insert into test1 values(2); 1 row insertedSQL> insert into test1 values(2); 1 row insertedSQL> insert into test2 values(1); 1 row insertedSQL> commit; Commit completeSQL> select * from test1  2  minus  3  select * from test2; A----------2SQL> select * from test1  2  INTERSECT  3  select * from test2; A----------1SQL> select * from test1  2  union  3  select * from test2; A----------12SQL> select * from test1  2  union all  3  select * from test2; A----------11221