Passing NLS settings to TO

Many of the number format model elements listed in Table 9-5 ultimately derive their meaning from one of the NLS parameters. For example, the G element represents the numeric group separator, which is the second character in the NLS_NUMERIC_CHARACTERS setting in effect when the conversion takes place. You can view current NLS parameter settings by querying the NLS_SESSION_PARAMETER view:

SQL> SELECT * FROM nls_session_parameters PARAMETER VALUE------------------------- ---------------NLS_LANGUAGE AMERICANNLS_TERRITORY AMERICANLS_CURRENCY $NLS_ISO_CURRENCY AMERICANLS_NUMERIC_CHARACTERS.,NLS_CALENDAR GREGORIANNLS_DATE_FORMAT DD-MON-RR

Some NLS parameter settings are by default dependent on others. For example, set NLS_TERRITORY to AMERICA, and Oracle defaults NLS_NUMERIC_CHARACTERS TO ".,". If you need to, you can then override the NLS_NUMERIC_CHARACTERS setting (using an ALTER SESSION command, for example).

On rare occasions, you may want to override specific NLS parameter settings for a single call to TO_NUMBER. In the following example, we invoke TO_NUMBER and specify NLS settings corresponding to NLS_TERRITORY=FRANCE:

a:= TO_NUMBER('F123.456,78','L999G999D99', 'NLS_NUMERIC_CHARACTERS='',.''' || ' NLS_CURRENCY=''F''' || ' NLS_ISO_CURRENCY=FRANCE');

Because our NLS parameter string is so long, we've broken it up into three separate strings concatenated together so that our example fits nicely on the page. Note our doubling of quote characters. The setting we want for NLS_NUMERIC_CHARACTERS is:

NLS_NUMERIC_CHARACTERS=',.'

We need to embed this setting into our NLS parameter string, and to embed quotes within a string we must double them, so we end up with:

'NLS_NUMERIC_CHARACTERS='',.'''

The three NLS parameters we've set in this example are the only three you can set via TO_NUMBER. We don't know why that is. It certainly would be much more convenient if we could simply do the following:

a:= TO_NUMBER('F123.456,78','L999G999D99','NLS_TERRITORY=FRANCE');

But unfortunately, NLS_TERRITORY is not something you can set via a call to TO_NUMBER. You are limited to specifying NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, and NLS_ISO_CURRENCY.

For detailed information on setting the various NLS parameters, please see Oracle's Globalization Support Guide, which is part of the Oracle9i Database documentation set.

Avoid using the third argument to TO_NUMBER—we believe it's better to rely on session settings to drive the way in which PL/SQL interprets format model elements such as L, G, and D. Instead of your having to hardcode such information throughout your programs, session settings can be controlled by the user outside the bounds of your code.

Passing NLS settings to TO_NUMBER