Oracle NLS Settings – Prevent Override by Environment Variable

oracleoracle-11g-r2parameter

We have a databases server that is used for development which has hundreds of database instances for different branches/builds of our application. And we needed an special instance that has NLS_CALENDAR='Thai Buddha' to test a particular bug.

We have set up a database and included NLS_CALENDAR='Thai Buddha' setting in init.ora. However, we still get 'GREGORIAN' for NLS_CALENDAR, since environment variables overrides the setting in init.ora.

NLS_CALENDAR value is set as follows:

NLS_SESSION_PARAMETERS: 'GREGORIAN'

NLS_INSTANCE_PARAMETERS: 'Thai Buddha'

NLS_DATABASE_PARAMETERS: 'GREGORIAN'

ALTER SESSION is not an option because then we have to modify our application code to issue an ALTER SESSION whenever a user logs in (This is not possible because we have multiple clients already deployed on various machines). Modifying the environment setting is not an option either because there are other database instances running on the same host.

I have tried ALTER SYSTEM, but it gives following oracle error:

ORA-02096: specified initialization parameter is not modifiable with this
option

How can we make a single database instance to ignore the environment setting? Or is there any other way we can make our instance default to 'Thai Buddha' when it comes to NLS_CALENDAR setting?

Best Answer

The documentation clearly describes the precedence for NLS settings: http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch3globenv.htm#NLSPG188

1 (highest) Explicitly set in SQL functions
2           Set by an ALTER SESSION statement
3           Set as an environment variable
4           Specified in the initialization parameter file
5           Default

You can choose the first two from these. A logon trigger can also issue the ALTER SESSION, and you don't need to modify the application code for that:

CREATE OR REPLACE TRIGGER SET_NLSCALENDAR_ONLOGIN AFTER LOGON ON DATABASE
BEGIN
  IF SYS_CONTEXT('USERENV','SESSION_USER') = 'SCOTT' THEN
    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_CALENDAR=''Thai Buddha''';
  END IF;
END;
/

Check setting for SCOTT and other user:

SQL> connect scott/tiger
Connected.
SQL> select value from nls_session_parameters where parameter = 'NLS_CALENDAR';

VALUE
-----------
Thai Buddha

SQL> select value from nls_database_parameters where parameter = 'NLS_CALENDAR';

VALUE
-----------
GREGORIAN

SQL> conn hr/hr
Connected.
SQL> select value from nls_session_parameters where parameter = 'NLS_CALENDAR';

VALUE
-----------
GREGORIAN
Related Question