NLS - resetting German to American

We have a database set up on a German network using Windows NT4. The NLS settings (presumably inherited from the OS default) are German.
I want to reset this to American. Is it simply a matter of setting NLS_LANGUAGE & NLS_TERRITORY to AMERICAN & AMERICA in the init.ora file? Or must I also set NLS_CHAR_SET as well?

You can alter the NLS_LANGUAGE and NLS_CHAR_SET at the session level, but I am not sure about the database level.

Character set is important (as you probably know) because for numbers, the comma is equivalent to the decimal point in English, and the decimal point is the thousands separator instead of the comma.

Good luck

Dave, thanks.

I've been told that setting NLS_TERRITORY as an init parameter will change the decimal and comma separators, local currency symbols etc. and NLS_LANGUAGE changes date formats.
The West European CHARSET of WE8ISO8859P1 should support German ... shouldn't it? I've tried inserting German text (with umlauts etc) into our DB here and they commit okay.

I need to change it all for all future sessions, so it has to be DB level.

Has anyone ever done this?

There are two character sets that will work and I don't know the differences. They are

WE8ISO8859P1 and
WE8ISO8859P15

My understanding is that the database character set is fixed at database creation. You can convert to a character set that is a superset of the character set, but not a subset if you are loading the data into a new database, but as far as I know, you cannot issue an ALTER DATABASE command to change the database character set.

You are correct on the NLS_TERRITORY remark.

You can also use

SET NLS_NUMERIC_CHARACTERS to switch the functions of comma and decimal point.

Good luck.

Yes,
Agree on the CHARSET point. As it was created in Germany it should support the 'special' German characters as well as our US/UK ones.

I've just mailed a new INIT.ORA file to the site. Fingers crossed.

Thanx.

John

Further to my last posting.

There is an ALTER DATABASE CHARACTERSET command but I suspect that if you are storing any characters that are not part of the new proposed CHARSET then you'll be in big trouble.

In my case as both DBs were created in Western Europe then the default CHARACTERSET should be okay for both Germany and England (and dare I say it, the US too).

----------------------------

John

I was once required to change the database character set. Oracle states that the character set is fixed at database creation, but I contacted Oracle support at the time (this was about 3 years ago), and they were able to supply me with a script that would make the change.

The main issue is that if you set the character set to an invalid value, the database will be corrupted.

IIRC, what the script did was to prompt for the desired new characterset, perform a translation of a character into the new character set (can't remember which SQL function this used - might even have been 'translate :) ). If the translation was successful, a confirmation message was displayed by the script, and the database character set was changed on an affirmative response to the prompt. I think it did use the alter database characterset command.

Might be worth a search on Metalink for this?

HTH

David.

To Dave_A:

When you performed the conversion, was the new character set a superset of the old, a subset of the old, or neither?

I am just curious.

Thanks Dave.

Looking at it - the character set is okay. Western European can cope with German and English/US characters.

I'm really trying to change the NLS_DATE_FORMAT as the application running on it keeps returning INVALID_MONTH errors (ORA-01843). The German default is DD-MM-YY and I think the application (via ODBC) is passing DD-MON-YY to the database. I can't get at the source code so I can't check exactly what's going on.

I've tried setting the init.ora file NLS_DATE_FORMAT parameter but it doesn't seem to take effect (??). I've also set NLS_LANGUAGE and NLS_TERRITORY and this doesn't seem to work either. The Alert file shows the new params at startup but when you fire up SQL*Plus and look - the old parameters still hold sway.

I can dynamically change them using ALTER SESSION but that's not good enough. I need it to be permanent for all sessions.

Help!

-------------------------------

John

Hi Dave (dknight)- confusion reigns - too many Daves :)

The conversion I performed was to a superset of the original character set.

This article describes how one can change the character set of a database.
It should be done with extreme caution having noted the following
limitations.

The database character set should only be changed if the characters of the
code page of the originating database correspond to the same characters
of the target database, ie. if the database was created with the
characterset US7ASCII and it is to be updated to WE8ISO8859P1. Since these
have the same encoding scheme for the first 127 bits, changing the
character set from US7ASCII to WE8ISO8859P1 will display all characters up
to 127 bits as the same character before and after. In addition, in this
particular example, if any characters have been entered with the 8th bit
set, then updating the database characterset to 8 bit will allow that 8th
bit to be displayed. You must not change the characterset from one encoding
scheme to another encoding scheme where the code pages do not correspond.
This will completely scramble your database. In addition, if case*designer
diagrams are stored in the database, this method must not be used. Contact
Worldwide Support for further details.

Before attempting to run any of the scripts below, you must take a full
cold backup of your database. In addition, the procedure must be thoroughly
tested before attempting this on a production instance.

Here's a SQL*PLUS script that allows a database's character set to be
changed to a different encoding scheme without having to rebuild the database.
set echo off
set verify off

rem The data dictionary table that records the database
rem character set is sys.props$
rem
rem SQL> describe sys.props$
rem Name Null? Type
rem ------------------------------- -------- ----
rem NAME NOT NULL VARCHAR2(30)
rem VALUE$ VARCHAR2(2000)
rem COMMENT$ VARCHAR2(2000)

rem For example:
rem
rem SQL> column c1 format a30
rem SQL> select name c1, value$ c1 from sys.props$;

rem C1 C1
rem ------------------------------ ------------------------------
rem DICT.BASE 2
rem NLS_LANGUAGE AMERICAN
rem NLS_TERRITORY AMERICA
rem NLS_CURRENCY $
rem NLS_ISO_CURRENCY AMERICA
rem NLS_NUMERIC_CHARACTERS .,
rem NLS_DATE_FORMAT DD-MON-YY
rem NLS_DATE_LANGUAGE AMERICAN
rem NLS_CHARACTERSET WE8DEC
rem NLS_SORT BINARY
rem GLOBAL_DB_NAME NLSV7.WORLD

rem NLS_CHARACTERSET can be changed by updating its value, for example:

rem update sys.props$
rem set value$ = 'WE8ISO8859P1'
rem Where name = 'NLS_CHARACTERSET';

rem The database has to be shutdown and restarted before the change
rem becomes effective.

rem It is very important to specify the character set name correctly.
rem IMPORTANT NOTE
rem =============
rem If NLS_CHARACTERSET is updated to an invalid value, it will not then
rem be possible to restart the database once it has been shutdown.
rem To recover, it will be necessary to re-create the database, since it
rem cannot be restarted to correct the invalid NLS_CHARACTERSET entry.

rem The character set name should be in uppercase.
rem The new value is not effective until the database has been shutdown and
rem restarted.
rem
rem A suggested procedure is as follows, and can be done by running this
rem script from SQL*Plus when logged into the SYSTEM account.
rem
rem USAGE : SQL> start ch_db.sql
rem
rem where is the desired database character set
rem

Prompt First check that the character set name is valid.

set echo on

select convert('a','&1','us7ascii') from dual;

set echo off

prompt If this select statement returns error ORA-01482, then the
prompt specified character set name is not valid for this installation.
prompt Abort the procedure now with Control-c

prompt To continue, press return
accept ans CHAR

Prompt Check the current value of database character set.

column c1 format a30
select name c1, value$ c1 from sys.props$
where name = 'NLS_CHARACTERSET';

prompt To continue, press return

Prompt Update to new character set

update sys.props$
set value$ = upper('&1')
where name = 'NLS_CHARACTERSET';

set echo off

prompt To continue, press return
accept ans CHAR

Prompt Check the new value of database character set

select name c1, value$ c1 from sys.props$
where name = 'NLS_CHARACTERSET';

Prompt If the value is updated as required, press return to continue and
Prompt then manually type COMMIT; to commit the change. Then shutdown and
Prompt restart the database.
Prompt
Prompt If the value is not updated as required, press return to continue and
Prompt than manually type ROLLBACK; to prevent the change.

prompt To continue, press return
accept ans CHAR

As you probably know, the MON part of the date that is passed will be in German and will be incompatible. You may need to change the format, load it as numeric and then change back to American date format.

I do not know how to change NLS_LANGUAGE and TERRITORY at the database level.

Good luck.