DEV Community

umzzil nng
umzzil nng

Posted on Originally published at oraerror.com

Oracle ORA-12714 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12714: Invalid National Character Set Specified

ORA-12714 is thrown by Oracle Database when an unsupported character set is specified as the National Character Set. Oracle strictly permits only two values for the national character set: AL16UTF16 and UTF8 — anything else triggers this error immediately. It most commonly surfaces during database creation or when attempting to alter NLS-related parameters incorrectly.

Top 3 Causes & SQL Examples

Cause 1: Wrong Value in CREATE DATABASE Statement

Specifying an unsupported character set name (e.g., a regional encoding or a typo) in the NATIONAL CHARACTER SET clause will instantly raise ORA-12714.

-- WRONG: Causes ORA-12714
CREATE DATABASE mydb
  CHARACTER SET AL32UTF8
  NATIONAL CHARACTER SET KO16MSWIN949;  -- Not allowed

-- CORRECT: Use AL16UTF16 (recommended)
CREATE DATABASE mydb
  CHARACTER SET AL32UTF8
  NATIONAL CHARACTER SET AL16UTF16;

-- CORRECT: UTF8 is also valid (legacy support)
CREATE DATABASE mydb
  CHARACTER SET AL32UTF8
  NATIONAL CHARACTER SET UTF8;
Enter fullscreen mode Exit fullscreen mode

Cause 2: Attempting to ALTER NLS_NCHAR_CHARACTERSET Directly

Some DBAs try to change the national character set post-creation using ALTER SYSTEM or ALTER DATABASE, which is not supported through standard commands.

-- WRONG: This will fail with ORA-12714 or related errors
ALTER SYSTEM SET NLS_NCHAR_CHARACTERSET = 'KO16MSWIN949';

-- CORRECT: First, check the current national character set
SELECT PARAMETER, VALUE
FROM   NLS_DATABASE_PARAMETERS
WHERE  PARAMETER = 'NLS_NCHAR_CHARACTERSET';

-- To change it properly, use Oracle DMU or csscan utility
-- Never attempt a brute-force update to the data dictionary
Enter fullscreen mode Exit fullscreen mode

Cause 3: Typos or Invalid Values in Automation Scripts

In CI/CD pipelines or manual scripts, copy-paste errors such as AL16UTF-16, al16utf16 (lowercase), or UTF-8 (with hyphen) are common culprits.

-- Validate allowed national character sets before scripting
SELECT *
FROM   V$NLS_VALID_VALUES
WHERE  PARAMETER = 'NCHARACTERSET';

-- Verify charset name by ID
SELECT NLS_CHARSET_NAME(2000) AS CHARSET FROM DUAL;  -- Returns AL16UTF16
SELECT NLS_CHARSET_NAME(871)  AS CHARSET FROM DUAL;  -- Returns UTF8

-- Quick sanity check on current DB settings
SELECT PARAMETER, VALUE
FROM   V$NLS_PARAMETERS
WHERE  PARAMETER = 'NLS_NCHAR_CHARACTERSET';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  1. If creating a new database, replace any invalid NATIONAL CHARACTER SET value with AL16UTF16 (preferred for all new deployments on Oracle 12c and later).
  2. If the database already exists, do not attempt to change the national character set using ALTER commands. Use Oracle's official Database Migration Assistant for Unicode (DMU) or the csscan utility to assess and migrate safely.
  3. If using scripts or automation, add an input validation guard that rejects any value not in ('AL16UTF16', 'UTF8') before executing the database creation command.

Prevention Tips

  • Standardize your DB creation templates: Maintain a version-controlled, peer-reviewed template script where NATIONAL CHARACTER SET AL16UTF16 is hardcoded. Treat any deviation as a change request requiring approval.
  • Enable auditing on critical DDL: Use Oracle Unified Auditing to log all ALTER DATABASE and ALTER SYSTEM commands, so unauthorized or accidental NLS changes are caught early and traceable.

Related Oracle Errors

Error Code Description
ORA-12705 Invalid NLS parameter value (e.g., bad NLS_LANGUAGE)
ORA-12712 New character set must be a superset of old one
ORA-12899 Value too large for column — can occur with misconfigured NCHAR types

📖 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.

Top comments (0)