We are faced with an unusual problem. We created a *11.2.0.2.0* DB with character set AL32UTF8. This is for an application which is in Persian. So data and dates must be in Persian. We want the entire DB to be in Persian. But the thing is for client sessions in SQL*Plus, i.e. when we login into SQL*Plus and execute
SELECT SYSDATE FROM DUAL;
we get dates in Gregorian. i.e.
01-DEC-11
These are the parameters for the DB, Instance and Session:
SELECT * FROM nls_database_parameters ORDER BY parameter;
gives
PARAMETER VALUE
------------------------------ -----------------------------------
NLS_CALENDAR GREGORIAN
NLS_CHARACTERSET AL32UTF8
NLS_COMP BINARY
NLS_CURRENCY $
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_DUAL_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .,
NLS_RDBMS_VERSION 11.2.0.2.0
NLS_SORT BINARY
NLS_TERRITORY AMERICA
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
SELECT * FROM nls_instance_parameters ORDER BY parameter;
gives
PARAMETER VALUE
------------------------- -----------------------
NLS_CALENDAR PERSIAN
NLS_COMP BINARY
NLS_CURRENCY
NLS_DATE_FORMAT
NLS_DATE_LANGUAGE
NLS_DUAL_CURRENCY
NLS_ISO_CURRENCY
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS
NLS_SORT
NLS_TERRITORY AMERICA
NLS_TIMESTAMP_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_TIME_FORMAT
NLS_TIME_TZ_FORMAT
SELECT * FROM nls_session_parameters ORDER BY parameter;
gives
PARAMETER VALUE
------------------------- ---------------------------------
NLS_CALENDAR GREGORIAN
NLS_COMP BINARY
NLS_CURRENCY $
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_DUAL_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .,
NLS_SORT BINARY
NLS_TERRITORY AMERICA
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
Why does the NLS_CALENDAR for the DB is showing is Gregorian when queried from SQL*Plus in my machine, because when you query in the DB machine itself, which is in Linux, it shows as Persian?? YES, WE DOUBLE CHECKED THIS. When we login to the Linux DB machine, and go into SQL*Plus and put this query (i.e. SELECT FROM nls_session_paras) we get the NLS_CALENDAR as Persian. Same query in Sql*PLus from my machine in Windows gives it as Gregorian??? If we connect to the DB using Linux Sql*Plus we get the session nls_calendar as Persian, but when we query using Windows SQL*Plus we get as Gregorian.
Why does the NLS_CALENDAR for the SESSION is showing as Gregorian when for the INSTANCE it is Persian?
We did this also: i.e. When we create a env. variable in Windows XP (i.e. right-click My Computer. Go to Advanced --> Env. Variables and add one for the System.) and then close the SQL*Plus and re-login, then the NLS_CALENDAR for the SESSION is set to PERSIAN??
What is happening here?? Why can't we set it in one place (i.e. at DB level) so that from anywhere you query and manipulate dates you will do it in Persian calendar???
Edited by: Channa on Dec 1, 2011 3:22 AM
Edited by: Channa on Dec 1, 2011 4:18 AM