Oracle ORA-12704 Error: Causes and Solutions Complete Guide

ORA-12704: Character Set Mismatch — Causes, Fixes, and Prevention

ORA-12704 is thrown by Oracle when a SQL statement attempts to compare, concatenate, or operate on string values that belong to different character sets. The most common scenario is mixing VARCHAR2 (database character set) and NVARCHAR2 (national character set) columns without explicit conversion. This error can surface in queries, PL/SQL blocks, and across database links, and if left unaddressed, it can halt application functionality entirely.

---

Top 3 Causes

1. Mixing VARCHAR2 and NVARCHAR2 Columns

Oracle stores VARCHAR2 using the database character set (e.g., AL32UTF8) and NVARCHAR2 using the national character set (e.g., AL16UTF16). Directly comparing or joining these two types without conversion triggers ORA-12704.

-- Triggers ORA-12704
SELECT *
FROM employees
WHERE varchar2_col = nvarchar2_col;

-- Fix: Explicitly convert to the same type
SELECT *
FROM employees
WHERE TO_NCHAR(varchar2_col) = nvarchar2_col;

-- Alternative fix
SELECT *
FROM employees
WHERE varchar2_col = TO_CHAR(nvarchar2_col);

2. Missing N Prefix When Comparing Literals to NCHAR/NVARCHAR2 Columns

When you compare a plain string literal ('value') to an NVARCHAR2 or NCHAR column, Oracle treats the literal as VARCHAR2, causing a character set conflict. Always prefix string literals with N when working with national character set columns.

-- Triggers ORA-12704
SELECT * FROM customers WHERE national_name = 'John';

-- Fix: Use the N prefix for the literal
SELECT * FROM customers WHERE national_name = N'John';

-- PL/SQL example with correct typing
DECLARE
    v_name NVARCHAR2(100) := N'John';
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM customers
    WHERE national_name = v_name;
    DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;
/

3. Cross-Database Queries via DB Link with Mismatched Character Sets

When querying a remote database through a DB Link where the remote database uses a different character set than the local one, Oracle may be unable to implicitly resolve the mismatch, resulting in ORA-12704.

-- Triggers ORA-12704 if remote DB has a different character set
SELECT l.name, r.department
FROM local_employees l
JOIN remote_table@my_db_link r ON l.id = r.id
WHERE l.name = r.manager_name;

-- Fix: Use CONVERT() to align character sets explicitly
SELECT l.name, r.department
FROM local_employees l
JOIN remote_table@my_db_link r ON l.id = r.id
WHERE l.name = CONVERT(r.manager_name, 'AL32UTF8', 'KO16MSWIN949');

-- Check character sets of both databases
SELECT parameter, value
FROM nls_database_parameters
WHERE parameter IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

---

Quick Fix Solutions

• Use TO_NCHAR() to convert VARCHAR2 to the national character set.
• Use TO_CHAR() to convert NVARCHAR2 back to the database character set.
• Always use the N prefix (N'literal') when comparing string literals to NCHAR/NVARCHAR2 columns.
• Use CONVERT(value, dest_charset, src_charset) for cross-database-link comparisons.

-- Utility query: find tables mixing VARCHAR2 and NVARCHAR2 columns
SELECT table_name,
       SUM(CASE WHEN data_type IN ('CHAR','VARCHAR2') THEN 1 ELSE 0 END) AS char_cols,
       SUM(CASE WHEN data_type IN ('NCHAR','NVARCHAR2') THEN 1 ELSE 0 END) AS nchar_cols
FROM all_tab_columns
WHERE owner = 'YOUR_SCHEMA'
GROUP BY table_name
HAVING SUM(CASE WHEN data_type IN ('NCHAR','NVARCHAR2') THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN data_type IN ('CHAR','VARCHAR2') THEN 1 ELSE 0 END) > 0
ORDER BY table_name;

---

Prevention Tips

• Standardize on one character type. Prefer VARCHAR2 with AL32UTF8 as the database character set for all new schemas. Avoid NCHAR/NVARCHAR2 unless there is a hard requirement, and document the rationale when you do use them.
• Enforce type checks in code reviews and CI pipelines. Add the detection query above to your pre-deployment checklist to catch mixed character type columns before they reach production. When using DB Links, document the remote database character set and mandate explicit CONVERT() calls in all cross-link queries.

---

Related Errors

Error Code	Description
ORA-12705	Invalid NLS environment — often accompanies ORA-12704
ORA-06502	Buffer too small — can follow character set conversion that expands data length
ORA-12899	Value too large for column — triggered when converted data exceeds column size

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.