Globalization

How to Resolve ORA-12705: invalid or unknown NLS parameter value specified

ORA-12705 tells you that your NLS settings are not correct, you have to fix them first before doing something to the database.

There’re 2 error patterns in this post for ORA-12705, they are:

  1. ORA-12705 BEFORE Connected
  2. ORA-12705 AFTER Connected

ORA-12705 before Connected

Let’s set a false NLS_LANG deliberately before connecting to the database, which is an environment variable that Oracle will pick up to use. As for the format of NLS_LANG, you may check NLS_LANG, How and Why

[oracle@test ~]$ export NLS_LANG=JAPAN_JAPAN.UTF8
[oracle@test ~]$ echo $NLS_LANG
JAPAN_JAPAN.UTF8

Tried to connect to the database.

[oracle@test ~]$ sqlplus /nolog
...
SQL> conn hr/hr
ERROR:
ORA-12705: Cannot access NLS data files or invalid environment specified


SQL> conn / as sysdba
ERROR:
ORA-12705: Cannot access NLS data files or invalid environment specified
SQL> exit

The error ORA-12705 showed that both connections were rejected by the database. Why?

Solutions

You have two choices, the first one is to unset NLS_LANG, the other is to set a correct language for your environment.

1. Unset NLS_LANG

The database will use the default NLS settings for your session if NLS_LANG is not set. Let’s see what languages are valid in the database by querying V$NLS_VALID_VALUES.

[oracle@test ~]$ unset NLS_LANG
[oracle@test ~]$ sqlplus / as sysdba
...
SQL> set pagesize 100;
SQL> select value from v$nls_valid_values where parameter = 'LANGUAGE' AND isdeprecated = 'FALSE' order by 1;

VALUE
----------------------------------------------------------------
ALBANIAN
AMERICAN
ARABIC
ASSAMESE
AZERBAIJANI
BANGLA
BELARUSIAN
BRAZILIAN PORTUGUESE
BULGARIAN
CANADIAN FRENCH
CATALAN
CROATIAN
CYRILLIC KAZAKH
CYRILLIC SERBIAN
CYRILLIC UZBEK
CZECH
DANISH
DUTCH
EGYPTIAN
ENGLISH
ESTONIAN
FINNISH
FRENCH
GERMAN
GERMAN DIN
GREEK
GUJARATI
HEBREW
HINDI
HUNGARIAN
ICELANDIC
INDONESIAN
IRISH
ITALIAN
JAPANESE
KANNADA
KOREAN
LATIN AMERICAN SPANISH
LATIN SERBIAN
LATIN UZBEK
LATVIAN
LITHUANIAN
MACEDONIAN
MALAY
MALAYALAM
MARATHI
MEXICAN SPANISH
NORWEGIAN
ORIYA
POLISH
PORTUGUESE
PUNJABI
ROMANIAN
RUSSIAN
SIMPLIFIED CHINESE
SLOVAK
SLOVENIAN
SPANISH
SWEDISH
TAMIL
TELUGU
THAI
TRADITIONAL CHINESE
TURKISH
UKRAINIAN
VIETNAMESE

66 rows selected.

SQL> exit

2. Set Correct NLS_LANG

As you can see, there’s no “JAPAN” in the complete list of valid NLS languages. The valid value that we should set is “JAPANESE”. So let’s do it.

[oracle@test ~]$ export NLS_LANG=JAPANESE_JAPAN.UTF8
[oracle@test ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on 金 5月 17 19:23:20 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> conn hr/hr
Connected.

As you can see, not only our connection is successful, but the displayed language of welcome message is also correct for Japanese now.

ORA-12705 after Connected

During the session, you still have chances to get ORA-12705 if were unaware of the NLS settings. Let’s try to change NLS_DATE_LANGUAGE setting for current session.

SQL> alter session set nls_date_language = 'Sweden';
ERROR:
ORA-12705: NLSデータファイルにアクセスできないか、無効な環境が指定されました

As you can see, we got ORA-12705 in Japanese. Next, we set NLS_DATE_LANGUAGE correctly with a valid value.

SQL> alter session set nls_date_language = 'Swedish';

Session altered.

The solution to ORA-12705 is always the same, just use a valid value for your NLS settings.

For more globalization support, please check Database Globalization Support Guide: Overview of Globalization Support.

Leave a Reply

Your email address will not be published. Required fields are marked *