ORA-00932: inconsistent datatypes: expected - got CLOB

Posted by Sebrina Pilcher on Sunday, January 14, 2024

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 
7

7 Answers

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?

5

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>, ...).

1

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'; 
0

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.

1

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.

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 .

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.

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJoaGtxaGV9dISOqKmaZWBlhnR%2BjKKlnKeeqLa0wMSnq2ackamutcXPnqpmnailsqTAxJ1koKekYrCtu8E%3D

ncG1vNJzZmilnJ23b63MrGpnnJmctrWty6ianpmeqL2ir8SsZZynnWS8s62MaWdya2Jitq%2Bvzqeqoqukmru1ecOaq5qsqaWytHnEsaeem6SasW6zzq1knKSfl3upwMyl