Read current NLS_NUMERIC_CHARACTERS session setting

Question

I have to run a report with numeric decimals as parameters in different territories with different NLS_NUMERIC_CHARACTERS, aka comma separators.

NLS_NUMERIC_CHARACTERS is set on the session level eg. with

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';

Expression NLS_NUMERIC_CHARACTERS = ',.' means the the decimal marker is a comma, and the thousands marker to a point.

To get the user input right in all the different territories I assume that the NLS_NUMERIC_CHARACTERS settings of the session is set correctly. I want to evaluate that session state into a field value s_nlsparam and use it to convert the report parameters correctly with

.. to_number(expr,format, v_nlsparam) ...

so far the plan. This would tame any potential ORA-01722: invalid number in the multi-national context.

Now I can't find a way to read the current session state of the NLS_NUMERIC_CHARACTERS settings. I checked the view v$session, but it doesn't offer any such field. Neither I found such a parameter using:

select sys_context('USERENV', .... ) from dual

Is there a way to get it?

Answer 1

You can see that info using this select:

select value 
  from nls_session_parameters
 where parameter = 'NLS_NUMERIC_CHARACTERS';

Answer 2

Are you sure that you really need to know the current value? Since you are assuming that the setting is set correctly in your user session, it would generally make more sense to use the generic format model in your conversion functions. For example

to_number( some_string, '999999999D99' )

will convert a string to a number (add more 9's if you need to support numbers larger than a billion) using whatever the session's decimal point is. If the session uses the comma as the decimal separator, it will look for a comma. If the session uses the period as the decimal separator, it will look for a period. Similarly

to_char( some_number, '999G999G999D99' ) 

will convert a number to a string using whatever the group and decimal separator the session uses.