Re: Default Date formatting with NLS_DATE_FORMAT

From: Gary M. Jablonski <gary.jablonski_at_nospam.com>
Date: Fri, 19 Feb 1999 08:25:31 -0500
Message-ID: <36CD664B.CB9EE539@nospam.com>

As far as I know, the NLS_DATE_FORMAT is a session setting.
To make it apply to all sessions, look into the concept of
glogin.sql.  That's sort of a profile exectuted for all sessions.
When placed in the appropriate ORACLE system directory
(usually in  $ORACLE_HOME/product/7.3.4/sqlplus/admin)
it can be applied to all users.

The SQL*Plus command may be known by a different name under some operating
systems, for example, plus33. See your SQL*Plus installation documentation for
further information on your specific operating system.

SQL*Plus supports a Site Profile, a SQL*Plus command file created by the database
administrator. This file is generally named GLOGIN with an extension of SQL.
SQL*Plus executes this command file whenever any user starts SQL*Plus and
SQL*Plus establishes the Oracle connection. The Site Profile allows the DBA to set
up SQL*Plus environment defaults for all users at a particular site; users cannot
directly access the Site Profile. The default name and location of the Site Profile
depend on your system. Site Profiles are described in more detail in the Oracle
installation and user's manual(s) provided for your operating system.

SQL*Plus also supports a User Profile, executed after the Site Profile. SQL*Plus
searches for a filenamed LOGIN with the extension SQL in your current directory. If
SQL*Plus does not find the file there, SQL*Plus will search a system-dependent
path to find the file. Some operating systems may not support this path search.

If you fail to log in successfully to SQL*Plus because your username or password is
invalid or some other error, SQL*Plus will return an error status equivalent to an
EXIT FAILURE command. See the EXIT command in this chapter for further
information.

See p.132  ORACLE8 The complete reference
Koch & Loney, ORACLE press.

Gary Jablonski
mailto:gary_jablonski*AT*webmail.bellsouth.net
  -------replace *AT* with @

Ilias Santouridis wrote:

Hi all.

We want to change our database's default date formatting.
The current format is "DD-MON-YY", which I guess results from
the value of NLS_TERRITORY which is "AMERICA".

I want to change the date format to "YYYY/MM/DD".

I've tried to change the value of NLS_DATE_FORMAT parameter.
When I execute :

 alter session set NLS_DATE_FORMAT = "YYYY/MM/DD"

the format changes for the current session.

However, I want to make this format the default one for all sessions.
The manual suggests to make an entry in the init.ora file
for the NLS_DATE_FORMAT parameter. I included the following entry in
our database's init.ora file :

nls_date_format="YYYY/MM/DD"

The database was then shutted down and restarted. However, the default
date format didn't change and remained "DD-MON-YY".
Any explanations ?

I would like to thank you in advance.
Ilias

      Received on Fri Feb 19 1999 - 07:25:31 CST