ORA-00932: inconsistent datatypes: expected - got CLOB

Question

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 

Answer 1

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?