ORA-00932 Inconsistent Datatypes - Complete Resolution Guide

ORA-00932: Inconsistent Datatypes

Error Overview

Error Text: ORA-00932: inconsistent datatypes: expected string got string

The ORA-00932 error occurs when Oracle encounters a datatype mismatch during SQL execution. This happens when you attempt to compare, assign, or operate on values with incompatible datatypes. This is one of the most common development errors in Oracle databases.

Common Causes

1. Comparing Incompatible Types

• Comparing VARCHAR2 to NUMBER without conversion
• Comparing DATE to VARCHAR2
• Using CLOB/BLOB in equality comparisons

2. INSERT/UPDATE Type Mismatches

• Inserting string values into NUMBER columns
• Date format mismatches
• LOB handling errors

3. Function Return Type Issues

• DECODE/CASE returning mixed datatypes
• NVL with incompatible argument types
• Aggregate functions with wrong column types

4. UNION/UNION ALL Mismatches

• Column datatype differences between SELECT statements
• Different number precision/scale
• Character vs numeric columns in same position

Common Scenarios and Solutions

Scenario 1: CLOB Comparison Error

-- ERROR: Comparing CLOB directly
SELECT * FROM documents
WHERE clob_column = 'search text';
-- ORA-00932: inconsistent datatypes: expected - got CLOB

-- SOLUTION: Use DBMS_LOB.INSTR or TO_CHAR (for small CLOBs)
SELECT * FROM documents
WHERE DBMS_LOB.INSTR(clob_column, 'search text') > 0;

-- Or for exact match on small CLOBs (< 4000 chars)
SELECT * FROM documents
WHERE DBMS_LOB.SUBSTR(clob_column, 4000, 1) = 'search text';

Scenario 2: DECODE/CASE Mixed Types

-- ERROR: Mixed types in DECODE
SELECT DECODE(status,
              'A', 'Active',
              'I', 0,        -- NUMBER mixed with VARCHAR2
              'Unknown')
FROM users;
-- ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

-- SOLUTION: Ensure consistent return types
SELECT DECODE(status,
              'A', 'Active',
              'I', '0',      -- Convert to string
              'Unknown')
FROM users;

-- Or use CASE with explicit casting
SELECT CASE status
         WHEN 'A' THEN 'Active'
         WHEN 'I' THEN TO_CHAR(0)
         ELSE 'Unknown'
       END
FROM users;

Scenario 3: NVL Type Mismatch

-- ERROR: NVL with different types
SELECT NVL(commission_pct, 'N/A') FROM employees;
-- ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

-- SOLUTION: Convert to common type
SELECT NVL(TO_CHAR(commission_pct), 'N/A') FROM employees;

-- Or use numeric placeholder
SELECT NVL(commission_pct, 0) FROM employees;

Scenario 4: UNION Column Mismatch

-- ERROR: Different column types in UNION
SELECT employee_id, hire_date FROM employees
UNION
SELECT department_id, department_name FROM departments;
-- ORA-00932: inconsistent datatypes: expected DATE got CHAR

-- SOLUTION: Align datatypes with conversion
SELECT employee_id, TO_CHAR(hire_date, 'YYYY-MM-DD') FROM employees
UNION
SELECT department_id, department_name FROM departments;

Scenario 5: Implicit Conversion Failure

-- ERROR: String to number conversion fails
SELECT * FROM orders
WHERE order_id = '12345ABC';  -- order_id is NUMBER
-- ORA-00932 or ORA-01722 depending on context

-- SOLUTION: Use proper datatype
SELECT * FROM orders
WHERE order_id = 12345;

-- Or if column should be VARCHAR2, fix the comparison
SELECT * FROM orders
WHERE TO_CHAR(order_id) = '12345ABC';

Diagnostic Queries

Identify Column Datatypes

-- Check table column datatypes
SELECT column_name, data_type, data_length, data_precision, data_scale
FROM all_tab_columns
WHERE owner = 'SCHEMA_NAME'
  AND table_name = 'TABLE_NAME'
ORDER BY column_id;

-- Compare columns between tables for UNION operations
SELECT
  a.column_name,
  a.data_type as table1_type,
  b.data_type as table2_type,
  CASE WHEN a.data_type != b.data_type THEN 'MISMATCH' ELSE 'OK' END as status
FROM all_tab_columns a
JOIN all_tab_columns b
  ON a.column_id = b.column_id
WHERE a.owner = 'SCHEMA' AND a.table_name = 'TABLE1'
  AND b.owner = 'SCHEMA' AND b.table_name = 'TABLE2';

Find LOB Columns

-- Identify LOB columns that might cause issues
SELECT owner, table_name, column_name, data_type
FROM all_tab_columns
WHERE data_type IN ('CLOB', 'BLOB', 'NCLOB', 'LONG')
  AND owner = 'SCHEMA_NAME'
ORDER BY table_name, column_name;

Check View Definitions for Type Issues

-- Review view column types
SELECT column_name, data_type, data_type_owner
FROM all_tab_columns
WHERE owner = 'SCHEMA_NAME'
  AND table_name = 'VIEW_NAME';

Resolution Steps

1. Explicit Type Conversion

-- String to Number
TO_NUMBER('123')
TO_NUMBER('1,234.56', '9,999.99')

-- Number to String
TO_CHAR(12345)
TO_CHAR(1234.56, '9999.99')

-- String to Date
TO_DATE('2024-01-15', 'YYYY-MM-DD')
TO_TIMESTAMP('2024-01-15 10:30:00', 'YYYY-MM-DD HH24:MI:SS')

-- Date to String
TO_CHAR(SYSDATE, 'YYYY-MM-DD')
TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF')

2. Handle LOB Comparisons

-- For CLOB searching
SELECT * FROM documents
WHERE DBMS_LOB.INSTR(content_clob, 'search term', 1, 1) > 0;

-- For CLOB comparison (exact match)
SELECT * FROM documents
WHERE DBMS_LOB.COMPARE(content_clob, TO_CLOB('exact text')) = 0;

-- For BLOB comparison
SELECT * FROM files
WHERE DBMS_LOB.COMPARE(file_blob, other_blob) = 0;

-- Convert small CLOB to VARCHAR2
SELECT * FROM documents
WHERE TO_CHAR(content_clob) LIKE '%pattern%';  -- Only for < 4000 chars

3. Fix DECODE/CASE Statements

-- Ensure all branches return same type
SELECT
  CASE
    WHEN status = 1 THEN 'Active'
    WHEN status = 2 THEN 'Inactive'
    WHEN status = 3 THEN 'Pending'
    ELSE 'Unknown'  -- All return VARCHAR2
  END as status_desc
FROM accounts;

-- Use CAST for explicit typing
SELECT
  CASE
    WHEN flag = 'Y' THEN CAST(1 AS NUMBER)
    ELSE CAST(0 AS NUMBER)
  END as flag_num
FROM settings;

4. Fix UNION Queries

-- Align all columns to compatible types
SELECT
  TO_CHAR(id) as id,
  name,
  TO_CHAR(created_date, 'YYYY-MM-DD') as date_str
FROM table1
UNION ALL
SELECT
  code as id,
  description as name,
  effective_date as date_str
FROM table2;

Prevention Strategies

1. Use Explicit Conversions

-- Always be explicit about type conversions
-- Instead of relying on implicit conversion:
WHERE order_date = '2024-01-15'  -- Implicit

-- Use explicit conversion:
WHERE order_date = TO_DATE('2024-01-15', 'YYYY-MM-DD')  -- Explicit

2. Validate Input Data

-- Create validation function
CREATE OR REPLACE FUNCTION is_number(p_str VARCHAR2) RETURN NUMBER IS
  v_num NUMBER;
BEGIN
  v_num := TO_NUMBER(p_str);
  RETURN 1;
EXCEPTION
  WHEN VALUE_ERROR THEN RETURN 0;
END;
/

-- Use in queries
SELECT * FROM data
WHERE is_number(string_column) = 1
  AND TO_NUMBER(string_column) > 100;

3. Consistent Column Types in Design

-- Use consistent types for similar data
-- Define standards:
-- IDs: NUMBER(12) or VARCHAR2(36) for UUIDs
-- Dates: DATE or TIMESTAMP
-- Flags: VARCHAR2(1) with 'Y'/'N' or NUMBER(1) with 1/0
-- Money: NUMBER(19,4)

4. Code Review Checklist

• Check all DECODE/CASE return types match
• Verify UNION/UNION ALL column alignment
• Review NVL/COALESCE argument types
• Validate LOB column operations
• Test with edge case data

Common Error Variations

Error Message	Cause	Solution
expected NUMBER got CHAR	String compared to number	Use TO_NUMBER()
expected DATE got CHAR	String compared to date	Use TO_DATE()
expected - got CLOB	Direct CLOB comparison	Use DBMS_LOB functions
expected CHAR got NUMBER	Number in string context	Use TO_CHAR()
expected CHAR got CLOB	CLOB in VARCHAR2 context	Use TO_CHAR() or DBMS_LOB

Related Errors

• ORA-01722 - Invalid number (conversion failure)
• ORA-01858 - Non-numeric character in date
• ORA-01861 - Literal does not match format string
• ORA-06502 - PL/SQL numeric or value error

Best Practices Summary

1. Always use explicit type conversion functions (TO_CHAR, TO_NUMBER, TO_DATE)
2. Match datatypes in conditional expressions (DECODE, CASE, NVL)
3. Handle LOB columns specially - use DBMS_LOB package
4. Verify UNION column alignment before combining queries
5. Test with boundary data including NULLs and edge cases
6. Document expected datatypes in stored procedures and functions