ORA-00932: inconsistent datatypes
An expression combines types Oracle can't convert between, such as comparing a CLOB with =, or mixing DATE and NUMBER where one type is expected.
- Error code
ORA-00932- Category
- Data values and conversion
What it means
ORA-00932 means a value of one datatype was used where an incompatible type was required, for example ORA-00932: inconsistent datatypes: expected NUMBER got DATE. LOBs are a common source: a CLOB can't be compared with =, used in GROUP BY, ORDER BY or DISTINCT.
Newer releases word it as expression failed_expression is of data type expression_data_type, which is incompatible with expected data type expected_data_type.
Common causes
- Comparing or grouping by a
CLOBorBLOBcolumn. CASEorDECODEbranches that return different types.UNIONmembers whose columns have incompatible types in the same position.- Passing a value of the wrong type to a function.
How to fix it
-
Compare LOBs with
DBMS_LOBfunctions, or convert a short CLOB withTO_CHARorDBMS_LOB.SUBSTR.SELECT * FROM docs WHERE DBMS_LOB.COMPARE(body, TO_CLOB('hello')) = 0; SELECT DISTINCT DBMS_LOB.SUBSTR(body, 4000, 1) FROM docs; -
Make every
CASEbranch andUNIONcolumn return the same type, with explicit conversions.SELECT CASE WHEN qty > 0 THEN TO_CHAR(qty) ELSE 'none' END FROM stock;
Related errors
ORA-01722invalid numberORA-06502PL/SQL: numeric or value error8114SQL Server: Error converting data type42804PostgreSQL: datatype mismatch
More data values and conversion errors
ORA-12899value too large for columnORA-01401inserted value too large for columnORA-01438value larger than specified precisionORA-01476divisor is equal to zeroORA-01843not a valid monthORA-01861literal does not match format stringORA-01830date format picture ends before end of input
The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.
Work on Oracle in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.