In Oracle Database, there are a few different NLS parameters that determine how currency is displayed when using functions like TO_CHAR() to format a number as a currency.

• NLS_CURRENCY specifies the string to use as the local currency symbol for the L number format element.
• NLS_ISO_CURRENCY determines what to use for the C format element.
• NLS_DUAL_CURRENCY specifies what to use for the U format element.

The default value for these is determined by the NLS_TERRITORY parameter.

You can change each of these parameters individually if you wish, but in most cases, you’re better off changing the NLS_TERRITORY parameter. That’s because, doing so implicitly changes all the other currency parameters to their default values for that territory.

Return the Current Values

First, let’s query the V$NLS_PARAMETERS view to find out what our default values are:

SELECT 
    PARAMETER,
    VALUE
FROM V$NLS_PARAMETERS
WHERE PARAMETER IN (
    'NLS_TERRITORY',
    'NLS_CURRENCY',
    'NLS_ISO_CURRENCY',
    'NLS_DUAL_CURRENCY'    
    );

Result:

           PARAMETER        VALUE 
____________________ ____________ 
NLS_TERRITORY        AUSTRALIA    
NLS_CURRENCY         $            
NLS_ISO_CURRENCY     AUSTRALIA    
NLS_DUAL_CURRENCY    $           

The territory is Australia, and all currency parameters use the default values for this territory.

Change all Currency Symbols

Let’s change the NLS_TERRITORY parameter to a new value:

ALTER SESSION SET NLS_TERRITORY = 'Denmark';

Result:

Session altered.

Now let’s check our currency NLS parameters again:

SELECT 
    PARAMETER,
    VALUE
FROM V$NLS_PARAMETERS
WHERE PARAMETER IN (
    'NLS_TERRITORY',
    'NLS_CURRENCY',
    'NLS_ISO_CURRENCY',
    'NLS_DUAL_CURRENCY'    
    );

Result:

           PARAMETER      VALUE 
____________________ __________ 
NLS_TERRITORY        DENMARK    
NLS_CURRENCY         kr         
NLS_ISO_CURRENCY     DENMARK    
NLS_DUAL_CURRENCY    €          

Regarding the NLS_ISO_CURRENCY parameter, although this determines which ISO currency symbol to use, the actual currency symbol isn’t returned here.

The following example illustrates what I mean:

SELECT 
    TO_CHAR(45, 'L99') AS "L",
    TO_CHAR(45, 'C99') AS "C",
    TO_CHAR(45, 'U99') AS "U"
FROM DUAL;

Result:

               L             C              U 
________________ _____________ ______________ 
         kr45         DKK45            €45   

So, even though the NLS_ISO_CURRENCY parameter has a value of DENMARK, the C format element returned DKK, which is the actual ISO currency symbol for Denmark.

Change the Currency Symbols Individually

As mentioned, you can explicitly set each NLS parameter individually.

For example, having set the territory to Denmark in the previous example, we can now override any (or all) of the currency symbols by setting them explicitly:

ALTER SESSION SET NLS_CURRENCY = '$';
ALTER SESSION SET NLS_ISO_CURRENCY = 'AUSTRALIA';
ALTER SESSION SET NLS_DUAL_CURRENCY = '$';

Result:

Session altered.


Session altered.


Session altered.

Now here’s what we get when running the previous SELECT statement:

SELECT 
    TO_CHAR(45, 'L99') AS "L",
    TO_CHAR(45, 'C99') AS "C",
    TO_CHAR(45, 'U99') AS "U"
FROM DUAL;

Result:

               L             C                U 
________________ _____________ ________________ 
          $45         AUD45              $45   

So, even though our territory remains at Denmark, our currency symbols reflect a different territory (in this case, Australia).

One thing to be mindful of when doing this is that, you could end up in a situation where your currency parameters don’t reflect other parameters, such as NLS_NUMERIC_CHARACTERS (which determines which characters to use for the group separator and decimal character).

For example:

SELECT 
    TO_CHAR(4599.60, 'L99G999D99') AS "L",
    TO_CHAR(4599.60, 'C99G999D99') AS "C",
    TO_CHAR(4599.60, 'U99G999D99') AS "U"
FROM DUAL;

Result:

                      L                    C                       U 
_______________________ ____________________ _______________________ 
           $4.599,60          AUD4.599,60               $4.599,60   

In this case, we have Australian currency symbols, but the group separator is a period (.) and the decimal character is a comma (,), which does not reflect Australian conventions (it reflects the convention used by Denmark). Australian convention is the other way around – the group separator is a comma (,) and the decimal character is a period (.).

To illustrate this, here’s what we get if we simply reset the territory to Australia, then run the statement again:

ALTER SESSION SET NLS_TERRITORY = 'Australia';

SELECT 
    TO_CHAR(4599.60, 'L99G999D99') AS "L",
    TO_CHAR(4599.60, 'C99G999D99') AS "C",
    TO_CHAR(4599.60, 'U99G999D99') AS "U"
FROM DUAL;

Result:

                      L                    C                       U 
_______________________ ____________________ _______________________ 
           $4,599.60          AUD4,599.60               $4,599.60