Oracle Connection and nls_lang

Question

Hello

I have a legacy database set with NLS_LANG set to IW8ISO8859P8. This I cannot change.

I have another application, that is not working with unicode, that works on the same data my application works on. In some of the fields, and some of the times, the user inserts as part of a string, the character 161 which represents NIS currency simble. When I try to read this data from .Net using oledb, all is well but when I try to do so with the oracle client, I get garbage data instead. also when I insert in .Net the NIS simble, it is saved as garbage.

I know I can manipulate the data after I got it, but I am looking for some configuration of the oracle client in .Net to get this to work.

I have been working on this for a few weeks now, any help would be appriciated. Tnx Noam

Answer 1

Have you looked at Oracle's NLS_LANG FAQ?

Answer 2

did you check the nls_lang setting on the client? Any changes to the nls setting can be overwritten by settings on the client (registry and environment). maybe this is the cause of your problem. Verify that you don't have any other nls settings on the client and that non of you functions explicitly change it. you can run an after logon trigger to change the session format. this will make sure that the server settings will affect all clients. In addition, make sure that nls_territory is set. it should be set as part of the nls_lang but just in case you meant nls_language. I believe the currency symbol is affected by this.

Answer 3

At the end of the day I solved it using a secondary nvarchar column. and triggers to translate between them.

I have written functions in plsql that do a byte conversion to and from Unicode, using a custom encoding realized by many many if statements.

And for each column with the problematic data I added a second column with nvarchar storage, and triggers that uses that function to translate the problematic data.

So the legacy applications uses and updates the varchar columns, and the triggers translate it to the Unicode columns. and the .Net code uses the Unicode columns, and again the triggers translate the info to the problematic encoding used in the legacy application.

If you need more info, please feel free to contact me for more information