Solving ORA-00932: Inconsistent datatypes: expected “type1” but got “type2”

This is an Oracle Database error that happens when Oracle expects one type of data (like a number, date, or string) but finds a different, incompatible type during SQL execution.

What Triggers It

1. Mismatched Data Types in Expressions or Comparisons
   • Comparing a VARCHAR2 (string) column to a NUMBER column without proper conversion.
   • Example: WHERE salary = 'ABC'.
2. Invalid Function Arguments
   • Passing a string to a function that expects a number, or vice versa.
   • Example: TO_CHAR(123, SYSDATE) – here SYSDATE is a date, not a number format.
3. UNION or UNION ALL Queries with Different Column Types
   • Combining queries where corresponding columns don’t share the same data type.
   • Example: First query returns a NUMBER, second query returns a VARCHAR2 in the same column position.
4. INSERT or UPDATE with Wrong Type
   • Trying to insert a string into a numeric column.
   • Example: INSERT INTO employees (employee_id) VALUES ('ABC').
5. PL/SQL Assignments or Bind Variables
   • Assigning a cursor column of type DATE to a VARCHAR2 variable without conversion.

How to Solve It

1. Check Column and Variable Types
   • Use DESC table_name to inspect column definitions.
   • Ensure both sides of a comparison or assignment are compatible.
2. Use Explicit Conversion Functions
   • TO_NUMBER(string) – convert string to number.
   • TO_CHAR(date_or_number, format) – convert date/number to string.
   • TO_DATE(string, format) – convert string to date.
3. Align Data Types in Set Operations
   • In UNION, INTERSECT, or MINUS, cast columns to the same type: SELECT CAST(emp_id AS VARCHAR2(10)) FROM employees UNION SELECT emp_code FROM contractors;
4. Fix Wrong Inserts or Updates
   • Make sure the value matches the column type. Convert if needed: INSERT INTO employees (hire_date) VALUES (TO_DATE('2025-09-18','YYYY-MM-DD'));
5. Check PL/SQL Variables
   • Declare variables with correct data types or use conversions.

Real-Life Examples

1. Comparing a Number with a String

-- employees.salary is NUMBER
SELECT * FROM employees WHERE salary = 'high';  

Oracle expects NUMBER, but gets a VARCHAR2.

Fix:

-- Either compare with a number:
SELECT * FROM employees WHERE salary = 50000;
-- Or convert appropriately if stored as string elsewhere.

2. UNION with Mismatched Types

SELECT emp_id FROM employees      -- emp_id is NUMBER
UNION
SELECT emp_code FROM contractors; -- emp_code is VARCHAR2

Oracle can’t union a number and string.

Fix:

SELECT TO_CHAR(emp_id) FROM employees
UNION
SELECT emp_code FROM contractors;

3. Inserting Wrong Data

-- hire_date is DATE
INSERT INTO employees (hire_date) VALUES ('2025-09-18');

Oracle sees a string, not a date.

Fix:

INSERT INTO employees (hire_date)
VALUES (TO_DATE('2025-09-18','YYYY-MM-DD'));

4. Function Argument Type Mismatch

-- TO_CHAR expects a number/date then format string, but SYSDATE is used incorrectly
SELECT TO_CHAR(123, SYSDATE) FROM dual;

Wrong argument types.

Fix:

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;

Summary in Simple Terms

• Oracle is strict about data types.
• This error means “I expected apples but you gave me oranges.”
• It happens in comparisons, unions, inserts, or functions where types don’t match.
• Solution:
  1. Check column and variable types.
  2. Use the correct conversion (TO_NUMBER, TO_CHAR, TO_DATE).
  3. Make sure combined queries use matching types.
  4. Insert/update values that match the column definitions.

By consistently aligning your data types or using explicit conversions, you can avoid ORA-00932.