Solved

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

Posted on 2009-07-10
14
9,226 Views
Last Modified: 2013-12-19
Hi All,
I'm hoping that someone might be able to give me some advice on this problem or if possible help me resolve it.
i have a small program which queres the db and is used as part of a validation tool.
But when run on one of the servers I get the following error:
.
INFO: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-12705: Cannot access NLS data files or invalid environment specified

I do not get this error if I run the program remotely!!! So, if i run this program from my laptop, the program connects without any problems onto the db on the server.

I tried unsetting the NLS_lang environment variable on the server but I still get the same result.
I checked the Oracle and NLS environment variables on the server and compared them to those on a server which the program runs on without any errors and they are the same.

NLS_LANG=_.WE8ISO8859P1
ORACLE_BASE=/appl/oracle
ORACLE_HOME=/appl/oracle/product/10.2.0/db_1
PATH=<other entires>:/appl/oracle/product/10.2.0/db_1/bin

Server is x86_64 GNU/Linux

Any thoughts?
0
Comment
Question by:ned_a
  • 7
  • 7
14 Comments
 
LVL 7

Expert Comment

by:fluglash
ID: 24821823
ORA-12705 invalid or unknown NLS parameter value specified

Cause: There are two possible causes: Either an attempt was made to issue an ALTER SESSION statement with an invalid NLS parameter or value; or the NLS_LANG environment variable contains an invalid language, territory, or character set.

Action: Check the syntax of the ALTER SESSION statement and the NLS parameter, correct the syntax and retry the statement, or specify correct values in the NLS_LANG environment variable. For more information about the syntax of the ALTER SESSION statement, see the Oracle9i SQL Reference.

NLS_LANG=WE8ISO8859P1
0
 
LVL 7

Expert Comment

by:fluglash
ID: 24821863
0
 

Author Comment

by:ned_a
ID: 24821875
The problem seems to be restricted to running the program from a linux server.  We have a product which has a distributed install, database may not always be on the same server or same platform as the app.  I ran the program from a linux server which then tried to connect to a db on a solaris server and got the same problem.
0
 
LVL 7

Expert Comment

by:fluglash
ID: 24822248
i tried this:

C:\>set NLS_LANG=qqq
C:\>set nls_lang
NLS_LANG=qqq
C:\>sqlplus user/pwd@SID
SQL*Plus: Release 9.2.0.8.0 - Production on Fri Jul 10 15:47:19 2009
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
ERROR:
ORA-12705: invalid or unknown NLS parameter value specified

C:\>set NLS_LANG=AMERICAN_AMERICA.CL8MSWIN1251
C:\>sqlplus user/pwd@SID
SQL*Plus: Release 9.2.0.8.0 - Production on Fri Jul 10 15:47:49 2009
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

so there seems to be a problem in client machine`s NLS_LANG setting
0
 

Author Comment

by:ned_a
ID: 24823789
With the original env :
NLS_LANG=_.WE8ISO8859P1

I ran the folloing command:
<terminal> :sqlplus username/pwd@SID    

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 10 15:34:54 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL>

I still get the same problem when running the program from the linux server.
0
 
LVL 7

Expert Comment

by:fluglash
ID: 24837456
perfectly connected from linux server to oracle instance under windows

-bash-3.2$ sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jul 13 10:04:22 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn user/pwd@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = host)(PORT = 1521)))(CONNECT_DATA = (SID = TEST_SID)))
Connected.
SQL>

post your NLS_LANGUAGE and NLS_TERRITORY parameters
0
 

Author Comment

by:ned_a
ID: 24838486
This is from the database I'm trying to connect to.
NLS_LANGUAGE      AMERICAN
NLS_TERRITORY      AMERICA
NLS_CURRENCY      $
NLS_ISO_CURRENCY      AMERICA
NLS_NUMERIC_CHARACTERS      .,
NLS_CHARACTERSET      AL32UTF8
NLS_CALENDAR      GREGORIAN
NLS_DATE_FORMAT      DD-MON-RR
NLS_DATE_LANGUAGE      AMERICAN
NLS_SORT      BINARY
NLS_TIME_FORMAT      HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT      DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT      HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT      DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY      $
NLS_COMP      BINARY
NLS_LENGTH_SEMANTICS      BYTE
NLS_NCHAR_CONV_EXCP      FALSE
NLS_NCHAR_CHARACTERSET      AL16UTF16
NLS_RDBMS_VERSION      10.2.0.4.0

Linux and unix servers have same NLS_LANG environment variables:
NLS_LANG=_.WE8ISO8859P1

Is this the information that you were looking for?

This seems to be a specific problem when running from Linux servers.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 7

Expert Comment

by:fluglash
ID: 24846412
try this on your linux machine:
$export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
$sqlplus /nolog
SQL> conn usr/pwd@yourSID
0
 

Author Comment

by:ned_a
ID: 24847002
Tried that but it doesn't work.
0
 
LVL 7

Expert Comment

by:fluglash
ID: 24847015
show the output
0
 

Author Comment

by:ned_a
ID: 24847020
Should also point out at this point that the way I'm checking to see if my problem is resolved is to run the program from a Linux server. I've never had a problem using the sqlplus command from the CLI on the linux server. So, I can use NLS_LANG=_.WE8ISO8859P1 and NLS_LANG=AMERICAN_AMERICA.AL32UTF8 for sqlplus command and it'll work fine. But the program doesn't work with either.
0
 

Author Comment

by:ned_a
ID: 24847083
java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-12705: Cannot access NLS data files or invalid environment specified

   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
   at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
   at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:283)
   at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:278)
   at oracle.jdbc.driver.T4CTTIoauthenticate.receiveOauth(T4CTTIoauthenticate.ja    va:785)
   at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:362)
   at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:414)
   at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
   at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.jav    a:35)
   at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
   at java.sql.DriverManager.getConnection(libgcj.so.7rh)
   at java.sql.DriverManager.getConnection(libgcj.so.7rh)
   at sqlQuery.GatewayValidation.getConnection(Unknown Source)
   at sqlQuery.GatewayValidation.main(Unknown Source)
0
 
LVL 7

Accepted Solution

by:
fluglash earned 250 total points
ID: 24877365
0
 

Author Closing Comment

by:ned_a
ID: 31601992
Modified code to include the following:

// Save the original Java locale
Locale originalLocale = Locale.getDefault();
// Set "en_US" as the safe default
Locale.setDefault( Locale.US );
// Login; this will send AMERICAN_AMERICA to the database
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@myhost:myport:mysid","username", "password");
/* ...or whatever connection method you use */
// Restore original Java locale, if needed
Locale.setDefault( originalLocale );
// Set your preferred Oracle language and territory in the DB session
Statement stmt = conn.createStatement();
stmt.execute( "ALTER SESSION SET NLS_LANGUAGE='' NLS_TERRITORY=''");
// ...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle query help 29 77
Oracle SQL Select within a Where Clause 9 56
Web Service from a stored procdure oracle 10 49
Use of Exception to end a Loop 3 16
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now