Solved

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

Posted on 2009-07-10
14
9,103 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
Comment Utility
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
Comment Utility
0
 

Author Comment

by:ned_a
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Tried that but it doesn't work.
0
 
LVL 7

Expert Comment

by:fluglash
Comment Utility
show the output
0
 

Author Comment

by:ned_a
Comment Utility
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
Comment Utility
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
Comment Utility
0
 

Author Closing Comment

by:ned_a
Comment Utility
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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Value of 0's not appearing. 9 49
oracle query help 36 65
EXECUTE IMMEDIATE 5 34
automatic email alert 1 20
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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

743 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

18 Experts available now in Live!

Get 1:1 Help Now