3

I want to set up a database (Oraclev 12.1.0.2) with NLS_CALENDAR parameter as 'Thai Buddha', at the same time I need to keep character set as AL32UTF8.

When I do this at setting up NLS_CALENDAR parameter does not get changed at DB level. SELECT SYSDATE FROM DUAL returns Current date in A.D (year is 2016, in Thai it should be 2559)

But if I alter the session and set NLS_CALENDAR parameter as 'Thai Buddha' SELECT SYSDATE FROM DUAL returns date with year as 2559.

Are there any specific guidelines I should follow to get this done?, or are there any other parameters I should change?

Dyn
  • 71
  • 8

3 Answers3

2

Set Charset:

This Should be set at database creation time - before you enter data. But if you can't recreate the database, connected as sysdba:

SHUTDOWN IMMEDIATE;
STARTUP RESTRICT;
ALTER DATABASE CHARACTER SET AL32UTF8;

Or use migration instructions provided by Oracle

Set Calendar:

To change NLS Oracle parameters at database level, you must use ALTER SYSTEM ... SCOPE=BOTH or ALTER DATABASE ...

So for your case:

ALTER SYSTEM SET NLS_CALENDAR="Thai Buddha" SCOPE=BOTH;

You must dig in for more in the Oracle Documentation

J. Chomel
  • 8,193
  • 15
  • 41
  • 69
2

In case anyone still looking for this, this can be done when setting up the database. Change following NLS_PARAMETERS 

NLS_CALENDAR  = 'Thai Buddha'; 
NLS_DATE_LANGUAGE  = 'THAI'; 
NLS_DATE_FORMAT = 'DD_MM_RR';

This will do. Cheers

Dyn
  • 71
  • 8
1

To make your changes at defualt level you need to make registry level changes.

By default value for NLS_LANG, if not set, is AMERICAN_AMERICA.US7ASCII. Similar is the case of NLS_CALENDER. Please check below link:

https://docs.oracle.com/cd/B28359_01/win.111/b32010/registry.htm

XING
  • 9,608
  • 4
  • 22
  • 38
  • Even the changes at registry level doesn't work. Is there any other possible way? – Dyn Jul 26 '16 at 05:24