The other day I got this annoying oracle error: ORA-12705: Cannot access NLS data files or invalid environment specified. Funny thing I wasn’t trying to access NLS data files
asked Oct 8, 2011 at 23:03
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.
Fix:
Unset the NLS_LANG environment variable
Windows — The NLS_LANG must be unset in the Windows registry (re-named is best). Look for the NLS_LANG subkey in the registry at HKEY_LOCAL_MACHINESOFTWAREORACLE, and rename it.
Linux/UNIX — Here you simply issue the Linux command «unset NLS_LANG»
answered Oct 8, 2011 at 23:04
Shawn BowerShawn Bower
1,1471 gold badge11 silver badges18 bronze badges
0
I have Oracle 10g XE and Windows 7. I resolved this as follows:
Go to Control panel > Regional and language options > Format and set your language.
answered Aug 20, 2012 at 20:24
3
Follow this procedure to set the NLS_LANG environment variable for Oracle databases.
To set the NLS_LANG environment variable for Oracle databases
Determine the NLS_LANG value.
In the data warehouse database, run the command
SELECT * FROM V$NLS_PARAMETERS
Make a note of the NLS_LANG value, which is in the format [NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET].
For example: American_America.UTF8
For Windows:
Navigate to Control Panel > System and click the Advanced tab. Click Environment Variables.
In System variables section, click New.
In the Variable Name field, enter NLS_LANG.
In the Variable Value field, enter the NLS_LANG value that was returned in Step 1.
The format for the NLS_LANG value should be [NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET].
For example: American_America.UTF8.
For UNIX, set the variable as shown below:
setenv NLS_LANG
For example: setenv NLS_LANG American_America.UTF8.
If your data is 7-bit or 8-bit ASCII and the Informatica Server is running on UNIX, then set
NLS_LANG _.WE8ISO8859P1
CAUTION: Make sure you set the NLS_LANG variable correctly, as stated in this procedure, or your data will not display correctly.
Reboot the machine after creating the variable.
answered Jun 15, 2015 at 10:55
जयदेवजयदेव
511 bronze badge
In my case, I was creating a database adapter in the oracle weblogic console, solve the problem by doing the following configuration.
Windows -> control panel -> region: 1. Change the format to English (United States) date format M / d / yy and 2. Change my address to the United States. Perform this configuration because my machine was configured with the Spanish language, then restart the computer and I worked without problems.
answered Oct 13, 2017 at 22:29
2
After addition of path in Environment variable of Oracle Instant client the Oracle SQLPLUSW goes to Oracle Instant client and for that client it required to set NLS LANG AS American_America.UTF8
answered Jun 15, 2015 at 12:46
जयदेवजयदेव
511 bronze badge
You can also just set the NLS config in your oracle settings
answered Oct 23, 2011 at 16:24
In case if above solutions not helped, can try my solution. I just replaced my latest version of Ojdbc14.jar
to an older version Ojdbc5.jar
. It helped me to solve my issue.
answered Nov 8, 2017 at 15:06
YithirashYithirash
3673 gold badges6 silver badges18 bronze badges
1
I was trying to configure my Data Source on WebLogic 12c that was pointing to an 11g Database and I kept getting this error. I found out that WL 12c is not compatible with oracle 11g databases.
I know it is not related but I am sure someone else will find this useful.
answered Nov 3, 2021 at 10:04
На днях я получил эту досадную ошибку оракула: ORA-12705: не удается получить доступ к файлам данных NLS или недействительной заданной среде. Забавно, что я не пытался получить доступ к файлам данных NLS
Ответ 1
Возможны две причины:
Была предпринята попытка выдать инструкцию ALTER SESSION с недопустимым параметром или значением NLS.
Переменная среды NLS_LANG содержит недопустимый язык, территорию или набор символов.
Fix:
Отменить переменную среды NLS_LANG
Windows. NLS_LANG должен быть удален в реестре Windows (лучше всего переименовать). Найдите подраздел NLS_LANG в реестре в HKEY_LOCAL_MACHINESOFTWAREORACLE и переименуйте его.
Linux/UNIX — здесь вы просто выдаете команду Linux «unset NLS_LANG»
Ответ 2
У меня есть Oracle 10g XE и Windows 7. Я решил это следующим образом:
Перейдите в Панель управления > Региональные и языковые параметры > Отформатируйте и установите язык.
Ответ 3
Следуйте этой процедуре, чтобы установить переменную среды NLS_LANG для баз данных Oracle.
Чтобы установить переменную среды NLS_LANG для баз данных Oracle
Определите значение NLS_LANG.
В базе данных хранилища данных выполните команду
SELECT * FROM V $NLS_PARAMETERS
Запишите значение NLS_LANG, которое находится в формате [NLS_LANGUAGE] _ [NLS_TERRITORY]. [NLS_CHARACTERSET].
Например: American_America.UTF8
Для Windows:
Перейдите в «Панель управления» > «Система» и перейдите на вкладку «Дополнительно». Выберите переменные среды.
В разделе «Системные переменные» нажмите «Создать».
В поле Имя переменной введите NLS_LANG.
В поле «Значение переменной» введите значение NLS_LANG, которое было возвращено на шаге 1.
Формат значения NLS_LANG должен быть [NLS_LANGUAGE] _ [NLS_TERRITORY]. [NLS_CHARACTERSET].
Например: American_America.UTF8.
Для UNIX установите переменную, как показано ниже:
setenv NLS_LANG
Например: setenv NLS_LANG American_America.UTF8.
Если ваши данные являются 7-битными или 8-разрядными ASCII, а Informatica Server запущен в UNIX, установите
NLS_LANG _.WE8ISO8859P1
ПРЕДУПРЕЖДЕНИЕ. Убедитесь, что вы правильно задали переменную NLS_LANG, как указано в этой процедуре, или ваши данные будут отображаться неправильно.
Перезагрузите компьютер после создания переменной.
Ответ 4
Вы также можете просто установить конфигурацию NLS в настройках оракула
Ответ 5
После добавления пути в переменную среды Oracle Instant client Oracle SQLPLUSW переходит к клиенту Oracle Instant, и для этого клиента требуется установить NLS LANG AS American_America.UTF8
Ответ 6
В моем случае я создавал адаптер базы данных в консоли web-браузера oracle, решая проблему, выполняя следующую конфигурацию.
Windows → панель управления → регион: 1. Измените формат на английский (США) формат даты M/d/yy и 2. Измените мой адрес в Соединенные Штаты. Выполните эту настройку, потому что моя машина была настроена на испанский язык, затем перезагрузите компьютер, и я работал без проблем.
Ответ 7
Если вышеприведенные решения не помогли, можете попробовать мое решение. Я только что заменил последнюю версию Ojdbc14.jar
на более старую версию Ojdbc5.jar
. Это помогло мне решить мою проблему.
I have an error trying to connect to oracle database:
Query execution failed
Reason:
SQL Error [12705] [72000]: ORA-12705: Cannot access NLS data files or invalid environment specified
ORA-02063: preceding line
My platform is a MacOs running obviously over a Mac, try to connect to remote oracle database. How can I fix this problem?
asked Feb 1, 2019 at 17:24
1
If you are using SQL Developer you have to follow this steps:
- Open SQL Developer package content. Go to Applications, right click on SQL Developer and select «Show Package Contents».
- Go to
Contents/Resources/sqldeveloper/sqldeveloper/bin/
- Open
sqldeveloper.conf
using a text editor. - Add the following lines:
# Options to avoid "ORA-12705: Cannot access NLS data files or invalid environment specified."
AddVMOption -Duser.language=en
AddVMOption -Duser.region=US
AddVMOption -Duser.country=en
- Restart SQL Developer
answered Feb 1, 2019 at 17:31
rubenlop88rubenlop88
4,1612 gold badges27 silver badges32 bronze badges
0
If you are here and using any on the Jetbrain Products (Pycharm/IntellJ IDEA/DataGrip)
- Open the properties of the connection
- Under Advanced, add the line below in the VM Options (without any quotes)
-Duser.language=en
-Duser.country=US
answered Mar 3, 2021 at 8:33
John KitonyoJohn Kitonyo
2,0091 gold badge14 silver badges17 bronze badges
With Maven try to add parameters:
-Duser.language=en -Duser.country=US
answered Mar 24, 2022 at 12:31
stakahopstakahop
9219 silver badges18 bronze badges
If you’re using oracle instant client for windows then remove the NSL variable from the windows environment, your issue will be resolved.
Henry Ecker♦
34k18 gold badges38 silver badges56 bronze badges
answered Oct 27, 2021 at 13:23
RajeshRajesh
5231 gold badge8 silver badges25 bronze badges
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:
- ORA-12705 BEFORE Connected
- 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.
I am getting this error while trying to create a connection pool, on my Oracle database, Oracle 10gR2.
java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-12705: Cannot access NLS data files or invalid environment specified
I am able to connect to the database over sqlplus & iSQLPlus client, but when I try to connect using this Java program, I get this error just when the connection pool is to be initialised and it does not initialise the connection pool.
Can someone please help me resolving it?
DB Version: Oracle version 10.2.0.1
OS: RHEL 4.0
Here is a barebone, java code which is throwing this error, while connecting to my database.
import java.sql.*;
public class connect{
public static void main(String[] args) {
Connection con = null;
CallableStatement cstmt = null;
String url = "jdbc:oracle:thin:@hostname:1521:oracle";
String userName = "username";
String password = "password";
try
{
System.out.println("Registering Driver ...");
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
System.out.println("Creating Connection ...");
con = DriverManager.getConnection(url, userName, password);
System.out.println("Success!");
} catch(Exception ex) {
ex.printStackTrace(System.err);
} finally {
if(cstmt != null) try{cstmt.close();}catch(Exception _ex){}
if(con != null) try{con.close();}catch(Exception _ex){}
}
}
}