1

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?

5
  • What do you mean by "we still get 'GREGORIAN' for NLS_CALENDAR"
    – miracle173
    Sep 1, 2014 at 6:59
  • @miracle173: I mean the value in NLS_SESSION_PARAMETERS view. aka the session parameter value for NLS_CALENDAR. Sep 1, 2014 at 7:08
  • You should add this to your question. The NLS_SESSION_PARAMETERS shows the session parameter values. These are defined by the environment variables. What do the NLS_INSTANCE_PARAMETERS and NLS_DATABASE_PARAMETERS show?
    – miracle173
    Sep 1, 2014 at 7:20
  • 2
    Maybe you can use a login trigger to set your session parameters.
    – miracle173
    Sep 1, 2014 at 7:26
  • What kind of client are you running? Does it run on the same machine as the database instances? Why are you not able to change the environment settings? Maybe you can describe your system and your problem a little bit more in detail.
    – miracle173
    Sep 1, 2014 at 7:32

1 Answer 1

5

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
2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.