SQLlightspeed logo SQLlightspeed
Oracle errors

ORA-00932: inconsistent datatypes

ORA-00932: inconsistent datatypes: expected X got Y

Two values that must have the same type do not. The message says what Oracle expected and what it got. Convert one side explicitly with TO_DATE, TO_CHAR or TO_NUMBER, or fix the expression.

What ORA-00932 means

Oracle converts some types implicitly, but not all pairs: a DATE cannot be compared with a NUMBER, and the branches of a CASE must have compatible types. The message names both sides, for example expected DATE got NUMBER. CLOB columns also trigger it in places that do not accept LOBs, such as DISTINCT or GROUP BY.

Common causes of ORA-00932

How to fix ORA-00932

  1. Read the expected and actual types in the message.
  2. Convert explicitly: TO_DATE, TO_CHAR or TO_NUMBER.
  3. Make every CASE branch return the same type.
  4. For CLOBs, compare with DBMS_LOB.COMPARE or use DBMS_LOB.SUBSTR where a short value is enough.

ORA-00932 example

CASE branches must agree:

-- ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
SELECT CASE WHEN status = 'A' THEN 'active' ELSE 0 END FROM customers;

-- Works
SELECT CASE WHEN status = 'A' THEN 'active' ELSE '0' END FROM customers;

In SQLlightspeed

When a statement fails, SQLlightspeed’s error panel shows the error code with a plain-language explanation and a suggestion for what to do next, together with Oracle’s own message and a link to Oracle’s documentation — for this error and dozens of other common ones.

Related errors

ORA-01722ORA-01861ORA-06502 All Oracle errors →

Try SQLlightspeed free for 30 days

Oracle documentation for ORA-00932. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.