Re: NLS

On 28 May 2003 08:18:15 -0700, v.hugo_at_gmx.de (Hans v. Hugo) wrote:

>I have a "little" problem with charsets: actually my server runs with
>american NLS-settings. That means in practise, that NLS properties in
>the enterprise manager are set to american (nls_language='AMERICAN',
>nls_teritory="america") and the view nls_database_parameters shows
>american settings too. (nls_language='AMERICAN',
>nls_teritory="america", NLS_CHARACTERSET="WE8ISO8859P1"). If I write
>the euro currency sign (€) to the database it saves it as an upside
>down question mark.
>this efect is caused by the orracle nls-settings. ( ... or is there
>something else?) So far, so good, but how can I change that settings
>in a correct way.
>I tried to chage the values in the enterprise manager, but that did
>not work: the server showed me an ora-02096 and telling me, that the
>use of dynamic parameters wasn't successful.
>I changed the entries in the database via with :
>
>UPDATE PROPS$ SET VALUE='GERMANY' WHERE NAME='NLS_TERRITORY';
>UPDATE PROPS$ SET VALUE='GERMAN' WHERE NAME='NLS_LANGUAGE';
>UPDATE PROPS$ SET VALUE='GERMAN' WHERE NAME='NLS_ISO_CURRENCY';
>UPDATE PROPS$ SET VALUE='GERMAN' WHERE NAME='NLS_DATE_LANGUAGE';
>UPDATE PROPS$ SET VALUE='WE8ISO8859P15' WHERE
>NAME='NLS_CHARACATERSET';
>
>and it works, but that seems to me a very brutal way and the
>properties in the enterprise manager stayed "american" as they where
>before.
>
>There must be another way to change that setting - but how ?
>
>Any hint would be nice.
>
>tia,
>
>Hans
>
>Btw.: Im running oracle 9.2 win with 2000/xp.

First of all : why on earth are you updating props$, rendering your database unsupported, instead of changing the characterset using the alter database command?

Secondly: The euro symbol is NOT included in the WE8ISO8859P1 characterset, because the ISO couldn't agree on the location of the symbol.
So they invented the WE8ISO8859P15 character set to accomodate for the euro.
Winblows nowadays use the 1252 code page, and as you can imagine this code page (and the corresponding MSWIN1252 Oracle characterset) has the euro on a different location.
As you must change from WE8ISO8859P1 to WE8ISO8859P15 or MSWIN1252, you can't use ALTER DATABASE because the target charset is not a superset of the source characterset.
You should NOT UPDATE PROPS$, as Oracle will DROP THE PHONE ON YOU as soon as you get into trouble because of this. So AFAIK the only viable alternative is export import.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed May 28 2003 - 12:30:04 CDT