Ошибка 12705 oracle

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

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;

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

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';

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.

I got ” ORA-12705: Cannot access NLS data files or invalid environment specified”  error in Oracle database.

ORA-12705: Cannot access NLS data files or invalid environment specified

Details of error are as follows.

ORA-12705: "invalid or unknown NLS parameter value specified"

Cause: There are two possible causes:

- An attempt was made to issue an ALTER SESSION statement with an invalid NLS parameter or value.

- The NLS_LANG environment variable contains an invalid language, territory, or character set.

Action: Check the syntax of the ALTER SESSION command and the NLS parameter, correct the 
syntax and retry the statement, or specify correct values in the NLS_LANG environment variable.

invalid or unknown NLS parameter value specified

This ORA-12705 errors are related with two possible causes:

– An attempt was made to issue an ALTER SESSION statement with an invalid NLS parameter or value.

– The NLS_LANG environment variable contains an invalid language, territory, or character set.

Action: Check the syntax of the ALTER SESSION command and the NLS parameter, correct the syntax and retry the statement, or specify correct values in the NLS_LANG environment variable.

Firstly Unset the NLS_LANG environment variable,

Windows – The NLS_LANG must be unset in the Windows registry. Look for the NLS_LANG subkey in the registry at \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE and rename it.

Linux/UNIX – Here you simply issue the Linux command “unset NLS_LANG”

You can set it as follows.

[MSDB]/home/oracle $ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9
[MSDB]/home/oracle $ 
[MSDB]/home/oracle $ echo $NLS_LANG
AMERICAN_AMERICA.WE8ISO8859P9

And you can set the nls_date_language parameter as follows. ( you can set the value according to your language )

SQL> alter session set nls_date_language = 'AMERICAN';

Session altered.

SQL> show parameter NLS_DATE_LANGUAGE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_date_language string AMERICAN
SQL>

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )