ORA-00932

ORA-00932: inconsistent datatypes: expected %s - got %s

What causes this error?

An attempt was made to perform an operation on incompatible data types. In some cases Oracle will attempt implicit conversion, but when it cannot be safely done, Oracle will raise this error.

How to fix it

The solution depends on the conversion required (look at the "expected %s - got %s" part).

For example, with: expected - got CLOB or expected - got NCLOB

Add TO_CHAR() to the N/CLOB columns to convert them to character fields. Alternatively, use functions like DBMS_LOB.SUBSTR() or DBMS_LOB.INSTR(). For example:

SQL> CREATE TABLE t1 (id NUMBER, c1 CLOB);
Table created.
SQL> INSERT INTO t1 VALUES (1, 'test string');
1 row created.

SQL> SELECT * FROM t1 WHERE c1 = 'test string';
SELECT * FROM t1 WHERE c1 = 'test string'
                      *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

SQL> SELECT  * FROM t1 WHERE TO_CHAR(c1) = 'test string';
        ID C1
---------- --------------------------------------------------------------------------------
         1 test string