Wednesday, June 30, 2010

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

Yesterday I got to "help" a colleague out with the aforementioned ORA-12705. I had never seen it before and there doesn't seem to be a whole lot of information out there, especially in regards to OBIEE.

Here's the definition from the docs:

Cause:
Either an attempt was made to issue an ALTER SESSION command with an invalid NLS parameter or value; or the environment variable(s) NLS_LANG, ORA_NLSxx, or ORACLE_HOME was incorrectly specified, therefore the NLS data files cannot be located.

Action:
Check the syntax of the ALTER SESSION command and the NLS parameter, correct the syntax and retry the statement, or specify the correct directory path/values in the environment variables.


This would occur when trying to import tables into the RPD (OBIEE). First thing that pops up is the Select Data Source screen:

select data source

Then this beauty would pop up:

ORA-12705

Ultimately the solution that, sort of worked, was here.

Windows - The NLS_LANG must be unset in the Windows registry (re-named is best). Look for the NLS_LANG subkey in the registry at \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE, and rename it.

We tried changing it to what I had in my registry: AMERICAN_AMERICA.WE8MSWIN1252

That didn't work.

Tried renaming it as the article suggested, that didn't work.

Finally, my colleague (without prompting from me), cleared the key (didn't delete, just blanked it)...and voila!

5 comments:

kurt said...

You do know that by unsetting nls_lang you default to the US7ASCII codepage ? And thus, if you import data with an 8bit codepage, you can end up with data loss.

Instead of unsetting NLS_LANG, setting ORA_NLS10 to %ORACLE_HOME%/nls/data in the registry might have been a better choice...

oraclenerd said...

I did not know that.

Admittedly, this post was a terrible way to go about it...just unsetting/setting something without understanding the downstream consequences is not a very good practice.

Thanks for the tip Kurt.

chet

Anonymous said...

If you didn't know that, perhaps you didn't know that using the 7 bit doesn't prevent you from putting in 8 bit data with several tools. Then an exp/imp to the proper bittage loses bits...

oraclenerd said...

Fortunately we aren't creating or modifying data with this tool...merely gathering the metadata for specific tables.

Anonymous said...

Thank you! I read a similar suggestion on other forums, but I have multiple ORACLE_HOMES and in my registry I was attempting to edit the NLS_LANG key in the specific home I was working with, which didn't work. Had to go to the top-level key as you suggest.