NLS_DATE_FORMAT problem

From: Robert Chung <robertch_at_mindspring.com>
Date: Fri, 24 Sep 1999 00:36:15 GMT
Message-ID: <37eac6ff.649548781@news.mindspring.com>

I am having problem resetting NLS_DATE_FORMAT parameter on my Oracle database running in HP/UX box. I changed it to nls_date_format ="DD-MON-YYYY" in initSID.ora and restarted database, but "select sysdate from dual" is showing only two-digits for year. sqlplus login.sql or glogin.sql do not have anything that changes NLS_DATE_FORMAT. According to output below (from nls_instance_parameters and nls_session_parameters), I can see database instance did accept new NLS_DATE_FORMAT, but apparently something is overwriting it whenever I start sqlplus session. What would that be? Someone please let me know where to look. By the way, alter session set nls_date_format= "DD-MON-YYYY" inside sqlplus always works. Thank you.

<<<<< UNIX ENVIRONMENT SETTING >>>>>
NLS_LANG=American_America.WE8ISO8859P1
ORA_NLS32=/opt/oracle/oracle804/ocommon/nls/admin/data

<<<<< OUTPUT FROM SQLPLUS >>>>>
21:51:22 SQL> select * from props$;

NAME

---

VALUE$

---

---

COMMENT$

---

---

DICT.BASE
2
dictionary base tables version #

NLS_LANGUAGE
AMERICAN
Language

NLS_TERRITORY
AMERICA
Territory

NLS_CURRENCY
$
Local currency

NLS_ISO_CURRENCY
AMERICA
ISO currency

NLS_NUMERIC_CHARACTERS
.,
Numeric characters

NLS_CALENDAR
GREGORIAN
Calendar system

NLS_DATE_FORMAT
DD-MON-YY
Date format

NLS_DATE_LANGUAGE
AMERICAN
Date language

NLS_CHARACTERSET
WE8ISO8859P1
Character set

NLS_SORT
BINARY
Linguistic definition

NLS_NCHAR_CHARACTERSET
WE8ISO8859P1
NCHAR Character set

NLS_RDBMS_VERSION
8.0.4.2.1
RDBMS version for NLS parameters

GLOBAL_DB_NAME
ODYPLAN.WORLD
Global database name

EXPORT_VIEWS_VERSION
7
Export views revision #

15 rows selected.

Elapsed: 00:00:00.01
21:51:22 SQL> select * from nls_session_parameters;

PARAMETER                      VALUE


------------------------------ ------------------------------


NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-YY
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY

9 rows selected.

Elapsed: 00:00:00.01
21:51:22 SQL> select * from nls_instance_parameters;

PARAMETER                      VALUE


------------------------------ ------------------------------


NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_SORT                       BINARY
NLS_DATE_LANGUAGE
NLS_DATE_FORMAT                DD-MON-YYYY
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS         .,

NLS_ISO_CURRENCY
NLS_CALENDAR 9 rows selected.

Elapsed: 00:00:00.01
21:51:22 SQL> select * from nls_database_parameters;

PARAMETER                      VALUE


------------------------------ ------------------------------


NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-YY
NLS_DATE_LANGUAGE              AMERICAN
NLS_CHARACTERSET               WE8ISO8859P1
NLS_SORT                       BINARY
NLS_NCHAR_CHARACTERSET         WE8ISO8859P1
NLS_RDBMS_VERSION              8.0.4.2.1

12 rows selected.

Elapsed: 00:00:00.00
21:51:22 SQL> select sysdate from dual;

SYSDATE

---

23-SEP-99 Elapsed: 00:00:00.00 Received on Thu Sep 23 1999 - 19:36:15 CDT