Oracle Database provides the TO_CHAR(number) function that enables you to format numbers based on a given format model. There are three format elements that you can use with the TO_CHAR() function in order to return a value as a currency.

Currency Format Elements

The currency format elements are L, C, and U.

C	Returns the ISO currency symbol (the current value of the NLS_ISO_CURRENCY parameter).
L	Returns the local currency symbol (the current value of the NLS_CURRENCY parameter).
U	Returns the Euro (or other) dual currency symbol, determined by the current value of the NLS_DUAL_CURRENCY parameter.

These format models allow you to output the currency sign based on your currency/region settings, rather than having to provide a fixed currency symbol.

While it’s true that you can provide a string literal for the currency (for example, a dollar sign ($) for dollars), this assumes that the currency is denominated in that hardcoded value. There are many other possible currencies around the world, and the format elements are able to dynamically return the local currency symbol for the user’s session.

Example

Here’s an example to demonstrate:

ALTER SESSION SET NLS_TERRITORY = 'Australia';
SELECT 
    TO_CHAR(12345, 'fmL99G999D00') AS "r1",
    TO_CHAR(12345, 'fmC99G999D00') AS "r3",
    TO_CHAR(12345, 'fmU99G999D00') AS "r3"
FROM DUAL;

Result:

           r1              r3            r3 
_____________ _______________ _____________ 
$12,345.00    AUD12,345.00    $12,345.00   

The only difference between these three columns is the currency format element. The first uses L, the second uses C, and the third uses U.

They all use the fm format modifier to suppress any padding. They also use the 9 and 0 format elements to output the numbers (the 0 element includes leading/trailing zeros). They also include a group separator (represented by G), a decimal character (represented by D).

Dual Currencies

In the above example, I set the NLS_TERRITORY to Australia. This resulted in the same currency symbol being returned in two of the three columns (i.e. the r1 and r3 columns).

In the following example, I use a different territory:

ALTER SESSION SET NLS_TERRITORY = 'Denmark';
SELECT 
    TO_CHAR(12345, 'fmL99G999D00') AS "r1",
    TO_CHAR(12345, 'fmC99G999D00') AS "r3",
    TO_CHAR(12345, 'fmU99G999D00') AS "r3"
FROM DUAL;

Result:

            r1              r3            r3 
______________ _______________ _____________ 
kr12.345,00    DKK12.345,00    €12.345,00   

This time we get a different currency symbol for each of the r1 and r3 columns.

See How to Check the Values of the NLS Parameters if you need to check them.

The 'nlsparam' Argument

When using the TO_CHAR() function, the 'nlsparam' argument can be used to specify the decimal character and the group separator, the local currency symbol, and the international currency symbol.

It takes the following form:

'NLS_NUMERIC_CHARACTERS = ''dg''
   NLS_CURRENCY = ''text''
   NLS_ISO_CURRENCY = territory '

Example:

SELECT 
    TO_CHAR(
        1234.56, 
        'fmL99G999D99',
        'NLS_NUMERIC_CHARACTERS = '',.''
        NLS_CURRENCY = ''€''
        NLS_ISO_CURRENCY = Germany'
    )
FROM DUAL;

Result:

€1.234,56

Here it is again, but this time I replace L with C in the format model:

SELECT 
    TO_CHAR(
        1234.56, 
        'fmC99G999D99',
        'NLS_NUMERIC_CHARACTERS = '',.''
        NLS_CURRENCY = ''€''
        NLS_ISO_CURRENCY = Germany'
    )
FROM DUAL;

Result:

EUR1.234,56

The C returns the ISO currency symbol, which in this case is EUR.

See this Full List of Number Format Elements in Oracle for a list of format elements that can be used to format numbers with the TO_CHAR() function.