The following statement changes the decimal character to a comma and the group separator to a period:
ALTER SESSION
SET NLS_NUMERIC_CHARACTERS = ',.';
These new characters are returned when you use their number format elements D and G:
SELECT TO_CHAR(SUM(sal), 'L999G999D99') Total FROM emp
TOTAL
--------------
FF29.025,00
The next statement changes the ISO currency symbol to the ISO currency symbol for the territory America:
ALTER SESSION
SET NLS_ISO_CURRENCY = America
The ISO currency symbol defined for America is used:
SELECT TO_CHAR(SUM(sal), 'C999G999D99') Total FROM emp
TOTAL
---------------
USD29.025,00
The next statement changes the local currency symbol to Euro:
ALTER SESSION
NLS_CURRENCY = 'Euro'
The new local currency symbol is returned when you use the L number format element:
SELECT TO_CHAR(SUM(sal), 'L999G999D99') Total FROM emp
TOTAL
--------------
Euro 29.025,00
Suppose you have a column (COL1) that contains the values ABC, ABZ, BCD and ÄBC in the ISO 8859/1 8-bit character set. Suppose also, that you wrote the following query:
SELECT COL1 FROM TAB1 WHERE COL1 > 'B'
The query would return both BCD and ÄBC since Ä has a higher numeric value than B.
You can perform linguistic comparisons using NLSSORT in the WHERE clause, as follows:
WHERE NLSSORT(column) operator NLSSORT(string)
Notice that NLSSORT has to be used on both sides of the comparison operator.
SELECT COL1 FROM TAB1 WHERE NLSSORT(COL1) > NLSSORT('B')
If a German linguistic sort is being used, this will not return strings beginning with Ä, because Ä comes before B in the German alphabet. If a Swedish linguistic sort is being used, those strings would be returned, because Ä comes after Z in the Swedish alphabet.
For NLS-compliant applications, avoid hard-coding a string containing a month name. However, if a hard coded month name is essential, avoid using the COPY Built-in. If you use COPY, the month name may be incorrect, depending on which language is specified. (To specify a date in a library, use a variable for the date and COPY will work.)
Language-dependent example (not recommended):
:emp.hiredate := '30-DEC-96';
copy ('30-DEC-96','emp.hiredate');
Instead, use TO_DATE, as shown in the following example.
Language-independent example (recommended):
:emp.hiredate := to_date('30-12-1996','DD-MM-YYYY');
Suppose you want your application to run in French. The application will be used in France and data will be displayed using the WE8ISO8895P1 character set. You would set NLS_LANG as follows:
NLS_LANG=French_France.WE8ISO8895P1
Now, suppose you still want your application to run in French, but this time it will be used in Switzerland. You would set NLS_LANG as follows:
NLS_LANG=French_Switzerland.WE8ISO8895P1
NLS_LANG=Norwegian_Norway.NDK7DEC
NLS_LANG=Norwegian_Norway.WE8ISO8895P1
NLS_LANG=Japanese_Japan.JA16SJIS
NLS_LANG=Arabic_Egypt.AR8MSWIN1256
NLS_LANG=American_America.AR8MSWIN1256
NLS_LANG=American_America.WE8ISO8859P1
FUNCTION nls_appl_mesg(index_no NUMBER)
RETURN CHAR
IS
msg CHAR(80);
BEGIN
IF index_no = 1001 THEN msg := 'L''impiegato che Voi cercate
non esiste...';
ELSIF index_no = 1002 THEN msg := 'Lo stipendio non puo essere minore
di zero.';
ELSIF ...
:
ELSE msg := 'ERRORE: Indice messaggio inesistente.';
END IF;
RETURN msg;
END;