Find Out Which Currency Symbol your Session Uses in Oracle

by

In Oracle Database, the following NLS parameters can be used to determine how currencies are displayed for the current session:

  • 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.

The L, C, and U number format elements can be used in functions like TO_CHAR() to format a number as a currency.

How to Return the Currency Symbol

You can find out which currency symbol your current session uses by querying the V$NLS_PARAMETERS view.

Example:

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        GERMANY    
NLS_CURRENCY         €          
NLS_ISO_CURRENCY     GERMANY    
NLS_DUAL_CURRENCY    €

The default value of these parameters are derived by the NLS_TERRITORY parameter. Here’s my current setting for that parameter:

SELECT VALUE
FROM V$NLS_PARAMETERS
WHERE PARAMETER = 'NLS_TERRITORY';

Result:

GERMANY

By changing that parameter, the value of the other parameters will be updated to the default values for that territory (unless the new territory uses the same value as the old territory for a given parameter).

However, you can also change each parameter directly, which enables you to have a different currency symbol than the default one for that territory.

Format Elements in Action

Here’s an example of where the above NLS parameters can come in use:

ALTER SESSION SET NLS_TERRITORY = 'DENMARK';

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