Subject: ORA-12705 - Common Reasons and How to Resolve Them Doc ID: Note:158654.1 Type: BULLETIN Last Revision Date: 05-DEC-2005 Status: PUBLISHED 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 we strongly advice to set the NLS_LANG, 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. 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) and check / change the NLS_LANG as documented in Note 179133.1 The correct NLS_LANG in a Windows Environment 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, then 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 264157.1How to set correctly NLS_LANG on Unix Environments and Note 131207.1 How to Set Unix Environment Variables 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 this is coverd in Note 77442.1 ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) Environment Variables explained. 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.1ALERT: 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, ORA_NLS10) Environment Variables explained. 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 > 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 Files: chmod 644 *.nlb On Windows NT, if you are using NTFS formated disks, make sure that: * the NT account that is starting the OracleService (typically the NT "system" user") has at least READ permissions on the directory and the files. * the NT account that is connecting to the database has at least READ permissions 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. 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. See 1 ORA-12705 with incorrectly specified NLS_LANG See 2 ORA-12705 with incorrectly specified ORA_NLSx See 3 ORA-12705 due to Installation Issues See 4 ORA-12705 when using Special Charactersets See 5 ORA-12705 when connecting via SQL*Net Similar issues are also described in the following notes: Note 152712.1Database 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 See 1 ORA-12705 with incorrectly specified NLS_LANG See 2 ORA-12705 with incorrectly specified ORA_NLSx See 3 ORA-12705 due to Installation Issues See 4 ORA-12705 when using Special Charactersets See 5 ORA-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: See 1 ORA-12705 with incorrectly specified NLS_LANG See 2 ORA-12705 with incorrectly specified ORA_NLSx See 3 ORA-12705 due to Installation Issues See 4 ORA-12705 when using Special Charactersets See 5 ORA-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 RELATED DOCUMENTS Note 21558.1OERR: ORA 12705 "invalid or unknown NLS parameter value specified" Note 60134.1NLS Frequently Asked Questions Note 13855.1NLS 7-BIT/8-BIT CONVERSION Note 77412.1How To Identify Which Registry Hive Is Used By A Specific Oracle Executable? Note 158577.1NLS_LANG Explained (How does Client-Server Character Conversion Work?) Note 179133.1The correct NLS_LANG in a Windows Environment Note 131207.1How to Set Unix Environment Variables Note 66825.1VMS: How to Research Oracle-Related Logicals on OpenVMS Note 107359.1ORA-12705 when changing nls_numeric_characters value Note 30950.1ALERT: ORA_NLS should be set in Oracle 7.2 Note 77442.1ORA_NLS (ORA_NLS32, ORA_NLS33) Environment Variables Note 1027964.6WINNT: LCC-00161 AND ORA-01031 ON STARTUP Note 1083152.6ORA-12705: Message not found Note 70150.1ALERT: Certain Character Sets Cause Client Code to Fail (eg:WE8ISO8859P15) Note 109168.1Support for Euro Symbol Note 39357.1(A) Undocumented or Lesser Known SQL*Net/Net8 Features & Parameters Note 96666.1 ORA-1012 ORA-12705 DURING MIGRATE FROM ORACLE7 TO ORACLE8 Note 76460.1Checklist for Migrating from Oracle7 to Oracle8.1 on UNIX Note 152712.1Database Startup with Server Manager Fails With ORA-1012 Note 152711.1Connect Internal Using SQLPLUS Fails With ORA-12705 Note 99031.1 ORA-12705 Running Procobol Program after Migration to Oracle 8.1.5 Note 1017491.102 EXP-41: DIRECT PATH MODE FULL DATABASE EXPORT Oracle8i National Language Support Guide Release 2 (8.1.6) Part No. A76966-01 Oracle9i Globalization Support Guide Release 1 (9.0.1)Part No. A90236-01 For further NLS / Globalization information you may start here: Note 150091.1 Globalization Technology (NLS) Library