Re: nls_date_format confusion, Still Confusing

From: Suresh Bhat <suresh.bhat_at_mitchell-energy.com>
Date: Tue, 06 Jul 1999 20:01:39 GMT
Message-ID: <01bec7fa$fbccb180$a504fa80@mndnet>

Hi Thomas,

We are on SUN UNIX, 7.3.3.0 RDBMS. No clients. Just straight Server. I had set NLS_DATE_FORMAT in initMGSP.ora file to DD-MON-RR about 3 months ago and we stop and start the Oracle database instance every day.

Here are other facts:

NLS_LANG environment variable is not set.

~ 24% echo
~ 25% echo $NLS_LANG
NLS_LANG: Undefined variable.
~ 26%

Here are the initMGSP.ora parameters:

SURESH_at_MGSP>column name format a25
SURESH_at_MGSP>column value format a15
SURESH_at_MGSP>select name,value from v$parameter where upper(name) like

'NLS_%';
__________________ To Quit, Type Ctrl-C __________________

NAME                      VALUE
------------------------- ---------------
nls_language              AMERICAN
nls_territory             AMERICA
nls_sort
nls_date_language
nls_date_format           DD-MON-RR
nls_currency

nls_numeric_characters
nls_iso_currency

8 rows selected.

Here are the NLS_DATE_FORMAT values for other parameters(?):

SURESH_at_MGSP>select value from nls_instance_parameters where parameter='NLS_DATE_FORMAT';
__________________ To Quit, Type Ctrl-C __________________

VALUE

DD-MON-RR 1 row selected.

SURESH_at_MGSP>select value from nls_session_parameters where parameter='NLS_DATE_FORMAT';
__________________ To Quit, Type Ctrl-C __________________

VALUE

DD-MON-RR 1 row selected.

SO FAR EVEVRYTHING LOOKS OK

SURESH_at_MGSP>select value from nls_database_parameters where parameter='NLS_DATE_FORMAT';
__________________ To Quit, Type Ctrl-C __________________

VALUE

DD-MON-YY 1 row selected.

I would have expected NLS_DATE_FORMAT value in NLS_DATABASE_PARAMETERS to be
DD-MON-RR not DD-MON-YY.

Where is DD-MON-YY coming from?

I read about NLS Language Support in Oracle 7 Server Administrator's Guide, Appendix C. It says that there are defaults for date formats etc. depending on the value of NLS_TERRITORY, but can be overridden by setting NLS_DATE_FORMAT in initialization file (which I thought I did in initMGSP.ora).

My conclusion is that I can change this parameter value for an INSTANCE( and consequently for all the sessions for this instance) such as MGSP but the DATABASE parameter value still remains the same as it was at the time of install which I believe is DD-MON-YY.

What are the implications of this if any?

Thanks if you or anyone else can shed some light on this.

Suresh
DBA
Mitchell Energy

Thomas Kyte <tkyte_at_us.oracle.com> wrote in article <378d1425.12681715_at_newshost.us.oracle.com>...

> A copy of this was sent to "DNA" <dna1_at_dnabbott.freeserve.co.uk>
> (if that email address didn't require changing)
> On Mon, 5 Jul 1999 20:57:00 +0100, you wrote:
> 
> >Hi All,
> >I am getting confused about the nls_date_format parameter.  I set it to
> >'DD-MON-YYYY' in the parameter file but when I look at the various nls

views

> >I get the following for this parameter :
> >nls_session_parameters : DD-MON-YY
> >nls_instance_parameters : DD-MON-YYYY
> >nls_database_parameters : DD-MON-YY
> >v$parameter : DD-MON-YYYY
> >I can understand the instance and parameter views but surely the session
> >parameters should pick up the value when it connects to the database?

Or am

> >I missing something?
> >The database is 7.3.4 on NT4.
> >
> >Thanks!
> >
> 
> 
> If the client sets the NLS_* parameters -- they override the server in

all
> cases. In fact, if the client sets the NLS_LANG parameter -- that causes all
> NLS_* settings on the server to be ignored and the defaults for that NLS_LANG

> specified on the client on used.
> 
> Where that comes into play is typically if the client is windows and the

server
> is unix. The client install on windows set the NLS_LANG parameter in the > registry by default. This setting is different from the default you find for
> unix. Any NLS_* settings you put in the init.ora will *not* be used by those
> clients. (happens if the client is on NT/Windows period -- doesn't matter where

> teh database is)
> 
> You have to set the NLS_DATE_FORMAT in the registry on the client
> 
> or
> 
> you need to put an "alter session set nls_date_format='yyyymmdd'" in your

apps

> right after the connect
> 
> or
> 
> with Oracle8i, release 8.1 you can code:
> 
> 
> create or replace trigger data_logon_trigger
> after logon
> ON DATABASE
> begin
>     execute immediate
>         'alter session set nls_date_format = ''yyyymmdd'' ';
> end;
> /
> 
> as well.
> 
> 
> -- 
> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to

Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June 21'st

>  
> Thomas Kyte                   tkyte_at_us.oracle.com
> Oracle Service Industries     Reston, VA   USA
> 
> Opinions are mine and do not necessarily reflect those of Oracle

Corporation
> Received on Tue Jul 06 1999 - 15:01:39 CDT