Re: How to fix NLS_DATE_FORMAT setting?

Keith Sauvant wrote:

> Hi group,
>
> I want Oracle (9i) to use one NLS_DATE_FORMAT setting for all sessions
> on one database. (How) can I achieve that?
>
> My System:
>
> Oracle 9i Server on Windows 2000 Server
> Oracle 9i Client on Windows XP Professional
> CGI-PHP on Apache using OCI8 Library to talk to Oracle
>
> What I tried:
> 1) I made a pfile from my spfile, added the NLS_DATE_FORMAT setting,
> converted it to a spfile, started the instance. I see the correct value
> in my instance configuration but it has no effect on my application
> 2) I tried setting the parameter in my application using "ALTER SESSION"
> command. Works but I do not like to do it this way. I am not quite sure
> when a db "session" ends using CGI-PHP...
>
> Where can the setting be overruled using method 1)? Windows-Registry of
> Server? Windows-Registry of Client? Environment Variables? OCI8 Library
> defaults?
>
> Any help would be appreciated.
> Best regards from Aachen, Germany
> Keith
>

Use ALTER SESSION and set it for each session using an AFTER LOGON trigger. The simplest syntax is:

CREATE OR REPLACE TRIGGER <trigger_name> AFTER LOGON ON DATABASE
CALL <procedure_name>;

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)