After installing the Ruby/OCI8 module on a Windows XP installation where sqlplus is working fine I got the following error when testing the connection in irb:
irb(main):012:0> cn = OCI8.new("username","password","database")
OCIError: ORA-12705: invalid or unknown NLS parameter value specified
from c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:158:in `begin'
from c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:158:in `initialize'
from c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:158:in `do_ocicall'
from c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:158:in `initialize'
from (irb):12:in `new'
from (irb):12
from :0
This one really took me several hours of google-hunting, finding 0 direct solutions. I was almost throwing my hat in the ring when I finally found the solution.
The Problem
In this case the ‘NLS parameter’ I was having a problem with turned out to be NLS_LANG.
This parameter is supposedly set during installation (I can’t remember choosing this, but this must be the only Oracle client installation I’ve ever done on MS Windows). The ORA-12705 error was cause by my client Ruby/OCI8 sending an incompatible NLS_LANG language parameter when connecting with this specific Oracle db.
Sqlplus, enterprise manager,… were all working fine.
Where is NLS_LANG stored on the client ?
The value was in my case stored in 3 different locations in the windows registry, always in a key ‘NLS_LANG’, once at HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE, and 2 more times in sub-keys of this location. Just do a search in regedit for ‘NLS_LANG’ and you’ll find the culprits. 1 of these NLS_LANG had the value ‘NA’, the other 2 were ‘DUTCH_BELGIUM.WE8MSWIN1252’.
If you want to test if this is the source of your issue, then apparently you can override this value using an ‘NS_LANG’ environment variable. To test this, just do ‘set NLS_LANG = ‘ in your dosbox before starting irb.
How do I know what language the Oracle db expects ?
Open a connection to the db with sqlplus (or whatever it is you’re using) and issue the following:
SQL> select USERENV('LANGUAGE') FROM DUAL;
Result:
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.WE8ISO8859P1
To view all NLS parameters, issue:
select * from NLS_DATABASE_PARAMETERS;
When doing ‘set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1’ before starting irb, connecting to the db went fine.
Will this work with rails ?
I tried ‘set’ting NLS_LANG before starting webrick, but to no avail. I really had to change registry values for rails to work when running onder webrick.
I guess (hope) it will be the same when running under a more production-level server too.
What if I need to connect to several different db’s which expect different language settings ?
God knows.
I’d start by trying to set all NLS_LANG registry entries to ‘NA’. If that doesn’t work, I’d try to find out if it’s possible to set this as a parameter when connecting. But how you would do this in Rails: ???
Please let me know if you find out!