Solved

Connecting to Oracle, sqlplus error ORA-12705: invalid or unknown NLS parameter value specified

Posted on 2004-04-18
8
35,029 Views
Last Modified: 2011-08-18
Hi,
I have installed Oracle 9i on a Linux Red Hat Advanced Server 2.1
After successfull installation when i try to connect to the database
it gives me error.

[oracle@localhost oracle]$ sqlplus /nolog

SQL*Plus: Release 9.0.1.0.0 - Production on Mon Feb 7 16:44:37 2000

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

SQL> connect as sysdba
Enter user-name: system
Enter password:
ERROR:
ORA-12705: invalid or unknown NLS parameter value specified

SQL> connect system/manager as SYSDBA
ERROR:
ORA-12705: invalid or unknown NLS parameter value specified

SQL>  connect system/manager@mtnl.localdomain as sysdba
ERROR:
ORA-12705: invalid or unknown NLS parameter value specified

i have tried mv sqlnet.ora sqlnet.ora.old, but nothing happened.
The $ORACLE_HOME and $ORACLE_SID seems to be correct.
can you please help me with this.
help is urgently required.
0
Comment
Question by:warrenprasad
8 Comments
 
LVL 2

Expert Comment

by:GDE
ID: 10857119
select * from sys.V_$NLS_VALID_VALUES;

for example for russian support
set NLS_LANG=RUSSIAN_CIS.RU8PC866
plus80.exe
set NLS_LANG=RUSSIAN_CIS.CL8MSWIN1251
it`s
<Language>_<TERRITORY>.<Encoding>

for english

PARAMETER            VALUE                                                      
-------------------- --------------------                                      
LANGUAGE             AMERICAN
TERRITORY            AMERICA
CHARACTERSET         WE8ISO8859P1  - ISO english
CHARACTERSET         US7ASCII      - 7bit english

Best regards
Dmitry

0
 

Author Comment

by:warrenprasad
ID: 10857192
Thanks Dmitry,

I tried

SQL> select * from sys.V_$NLS_VALID_VALUES;

but it said

SP2-0640: Not connected

The problem which i think is that i am not able to connect
to the database. As i had earlier said that while trying to
connect it gives error.

I dont know that whether I am messing up with the env
variables ??????

Please help.



0
 
LVL 4

Expert Comment

by:derekpapesch
ID: 10857193
You may have difficulties querying the v_$NLS_VALID_VALUES view without a database connection...

The NLS_LANG variable defines the client NLS settings.
It is either defined as an environment variable (UNIX), in the Registry and/or environment (PC with Windows NT / NT 2000), in the ORACLE.INI for 16 bit applications (NT/95 or Windows 3.11),CONFIG.ORA file in the $ORACLE_HOME\NLM directory ( NetWare) or as a logical on OpenVMS.


Here is the full text on what Oracle advises on ORA-12705s:


 
PURPOSE
This article is intended to give you an overview of common reasons for
ORA-12705 ?invalid or unknown NLS parameter value specified?
and how to resolve them.
SCOPE & APPLICATION
This article is intended to be used by Oracle DBAs and Application Developers receiving an ORA-12705.
ORA-12705 - Common Reasons and how to resolve them
Overview:
ORA-12705 "invalid or unknown NLS parameter value specified"

usually means, we cannot interpret the NLS information our Client (eg: SQL*Plus) is receiving. This is mostly a configuration issue either in your environment or because we cannot interpret the NLS information we receive from the Server (eg: because of version missmatches between client and server).

For an explanation of the error, see <Note:21558.1> OERR: ORA 12705 "invalid or unknown NLS parameter value specified"

As most issues are as a result of the incorrect setting of NLS_LANG and/or ORA_NLSx parameters, they will be discussed in detail in the first two sections, other sections will refer to these two sections and will contain additional information whenever necessary.

Contents:

1. ORA-12705 with incorrectly specified NLS_LANG
2. ORA-12705 with incorrectly specified ORA_NLSx
3. ORA-12705 due to Installation Issues
4. ORA-12705 when using Special Charactersets
5. ORA-12705 when connecting via SQL*Net
6. ORA-12705 during Migration
7. ORA-12705 when connecting with SVRMGRL and SQLPLUS
8. ORA-12705 when connecting with Precompiler Aplications
9. ORA-12705 during Export/Import
1 ORA-12705 with incorrectly specified NLS_LANG
1.1 The NLS_LANG variable defines the client NLS settings.
It is either defined as an environment variable (UNIX), in the Registry and/or environment (PC with Windows NT / NT 2000), in the ORACLE.INI for 16 bit applications (NT/95 or Windows 3.11),CONFIG.ORA file in the $ORACLE_HOME\NLM directory ( NetWare) or as a logical on OpenVMS.

1.2 Format of NLS_LANG:

LANGUAGE_TERRITORY.CHARACTERSET
(For an explanation of the different parts: LANGUAGE, TERRITORY, CHARACTERSET, check the <Note:60134.1> NLS Frequently Asked Questions and <Note:158577.1> NLS_LANG Explained )
 
1.3 What to consider when setting NLS_LANG:
a) The variable needs to be in Uppercase on Unix Systems.

b) If there is any mistake in the value setting of this variable (eg: a spelling mistake), you may encounter the ORA-12705. For a list of valid languages, territories and character sets the relevant National Language Support Guide for your version should be consulted.

c) Completely unsetting the NLS_LANG will use the default value, that is AMERICAN_AMERICA.US7ASCII. This should always fix the ORA-12705, however may give unexpected results, because we are only dealing correctly with characters that can be represented completely with 7 bits. As soon as special characters (usually stored using the 8th bit) are used, this will result in conversion problems.

For a discussion about 7bit/8bit conversions check

<Note:13855.1> NLS 7-BIT/8-BIT CONVERSION
 
d) On NT: if there are several Oracle_HOMEs on the machine you need to make sure, that you verify in the settings in the correct ORACLE_HOME. On how to determine the correct Oracle_Home, see <Note:77412.1> "How To Identify Which Registry Hive Is Used By A Specific Oracle Executable". Once the correct ORACLE_HOME has been determined, open the registry and verify the NLS_LANG entry in the special path. See also 1.4a)
e) On NT: if you set NLS_LANG as an environment variable this will take precedence over the value set in the Registry. In most cases this is not preferable, so you may unset this variable. For further information on how to do this see 1.4a)

f) On Unix: depending on the shell, you have to export the environment variable to make it visible to the subprocesses. Usually, it makes sense to set NLS_LANG in your specific Login-Script. See also 1.4b) on how to set the environment variable in different shells.

1.4 To check/set the values on different OS Systems:

a) windows NT /2000/2003
When there are several Oracle Installations (eg: Oracle Developer and Oracle Server Enterprise Edition) on your machine, then check which HOMEn value is being used. The entry in the Oracle.Key-File, which can be found in the $ORACLE_HOME\bin directory will point you to the correct entry in the Registry. See also 1.3d)

Once you have determined the value for the HOMEn, open the registry with the registry editor (eg: regedit), then go to

HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE -> HOMEn
HOMEn, is the value you took out of the Oracle.Key-File (eg: HOME2)
The next time you startup your application, the new value should be taken. If you have an open DOS-Box, the box has to be closed and reopened in order to get the new registry value.
To check if you have set an environment variable click on the START button, the choose SETTINGS -> CONTROL PANEL -> SYSTEM ->

Depending on your version goto:

Win2000: Advanced -> Environment Variables
WinNT: Environment
For the correct setting of the parameter please see: <Note:179133.1> The correct NLS_LANG in a Windows Environment
b) Unix
On how to verify/set environment variables on Unix, see <Note:131207.1> How to Set Unix Environment Variables
This is also valid for NLS_LANG.

c) VMS
<Note:66825.1> VMS: How to Research Oracle-Related Logicals on OpenVMS
Gives you an overview on how to set/verify Logicals on OpenVMS. The same is true for NLS_LANG.

1.5 Related issues

You may also see the ORA-12705, when some other NLS-related environment variables are not set correctly.
See: <Note:107359.1> ORA-12705 when changing NLS_NUMERIC_CHARACTERS value

 
2 ORA-12705 with incorrectly specified ORA_NLSxx
 
2.1 The ORA_NLSxx variables point to the actual location of the NLS Data files.
 
2.2 Format of ORA_NLSxx
Prior to starting the database, to allow for non-standard configurations the actual location of the NLS Data files is specified by the ORA_NLSxx environment variable, where xx depends on theNLSRTL version number. If the variable is not defined, the default value of $ORACLE_HOME/ocommon/nls/admin/data, or a Windows equivalent is used. If the variable is defined, the default location is not searched anymore. Thus the variable must not have a bogus value.

Depending on the Database Version a different ORA_NLSxx variable is needed:

  Release Environment Variable
7.2 ORA_NLS
7.3 ORA_NLS32  
8.0, 8.1, 9.0.1 ORA_NLS33  

2.3 What to consider when setting ORA_NLSx

a) The variable needs to be defined in Uppercase on Unix platforms

b) Not setting the ORA_NLSxx will make Oracle search for the NLS Data files in the default directory

c) If the directory cannot be read due to invalid file permissions, you will receive an ORA-12705

d) If there is a spelling mistake in the variable name, this will also result in an ORA-12705

e) Due to a bug in Oracle 7.2, ORA_NLSxx had to be set because it had no default value. See <Note:30950.1>ALERT: ORA_NLS should be set in Oracle 7.2
@<Bug:293590>

For a complete discussion about setting the ORA_NLSxx variables see <Note:77442.1> ORA_NLS (ORA_NLS32, ORA_NLS33) Environment Variables.

 
2.4 To check/set the ORA_NLSxx on different OS Systems:
 
Please see section  1.4 on how to set environment variables on different plattforms.
2.5 Related Issues

On Windows NT previous to Oracle 8.1 another variable had to be set: NLSRTLxx, which specifies the location of the NLS message files. If not set, you may also encounter the ORA-12705. Check <Note:1027964.6> WINNT: LCC-00161 AND ORA-01031 ON STARTUP deals with a similar setup problem

 
3 ORA-12705 due to Installation Issues
 
a) If you do not have the right permissions set on the NLS data files, you will receive an ORA-12705. The NLS loadable files are usually located in
- $ORACLE_HOME/ocommon/nls/admin/data on Unix
- $ORACLE_HOME\ocommon\nls\admin\data on Windows NT
- $ORA_ROOT_DEV:[ORACLE.ROOT817.OCOMMON.NLS] on OpenVMS
On Unix, the protections should be as follows:
drwxr-xr-x   2 oracle dba  9728 Aug 3 15:17 data/
-rw-r--r--    1 oracle dba    714 Mar 312000  *.nlb
To change the protection issue:
Directory:  chmod 755  <YourNLSDir>
Files:  chmod  644  *.nlb
On Windows NT, if you are using NTFS formated disks, make sure that the NT account that is connecting to the database has at least READ protection on the directory and the files.
You can view and modify the protections in the Windows Explorer by doing a right mouse click on the directory, clicking on the Permissions Button in the security Tab, and choosing READ access for the appropriate user.

On OpenVMS, the permissions on the directory and the files should be:

s:RWE, o:RWE, g:RE, w:RE
To set them, issue:
$ SET FILE/PROTECTION=( s:RWE, o:RWE, g:RE, w:RE)/LOG -
ORA_NLS33:*.*;*
b) Are all files installed
As the NLB loadable datafiles usually contain of several hundreds of files, it will be hard to verify for a complete installation of all files. You may check which files exist with another installation of the same version/platform.
c) Installing two incompatible versions into the same Oracle_Home
See <Note:1083152.6> "ORA-12705: Message not found" for this issue an how to resolve it.

 
4 ORA-12705 when using Special Charactersets
If you already have verified that your  NLS_LANG and ORA_NLSx values are setup correctly and your installation is correct, there might be an issue with compatibility issues between different versions.

A list of supported charactersets for your version is mentioned in the National Language Support Guide for your version.

If an Oracle8 or Oracle8i database is created with a character set which is NOT known to Oracle8.0 / Oracle7 clients connecting to it then various errors or strange results may be seen in the client application.

For a complete description, see:
<Note:70150.1> ALERT: Certain Character Sets Cause Client Code to Fail (eg: WE8ISO8859P15)

For a similar issue with Developer 6i and support of the Euro see:
<Note:109168.1> Support for Euro Symbol

 
5 ORA-12705 when connecting via SQL*Net
 
a) An ORA-12705 may occur, when you have specified an invalid entry for the ORACLE_HOME in the listener.ora. Go to your TNS_ADMIN directory (either specified by an environment variable or the default $ORACLE_HOME/network/admin) and check the listener.ora for an incorrect setting.
See also <Note:1011329.102> ORA-12705 INVALID OR UNKNOWN NLS PARAMETER VALUE SPECIFIED.

 
b)If additional environment variables are specified in the listener.ora and they are incorrectly specified, an ORA-12705 can be seen during connections over SQL*Net. Additional environment variables are defined by using the ENVS parameter.
The below note provides an example on how to set the ENVS parameter in the listener.ora:
<Note:39357.1> (A) Undocumented or Lesser Known SQL*Net/Net8 Features & Parameters

 
6 ORA-12705 during Migration
When doing a migration from a previous version of Oracle to a newer version, there are some special things to consider on setting ORA_NLSxx.

To check whether you have the correct setup of those variables, see the following Notes:
<Note:96666.1> ORA-1012 ORA-12705 DURING MIGRATE FROM ORACLE7 TO ORACLE8
<Note:76460.1> Checklist for Migrating from Oracle7 to Oracle8.1 on UNIX

 
7 ORA-12705 when connecting with SVRMGRL and SQLPLUS
Checking the causes described in sections 1 to 5 should usually resolve those issues.

See1ORA-12705 with incorrectly specified NLS_LANG
See2ORA-12705 with incorrectly specified ORA_NLSx
See3ORA-12705 due to Installation Issues
See4ORA-12705 when using Special Charactersets
See5ORA-12705 when connecting via SQL*Net
Similar issues are also described in the following notes:
<Note:152712.1> Database Startup with Server Manager Fails With ORA-1012
<Note:152711.1> Connect Internal Using SQLPLUS Fails With ORA-12705
 
8 ORA-12705 when connecting with Precompiler Applications
 
a) Please first check sections 1 to 5
See1ORA-12705 with incorrectly specified NLS_LANG
See2ORA-12705 with incorrectly specified ORA_NLSx
See3ORA-12705 due to Installation Issues
See4ORA-12705 when using Special Charactersets
See5ORA-12705 when connecting via SQL*Net
If this does not resolve your problems, you may check the below.
b) If you upgraded your environment to a newer version, you usually have to precompile your programs again or run the programs from the old Oracle_HOME. See <Note:99031.1> ORA-12705 Running Procobol Program after Migration to Oracle 8.1.5

9 ORA-12705 during Export/Import

a) As with other tools, first make sure, the points discussed in sections 1 to 5 are not the problem:

See1 ORA-12705 with incorrectly specified NLS_LANG
See2ORA-12705 with incorrectly specified ORA_NLSx
See3ORA-12705 due to Installation Issues
See4ORA-12705 when using Special Charactersets
See5ORA-12705 when connecting via SQL*Net
b) There might be an issue if the export catalog views are not up to date, because catexp.sql has not been run. Check <Note:1017491.102> EXP-41: DIRECT PATH MODE FULL DATABASE EXPORT
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 10857261
You need to check:
1. If it is defined as an env variable and is correct. Not that the variable needs to be in Uppercase. For example:

NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

2. If  you specified ORA_NLS33 incorrectly

3. If you do not have the right permissions set on the NLS data files, you will receive an ORA-12705. The NLS loadable files are usually located in $ORACLE_HOME/ocommon/nls/admin/data

On Unix, the protections should be as follows:
drwxr-xr-x   2 oracle dba  9728 Aug 3 15:17 data/
-rw-r--r--    1 oracle dba    714 Mar 312000  *.nlb

To change the protection issue:
Directory:  chmod 755  <YourNLSDir>
Files:  chmod  644  *.nlb

4. ORA-12705 when using Special Charactersets

If you already have verified that your  NLS_LANG and ORA_NLS33 values are setup correctly and your installation is correct, there might be an issue with compatibility issues between different versions.

A list of supported charactersets for your version is mentioned in the National Language Support Guide for your version

5. ORA-12705 when connecting via SQL*Net :
 
a) An ORA-12705 may occur, when you have specified an invalid entry for the ORACLE_HOME in the listener.ora. Go to your TNS_ADMIN directory (either specified by an environment variable or the default $ORACLE_HOME/network/admin) and check the listener.ora for an incorrect setting
b)If additional environment variables are specified in the listener.ora and they are incorrectly specified, an ORA-12705 can be seen during connections over SQL*Net. Additional environment variables are defined by using the ENVS parameter.

Hope this helps
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 47

Expert Comment

by:schwertner
ID: 10857819
Check if you have good value for ORA_NLS33. It should be:
ORA_NLS33                $ORACLE_HOME/ocommon/nls/admin/data

If ORA_NLS33 is OK then set NLS_LANG correct.
NLS_LANG=.UTF8
or
NLS_LANG=NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
or what is appropriate for your applications.
0
 

Author Comment

by:warrenprasad
ID: 10858103
i checked the NLS_LANG setting
its NLS_LANG=AMERICAN-AMERICA.UTF8

The ORA_NLS33 is also specified correctly.
nothing seems to be wrong here either.

$ORACLE_HOME/ocommon/nls/admin/data
permissions are also fine.

Listener.ora does not contain any wrong info about
$ORACLE_HOME.

One more thing, the Global database name that i gave during
installation was mtnl.localhost and sid was mtnldb.
I uninstalled the Oracle and reinstalled it with Global Database name
as oradb.localhost and SID as oradb, echo $ORACLE_SID gives ORADB
(in caps) .. can this be a problem, Or is there anything seems to be wrong which I have done ?????

Thanks for helping me!!!
0
 
LVL 1

Accepted Solution

by:
ora-dba earned 50 total points
ID: 10858302
Hello,
NLS_LANG=AMERICAN-AMERICA.UTF8 is the problem. It should be NLS_LANG=AMERICAN_AMERICA.UTF8 (replace - with _ )
0
 

Author Comment

by:warrenprasad
ID: 10858441
Bingo!!! IT WORKED!!!!!

Thaks a lot.

The '-' hyphen was the culprit.
After i changed it it started working.

Thanks a lot for helping me.

regards,
Warren
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

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.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

707 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

16 Experts available now in Live!

Get 1:1 Help Now