ORA-00932: inconsistent datatypes: expected - got CLOB
OracleClobOracle Problem Overview
Considering that TEST_SCRIPT
is a CLOB
why when I run this simple query from SQL*PLUS on Oracle, I get the error:
ORA-00932: inconsistent datatypes: expected - got CLOB
I have been reading a lot of questions about the same error but none of those is running a direct query from SQLPLUS
UPDATE IMS_TEST
SET TEST_Category = 'just testing'
WHERE TEST_SCRIPT = 'something'
AND ID = '10000239'
Full example:
SQL> create table ims_test(
2 test_category varchar2(30),
3 test_script clob,
4 id varchar2(30)
5 );
Table created.
SQL> insert into ims_test values ('test1','something','10000239');
1 row created.
SQL> UPDATE IMS_TEST
2 SET TEST_Category = 'just testing'
3 WHERE TEST_SCRIPT = 'something'
4 AND ID = '10000239';
WHERE TEST_SCRIPT = 'something'
*
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected - got CLOB
Oracle Solutions
Solution 1 - Oracle
You can't put a CLOB in the WHERE clause. From the documentation:
> Large objects (LOBs) are not supported in comparison conditions. > However, you can use PL/SQL programs for comparisons on CLOB data.
If your values are always less than 4k, you can use:
UPDATE IMS_TEST
SET TEST_Category = 'just testing'
WHERE to_char(TEST_SCRIPT) = 'something'
AND ID = '10000239';
It is strange to search by a CLOB anyways.. could you not just search by the ID column?
Solution 2 - Oracle
The same error occurs also when doing SELECT DISTINCT ..., <CLOB_column>, ...
.
If this CLOB column contains values shorter than limit for VARCHAR2 in all the applicable rows you may use to_char(<CLOB_column>)
or concatenate results of multiple calls to DBMS_LOB.SUBSTR(<CLOB_column>, ...)
.
Solution 3 - Oracle
Take a substr of the CLOB and then convert it to a char:
UPDATE IMS_TEST
SET TEST_Category = 'just testing'
WHERE to_char(substr(TEST_SCRIPT, 1, 9)) = 'something'
AND ID = '10000239';
Solution 4 - Oracle
I just ran over this one and I found by accident that CLOBs can be used in a like query:
UPDATE IMS_TEST
SET TEST_Category = 'just testing'
WHERE TEST_SCRIPT LIKE '%something%'
AND ID = '10000239'
This worked also for CLOBs greater than 4K
The Performance won't be great but that was no problem in my case.
Solution 5 - Oracle
I found that selecting a clob
column in CTE caused this explosion. ie
with cte as (
select
mytable1.myIntCol,
mytable2.myClobCol
from mytable1
join mytable2 on ...
)
select myIntCol, myClobCol
from cte
where ...
presumably because oracle can't handle a clob in a temporary table.
Because my values were longer than 4K, I couldn't use to_char()
.
My work around was to select it from the final select
, ie
with cte as (
select
mytable1.myIntCol
from mytable1
)
select myIntCol, myClobCol
from cte
join mytable2 on ...
where ...
Too bad if this causes a performance problem.
Solution 6 - Oracle
The problem may lie in selected null values in combination with a CLOB-type column.
select valueVarchar c1 ,
valueClob c2 ,
valueVarchar c3 ,
valueVvarchar c4
of Table_1
union
select valueVarchar c1 ,
valueClob c2 ,
valueVarchar c3 ,
null c4
of table_2
I reworked the cursor. The first cursor is composed of four non-null columns. The second cursor selects three non-null columns. The null values were injected into the cursorForLoop .
Solution 7 - Oracle
In my case I was using EntityFramework and the column I was trying to use was called value
- i.e. a reserved keyword by oracle.