Resolving the ORA-12705 error

原文请见：http://www.dba-oracle.com/t_ora_12705_error.htm Resolving the ORA-12705 error Oracle Tips by Burleson Consulting

Question:  My Oracle database allows me to connect with TOAD, Oracle Enterprise Manager and SQL Developer, but when I try to connect to Oracle from my Windows PC with the MS SQL Server Reporting Services I can connect OK with the Report Designer, but when I try to connect to Oracle from the SQL Server Report Manager I get the ORA-12705 error message:  ORA-12705: invalid or unknown NLS parameter value specified

Answer: 

First, see my notes on Resolving the ORA-12705: error

It's likely a missing NLS-LANG setting in your Windows PC client registry.   The NLS_LANG variable is in the Home0 directory in the Windows registry is not the only NLS_LANG, and you need to locate the Oracle directory in the registry and find NLS_LANG. This variable may be set to "NA", and a change to AMERICAN_AMERICA.WE8ISO8859P15 may fix your connectivity issue.

The Oracle docs note these details for the ORA-12705 error:

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.

It's an important clue that your Oracle connections works on some products and not others.  This suggests that the ORA-12705 may be precipitated by an environmental setting or parameter.  You can use this script to verify the NLS settings:

select
   d.parameter Dparameter,
   i.parameter Iparameter,
   s.parameter Sparameter
from
   nls_database_parameters d,
   nls_instance_parameters i,
   nls_session_parameters s
where
   d.parameter = i.parameter (+)
and
   d.parameter = s.parameter (+)
order by 1;

Possible solutions for the ORA-12705 error include verifying environmental parms and registry entries. 

NLS_LANG=american_america.we8iso8859p1
export NLS_LANG

NLSPATH=NLSPATH=$NLSPATH:/usr/xxx
export NLSPATH

LANG=en_US
export LANG

In Windows, check the registry for the key NLS_LANG, NLSPATH, and LANG.  This user notes a  novel solution to the ORA-12705 error on Windows:

I found a post somewhere on the Internet about a guy DELETING the NLS registry key. This key can be found by searching the registry on the server for home0.

I tried it once without restarting the server and it didn't work. Then I restored the key and continued to look for more information.

After not find anything, I decided to delete the key again and bounce the server. PRESTO!

And we also see this fix for the ORA-12705:

I contacted Microsoft support, after some tests, it appears that it was a permission problem in the registry.

The support has decided to create a bug to the developers team :
#471411 - "Cannot connect to Oracle linked server if first connection has failed due to insufficient permissions".

The .NET docs also note this for the ORA-12705 error:

Oracle Client software requires that you give the Authenticated User privilege to the Oracle Home by following these steps:

1. Log on to Windows as a user with Administrator privileges.

2. Launch Windows Explorer from the Start Menu and navigate to the ORACLE_HOME folder. This is typically the "Ora92" folder under the "Oracle" folder (i.e. D:/Oracle/Ora92).

3. Right-click on the ORACLE_HOME folder and choose the "Properties" option from the drop down list. A "Properties" window should appear.

4. Click on the "Security" tab of the "Properties" window.

5. Click on "Authenticated Users" item in the "Name" list (on Windows XP the "Name" list is called "Group or user names").

6. Uncheck the "Read and Execute" box in the "Permissions" list under the "Allow" column (on Windows XP the "Permissions" list is called "Permissions for Authenticated Users").

7. Re-check the "Read and Execute" box under the "Allow" column (this is the box you just unchecked).

8. Click the "Advanced" button and in the "Permission Entries" list make sure you see the "Authenticated Users" listed there with:

Permission = Read & Execute
Apply To = This folder, subfolders and files

If this is NOT the case, edit that line and make sure the "Apply onto" drop-down box is set to "This folder, subfolders and files". This should already be set properly but it is important that you verify this.

9. Click the "Ok" button until you close out all of the security properties windows. The cursor may present the hour glass for a few seconds as it applies the permissions you just changed to all subfolders and files.

10. Reboot your computer to assure that these changes have taken effect.

Re-execute the application and it should now work.