Setting the NLS_LANG environment variable on the client to match that of the Oracle Database (found in v$nls_parameters system view) is NOT NECESSARILY the correct configuration. NLS_LANG is used to tell the Oracle Client what character set the client's (or Informatica Server's) OS is using, so that appropriate code conversion can take place (or be skipped, when code pages match between Oracle client and server). Please see Oracle Technical Note 158577.1 - "NLS_LANG Explained" for a more elaborate explanation as well as troubleshooting tips. Please contact Oracle Support for configuration suggestions, as needed.
The NLS_LANG variable must to be in uppercase on UNIX.
If there is any mistake in the value setting of this variable (such as a spelling mistake), you may encounter the ORA-12705 error.
For a list of valid languages, territories and character sets, the relevant National Language Support Guide for your version should be consulted.Completely unsetting the NLS_LANG will use the default value AMERICAN_AMERICA.US7ASCII.
This may fix the ORA-12705 error. However it may give unexpected results, as you are only dealing correctly with characters that can be represented completely with 7-bits. When you use a special characters (usually stored using the 8th bit), this will result in conversion problems.On Windows, if there are several Oracle_HOMEs on the machine, ensure you have set the correct ORACLE_HOME.
Once you have determined the correct ORACLE_HOME , open the registry and verify the NLS_LANG entry in the special path.On Windows, 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.On UNIX, depending on the shell, you will have to export the environment variable to make it visible to the sub-processes.
Oracle SQL Developer is the only "known-good" client side tool which can be used to view characters correctly and works independently of client-side NLS_LANG settings.
0Awesome Comments!