NLS_NUMERIC_CHARACTERS reset when accessed from weblogic datasource

Question

I have problem with certain code and the root cause turned out to be NLS_NUMERIC_CHARACTERS setting at session level.

when I run the query directly on database:

SQL> select 'nls_database_parameters' , p.* from nls_database_parameters p where PARAMETER = 'NLS_NUMERIC_CHARACTERS'
  2  union all
  3  select 'nls_session_parameters', p.* from nls_session_parameters p where PARAMETER = 'NLS_NUMERIC_CHARACTERS'
  4  union all
  5  select 'nls_instance_parameters', p.* from nls_instance_parameters p where PARAMETER = 'NLS_NUMERIC_CHARACTERS';
'NLS_DATABASE_PARAMETERS' PARAMETER                VALUE
------------------------- ------------------------ -----
nls_database_parameters   NLS_NUMERIC_CHARACTERS   .,
nls_session_parameters    NLS_NUMERIC_CHARACTERS   .,
nls_instance_parameters   NLS_NUMERIC_CHARACTERS   .,

But, when I run this same query from weblogic datasource

select 'nls_database_parameters' , p.* from nls_database_parameters p where PARAMETER = 'NLS_NUMERIC_CHARACTERS'
union all
select 'nls_session_parameters', p.* from nls_session_parameters p where PARAMETER = 'NLS_NUMERIC_CHARACTERS'
union all
select 'nls_instance_parameters', p.* from nls_instance_parameters p where PARAMETER = 'NLS_NUMERIC_CHARACTERS';

--  'NLS_DATABASE_PARAMETERS',  PARAMETER,  VALUE
1   nls_database_parameters NLS_NUMERIC_CHARACTERS  .,
2   nls_session_parameters  NLS_NUMERIC_CHARACTERS  , 
3   nls_instance_parameters NLS_NUMERIC_CHARACTERS  .,

The session value is wrong and is being reset by some trigger/script when accessed through weblogic datasource, I checked the weblogic startup scripts etc to see if we reset somewhere but no help.

Any pointers/ideas in this direction much appreciated if somebody faced similar issue.

Answer 1

It is unlikely that this is being set explicitly. I'd wager that it is being set implicitly as a result of the user.language and user.country values used to initialize your JVM. I don't know exactly how WebLogic gets these by default but I would guess that the server's locale is set to one where the comma is the decimal separator.

You should be able to override the default WebLogic settings by setting the language and country in the startWebLogic script

set JAVA_OPTIONS=%JAVA_OPTIONS% -Duser.language=en -Duser.country=US

Of course, that may have impact other things as well (the language of error messages for example).

Answer 2

I'm not familiar with the Weblogic side of things, but you can scoop up material from within Oracle by searching its source code objects (triggers, procedures, packages, functions) in DBA_SOURCE. It may contain "execute immediate" statements (or the equivalent DBMS_SQL dynamic SQL call) with something similar to:

alter session set nls_numeric_characters = ',.';

Or it could also be calling DBMS_SESSION.SET_NLS procedure:

dbms_session.set_nls('NLS_NUMERIC_CHARACTERS', '".,"');

Querying it as follows should point you to some suspects (and likely some false positives too). You can improve it by filtering by owner.

SELECT *
  FROM dba_source s
 WHERE (upper(s.text) LIKE '%SET\_NLS%' ESCAPE '\'
        OR upper(s.text) LIKE '%NLS\_NUMERIC\_CHARACTERS%' ESCAPE '\')
       AND owner != 'SYS';

The dynamic view V$SQL may have your command there if it's an anonymous PL/SQL block or script.

SELECT *
 FROM v$sql s
WHERE (upper(s.sql_fulltext) LIKE '%SET\_NLS%' ESCAPE '\'
       OR upper(s.sql_fulltext) LIKE '%NLS\_NUMERIC\_CHARACTERS%' ESCAPE '\')
      AND s.parsing_schema_name = 'YOUR_DB_USER';

If you have already spotted the code that is causing the error and it's under control you could also fix the conversion by doing it explicitly in to_number (or to_char) with the syntax below:

SQL> select to_number('1.5', '999999D999', 'NLS_NUMERIC_CHARACTERS=,.'),
  2         to_number('1.5', '999999D999', 'NLS_NUMERIC_CHARACTERS=.,')
  3    from dual;
TO_NUMBER('1.5','999999D999',' TO_NUMBER('1.5','999999D999','
------------------------------ ------------------------------
                            15                            1,5

This way you become nls session immune to it, but it may not be feasible to fix all your code to use this.