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
77 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?
5The 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>, ...)
.
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';
0I 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.
1I 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