charset problem with Oracle

dazzyb has asked for the wisdom of the Perl Monks concerning the following question:

Currently have a database on solaris using the us7ascii charset.
I'm in the process of upgrading but charset is now we8iso8859p1.

The problem is I have a password de-cryption routine that causes wide character problems. I have set the NLS_LANG, ORA_NLS32 environment variables but to no avail.

Anyone point me in the right direction?

The oracle metadata has been exported/imported into a new database using the us7ascii charset.

I'm using Perl 5.8.0 upgraded from 5.00503. The main thrust of the decryption is chr() which gives me the expected results on the old version of Perl.

Wide character is not a valid character within the ASCII code.

Seems that on the old version of Perl if you called chr(336) this would return P in 5.00503 but 5.8.0 returns some control character.

The fix was staring me in the face. Check the return value and subtract 256 (0->255 vaild ascii) if greater than 255. Seems to be OK now that I have a database using the US7ASCII charset and the correct environment settings.

Still have the problem with the we8iso8859p1 charset but I think this could be data conversion during import.

Edit by jdporter: Changed title from 'DBI::Oracle'

Comment on charset problem with Oracle

Replies are listed 'Best First'.
Re: charset problem with Oracle by etcshadow (Priest) on Feb 11, 2004 at 04:51 UTC
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` [download] Edit by jdporter: Changed title from 'Re: DBI::Oracle'	[reply] [d/l] [select]
Re: charset problem with Oracle by graff (Chancellor) on Feb 11, 2004 at 01:23 UTC
So, is your password decryption routine written in Perl? And what exactly are the "wide character problems" that you are talking about? That term covers a lot of ground... How about you update your post with some more details, maybe some relevant code snippet, and pertinent examples of input, expected output, and actual output. Maybe tell us what version of Perl? What OS? (These things matter when we talk about "wide character problems".) Until you do that, no one will have much idea what direction you should be pointed in. Edit by jdporter: Changed title from 'Re: DBI::Oracle'	[reply]