1. Yiannis Papadomichelakis
  2. Beta Testing
  3. Thursday, 5 March 2020 10:20 AM UTC

Hi all....

I was browsing the bugs of PB19 R2 and I noticed an Oracle related bug, that never occurred in my tests. I am talking about the Bug 4214.

We use PB + Oracle since 1996 and what we always do is to change the NLS_LANG parameters in Registry. This parameters is stored on Oracle Home (in my case is HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ORACLE\KEY_OraClient12Home1_32bit) since I use the 32bit version of Oracle client.

So, in order for IDE to understand the decimal values, we use the following NLS_LANG AMERICAN_AMERICA.EL8MSWIN1253. We keep AMERICAN_AMERICA because we want American messages (It's very hard to understand the errors in the Greek translation) and also it helps PB to understand the numbers it's receiving. The EL8MSWIN1253 is used to define the Greek character set (Some of our databases do not use UTF charset).

So, I think that Marco did not setup the parameter correctly. If I was him, I would try to use an NLS_LANG parameter that would destroy the results completely. For example I could use a "Greek" or a "Russian" NLS_LANGUAGE in order get the error messages in that language.

If SELECT 1/0 FROM DUAL gives you an error in Italian, then the specified NLS_LANG is in wrong place...

 

However, the best approach for this problem is to change these parameters, immediately after login, by executing

ALTER SESSION SET NLS_LANGUAGE='AMERICAN' 

and

ALTER SESSION SET NLS_TERRITORY='AMERICA'

 

Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 5 March 2020 11:06 AM UTC
  2. Beta Testing
  3. # 1

I would definitely recommend UTF8, as with "American" you cannot store certain characters in a varchar2(..) variable like "Ñéí£' etc. You would have to use NVarchar in that case.

just my 2cts.

Comment
  1. Yiannis Papadomichelakis
  2. Thursday, 5 March 2020 11:18 AM UTC
The American_America defines the territory and language, not the character set. So using AMERICAN_AMERICA it's not related to what characters will you store.

We use AMERICAN_AMERICA.EL8MSWIN1253. The EL8MSWIN1253 is the Greek ANSI charset, so we can store all the characters we want to, but yes, I agree that UTF8 is better. So in unicode databases, we use UTF8...
  1. Helpful
  1. Miguel Leeuwe
  2. Thursday, 5 March 2020 11:23 AM UTC
Ah, sorry I thought that would also set the character set, thanks for correcting that!
  1. Helpful
There are no comments made yet.
David Peace Accepted Answer Pending Moderation
  1. Thursday, 5 March 2020 11:57 AM UTC
  2. Beta Testing
  3. # 2

Hi Yiannis

Yes all very good advice, we have used PB and Oracle since the nineties too.

We too set SET NLS_LANGUAGE and NLS_TERRITORY after the DB connection. We do this in a DB After Logon trigger (for none Oracle service connections). It avoids all the problems with inconsistent setups.

PB and Oracle work together very well it's all about getting the Oracle configuration correct.

Cheers

David

 

 

 

 

Comment
  1. Yiannis Papadomichelakis
  2. Thursday, 5 March 2020 12:06 PM UTC
After logon trigger is a nice approach, but it means that all clients work using the same settings. If 3rd party software runs on your database, then it may cause problems.

Unless you are also controlling that inside the trigger. Do you check program, module or similar parameters?
  1. Helpful
  1. David Peace
  2. Thursday, 5 March 2020 12:14 PM UTC
Yes the trigger is complex and manages different programs connecting. However as this is our package software it also checks for rogue connections and blows them away so it suits us nicely :)
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.