Re: nls_date_format confusion

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 Mon Jul 05 1999 - 15:23:42 CDT