Re: setting NLS_DATE_FORMAT globally rather than per session?

sybrandb_at_hccnet.nl wrote:
>>I've received a request for NLS_DATE_FORMAT to be set globally in a
>>database to "Mon dd yyyy hh:mi:ssAM" so that ad hoc queries that
>>use the time don't have to use the to_date function.
>>
>>Is this a reasonable or common thing to do? We're in a single
>>timezone in a single country, so there are no localization
>>issues to worry about.

> 
> It is a thing that won't work, as NLS setting are always overridden by
> the client. If the client didn't set them they are overridden to the
> defaults of the particular Oracle port.

To go into more detail:

See http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14225/ch3globenv.htm#g1012703

If no environment variable/registry setting is set on the client, the setting will be DD-MON-RR.

If NLS_LANG is set and NLS_DATE_FORMAT is not set, the format will be derived from the territory part of NLS_LANG.

If NLS_DATE_FORMAT is set, this will be respected.

Additionally, you can use ALTER SESSION to set NLS_TERRITORY or NLS_DATE_FORMAT for the session.

Here is a (slightly ugly) solution for the problem:

You can create a login trigger for the user that will change the setting of NLS_DATE_FORMAT for the session.

Yours,
Laurenz Albe Received on Fri Jun 22 2007 - 06:33:40 CDT