I've seen this problem before. First of all: check to make sure that those environment variables are set consistently on all sql
clients (not just the DB server, remember that, for example, a web server connecting to this DB is acting as a sql client... so check it's environment), not just on the server. More to the point... it may not be in an environment variable, it may be just under your sql client configuration (I can't remember the file name now... but you could always try
grep -ri nls $ORACLE_HOME =D). Also, I believe that there is an init.ora parameter to check on the DB server.
Just for curiosity/trivia's sake: the specific examples I've seen related to these issues are:
- NLS_DATE_LANG set to UK english instead of US english (caused day-of-week 0 to be monday instead of sunday, which in turn messed up some scheduling code... but only on some of several load-balanced web servers... and also, not reproducible in development... great kind of bug). It was ultimately an issue with the default sql client settings ($ORACLE_HOME/something-or-other), not even the environment (basically, what the client uses if the environment vars aren't set).
- NLS_CHARSET was flipped from us7ascii to we8iso8859p1 on a particular DB in a group of DBs that ran Advanced Replication (variously "snapshots" or "materialized views" depending on which document you read). Anyway, this resulted in snapshot refresh errors, as the host storing the master and the host storing the snapshot were moving the data back and forth in binary, without regard to the character-set. In general, however, changing the character set shouldn't matter much, as the character set is going to perform consistent mapping into- and out-of- the database. (I.e. you should only see a problem like this if you are manipulating the data in binary, as apparently snapshots do, rather than through the character encoding/decoding of SQL statements.)
Good luck.
------------
:Wq
Not an editor command: Wq
Edit by jdporter: Changed title from 'Re: DBI::Oracle'