A script-enabled browser is required for this page to function properly.

ALTER SESSION Examples

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

Comparing strings examples

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.

Format mask examples

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');

NLS_LANG examples

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

More examples:

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

PL/SQL libraries example