German installation

m3i used Ask the Experts™

Oracle version:
OS version: HP/UX 10.20

I need to install Oracle7 Server in German and I would like
to know:

1) What is the most common character set used in Germany? WE8DEC?
2) Do I need to set something else than database character set and
   NLS_LANG environement variable?
3) What is the difference between NLS_SORT=German,XGerman,German_Din
   and XGerman_Din?

Thank you,
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi m3i,

Let me send you some useful information in order to choose the right languages for you. I really don't know if the most common character set for Germany is WE8DEC.


 Within Oracle's National Language Architecture, the operation of language dependent features is controlled by defining the parameter NLS_LANG at the start of a user session. The syntax for NLS_LANG is composed of three independent components. They are:
 NLS_LANG = <language>_<territory>.<character set>
 <language> specifies the values for language-dependent conventions including:
- language for messages
- language for day and month names
- default sort sequence
 <territory> specifies values for territory-dependent conventions including:
- default date format
- decimal character
- group seperator
- local currency symbol
- ISO currency symbol
 <character set> specifies the character encoding scheme of the end-users's terminal.
 Note that <language> also defines default values for <territory> and<character set> if these are omitted.

NLS_LANG is defined as an environment variable, the specific mechanism being platform-dependent. For example, on UNIX:
       $setenv NLS_LANG American_America.WE8ISO8859P1
he effect of specifying NLS_LANG is to define the NLS environment for the user session. The following is an illustration of NLS default formatting of numbers and dates using the above example.
       select ename,hiredate,round(sal/12,2) sal from emp;
       ENAME      HIREDATE  SAL      
      ---------- --------- ----------
      Clark      09-DEC-88    4195.83
      Turner     17-FEB-87    5416.67
      Miller     23-MAR-87    4366.67
      Mller      01-APR-89    3795.83
      Hscht      10-MAY-90    2933.33
      Hlne       01-NOV-91    4066.67

 When specifying another language, for example:
       $setenv NLS_LANG French_France.WE8ISO859P1
 the same query returns:
       select ename,hiredate,round(sal/12,2) sal from emp;
       ENAME      HIREDATE  SAL      
      ---------- --------- ----------
      Clark      09/12/88     4195,83
      Turner     17/02/87     5416,67
      Miller     23/03/87     4366,67
      Mller      01/04/89     3795,83
      Hscht      10/05/90     2933,33
      Hlne       01/11/91     4066,67

Note that the change in formatting is transparent to the application using such a query, and is controlled by setting the appropriate NLS environment at runtime.

Additional NLS parameters can be included in the instance startup file (INIT.ORA) to specify the default server language environment when NLS_LANG is not defined for a user session. Note that this default is not effective on the client side.
Default values for <language> and <territory> are specified using the parameters NLS_LANGUAGE and NLS_TERRITORY, for example:
       NLS_LANGUAGE = Spanish
      NLS_TERRITORY = Spain
There is no default equivalent for the <character set> component of NLS_LANG since this defines the character encoding scheme being used on the client side.
Additional NLS parameters provide flexibility in controlling the
operation of specific language-dependent features.  These include:
NLS_DATE_FORMAT          - default date format
NLS_DATE_LANGUAGE        - language for day and month names
NLS_NUMERIC_CHARACTERS   - decimal character and group seperator
NLS_CURRENCY             - local currency symbol
NLS_ISO_CURRENCY         - ISO currency symbol
NLS_SORT                 - sort sequence
 For example:
       NLS_DATE_FORMAT = 'DD/Mon/YY'
      NLS_DATE_LANGUAGE = German
      NLS_CURRENCY = 'Dfl'
      NLS_ISO_CURRENCY = France
      NLS_SORT = Swedish
These parameters enable the values of specific language and territory-dependent conventions to be specified explicitely.  If specified, they override the corresponding values defined implicitly by NLS_LANGUAGE and NLS_TERRITORY.

The NLS environment can also be changed during a session using the above parameters in the ALTER SESSION statement, using the syntax:
       ALTER SESSION SET <nls_parameter> = <value>
 For example:
       $setenv NLS_LANG Italian_Italy
       select ename,hiredate,round(sal/12,2) sal from emp;
      ---------- --------- ----------
      Clark      09-Dic-88    4195,83
      Hlne       01-Nov-91    4066,67
      Hscht      10-Mag-90    2933,33
      Miller     23-Mar-87    4366,67
      Mller      01-Apr-89    3795,83
      Turner     17-Feb-87    5416,67
       alter session set NLS_DATE_LANGUAGE = German
                        NLS_DATE_FORMAT = 'DD.MON.YY'
                        NLS_NUMERIC_CHARACTERS = '.,';
                select ename,hiredate,round(sal/12,2) sal from emp;
      ---------- --------- ----------
      Clark      09.DEZ.88    4195.83
      Hlne       01.NOV.91    4066.67
      Hscht      10.MAI.90    2933.33
      Miller     23.MR.87     4366.67
      Mller      01.APR.89    3795.83
      Turner     17.FEB.87    5416.67

The ALTER SESSION mechanism is used implicitly to automatically set the NLS environment on a per-session basis.  If NLS_LANG is defined for a user session, the procedure that connects the user session to the database automatically executes an ALTER SESSION statement for
NLS_LANGUAGE and NLS_TERRITORY according to the values of <language> and <territory> specified by NLS_LANG.

In addition to the default format being specified by the NLS environment, additional format masks are provided by Oracle7 in the TO_CHAR and TO_DATE functions, namely:
 IW            - returns the ISO week number
I, IY, IYY and IYYY       - return the year relating to the ISO week number
RM and rm       - returns the month as a Roman numeral

For example, if sysdate were 1st Jan 1988:
       select to_char(sysdate,'IW IYYY') today from dual;
      53 1987

In addition to the default radix character (the character seperating the integer and decimal parts of a number) being specified by the NLS environment, additional format masks are provided by Oracle7 in the TO_NUMBER and TO_CHAR functions, namely:
D - returns the decimal character
G - returns the group seperator
L - returns the local currency symbol
C - returns the international currency symbol (as per ISO
    4217 1987-07-15)

RN,rn - returns a number in its roman numeral equivalent

For example:
       $setenv NLS_LANG French_France
       select ename,to_char(round(sal/12,2),'99G999D99')                            sal from emp;
       ENAME      SAL
      ---------- ----------
      Clark        4.195,83
      Hlne         4.066,67
      Hscht        2.933,33
      Miller       4.366,67
      Mller        3.795,83
      Turner       5.416,67

The decimal character and group seperator have to be different.

Note that the syntax for NLS_NUMERIC_CHARACTERS is:
       NLS_NUMERIC_CHARACTERS = '<decimal character><group seperator>'
SQL functions whose behavior is dependent on the NLS environment allow NLS parameters to be specified in the function. This enables their behavior to be independent of the current session NLS parameters.
For example 'language-independent' integrity contraints, stored procedures and views can be defined where these use string literals containing dates and numbers.
For example the interpretation of the string literals '1-JAN-89' and
'13.000,00' depends on the NLS parameters in effect for the session.
They can be specified to be independent of the NLS environment using:


Question 3.

Sorting is required in retrieving data in a certain order.  The convention for sorting is usually according to the binary values used to encode the characters.  Therefore, sorting will only be correct if the binary value corresponds in order of the character value.
Unfortunately, the binary ordering does not always correspond to the character ordering.  Therefore to maintain order integrity, Oracle uses a different type of sort: a linguistic sort.
This type of sort sorts data by their character encoding schemes as opposed to their actual binary value.

Text is conventionally sorted according to the binary values used to encode the characters.  Normally, this does not produce a sort order that is linguistically correct.  It will only be correct if the encoding scheme specifies all characters in ascending binary value according to the appropriate alphabetic convention.  Most encoding schemes do not follow any such convention. In addition, uppercase characters are separate from lowercase. A further problem arises with multi-national coding schemes that support several languages, since these may use different
alphabetic sequences.  For example, the '' is sorted before 'b' in
German but after 'z' in Swedish.
To overcome these limitations, Oracle's national language architecture provides a second type of sort:a linguistic sort. This enables text in single-byte character encoding schemes to be sorted according to specific linguistic conventions, independent of the binary values of characters.   Sorting of data in multi-byte character encoding schemes is dependent on the character encoding scheme, and is done according to the numeric sequence of characters as defined by the encoding scheme.
Oracle7 uses named linguistic sort sequences to specify how character data should be sorted. A linguistic sort name has no direct connection to language, though in most cases a linguistic sort is defined using the language name. For some languages, additional 'extended' linguistic sorts are defined (ie, XSpanish). These extended sort sequences cater for language-specific special cases (ie, the German 'sharp s', the Spanish 'll' and
'ch'). Conventionally these linguistic sorts are named with a preceeding
'X'. Specific linguistic sorts supported for single-byte character
encoding schemes are:
Arabic, Czech and XCzech, Danish and XDanish, Dutch and XDutch, Finnish, French, German and XGerman, German_Din and XGerman_Din, Greek, Hebrew, Hungarian and XHungarian, Icelandic, Italian, Latin, Lithuanian, Norwegian, Polish, Russian, Slovak and XSlovak, Spanish and XSpanish, Swedish, Swiss
and XSwiss, Thai_Dictionary, Thai_Telephone, Turkish and XTurkish, West_European and XWest_European.
Linguistic sort sequences are defined by name, and specified with the NLS_SORT parameter using the syntax:
                NLS_SORT = BINARY | <name>
where <name> is the name of a linguistic sort sequence.
For example:
                NLS_SORT = BINARY
                NLS_SORT = West_European
                NLS_SORT = German
Note that <name> has no direct connection to <language>. However in most cases each language will have a supported linguistic sort sequence that utilizes the same name.
For example:
        $setenv NLS_LANG German
        select letter from letters order by letter;

        alter session set NLS_SORT = Swedish;
        select letter from letters order by letter;
In some languages, additional 'extended' linguistic sorts are defined to accommodate for language-specific special cases involving digraphs (double characters). The definition of an 'extended' linguistic sort sequence identifies whether sorting and case conversion are to be adhered to.
For example, the linguistic sort sequence 'XSpanish' specifies that the double characters 'ch' and 'll' are sorted as single characters.
        alter session set NLS_SORT = XSpanish;
        select ename from emp order by ename;
The functions NLS_UPPER, NLS_LOWER and NLS_INITCAP in Oracle7 cater for special case conversion.  For example, the linguistic sort sequence 'XGerman' can specify that NLS_UPPER caters for the German :
        alter session set NLS_SORT = XGerman;
        select nls_upper('strae') uc from dual;
Note that the length of a character string can change.
When using comparison operators, characters are compared according to their binary values in the character encoding scheme.  A character is greater than another if it has a higher binary value in the database character set.  Since the binary sequence of characters may not match the linguistic sequence for a particular language, such comparisons may not be linguistically 'correct'.  To enable such comparisons to
reflect linguistic conventions, the NLSSORT function can be used, for example:
        $setenv NLS_LANG = German
        select letter from letters
        where nlssort(letter) > nlssort('b')
        order by letter;
        alter session set NLS_SORT = Swedish;
        select letter from letters
        where nlssort(letter) > nlssort('b')
        order by letter;
Note NLSSORT has to be used on both sides of the comparison operator.

I hope that this helps you. If not, don't hesitate to contact me directly to my e-mail address:

Best regards,

Juan Manuel Ponce Martinez
DBA - Buenos Aires - Argentina

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial