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:

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.

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:


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!


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.


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.