DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: NLS - resetting German to American

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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?

  2. #2
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    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
    David Knight
    OCP DBA 8i, 9i, 10g

  3. #3
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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?


  4. #4
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    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.
    David Knight
    OCP DBA 8i, 9i, 10g

  5. #5
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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

  6. #6
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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

  7. #7
    Join Date
    Feb 2001
    Posts
    123
    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.

  8. #8
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    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.
    David Knight
    OCP DBA 8i, 9i, 10g

  9. #9
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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

  10. #10
    Join Date
    Feb 2001
    Posts
    123
    Hi Dave (dknight)- confusion reigns - too many Daves

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width