RE: NLS_DATE_FORMAT not effective..help!

  The CREATE TRIGGER ... AFTER LOGON ON DATABASE will work on a session by session basis. As each session logs on to the database it will execute the trigger for that user. I currently use it in my database to do a "alter session set current_schema=?" to that I can point different users to different default schemas (so that the applications were no longer logging into the schema that owned all the objects and the developers didn't have to change any of their code to qualify all the tables, views, and sequences).

Jeffery Stevenson
Chief Databeast Tamer
Medical Present Value, Inc.
Austin, TX

-----Original Message-----
Sent: Wednesday, March 21, 2001 5:41 AM
To: Multiple recipients of list ORACLE-L

Hi,

Thanks for the reply.
But as far as i tried , it is not possible to set NLS_DATE_FORMAT at instance
level (except the entry in init file.)

I think by using LOGON we won't be able to achieve setting NLS_DATE_FORMAT for
the entire instance (all the sessions) . I remember reading LOGON triggers are
always triggered as SYS user.

regards,
Arul.

Martin Kendall wrote:

> Just create a Database on-logon trigger and setup the date as you want it.
> Then providing any "client" does not do an alter session you will have a
> standard DB date format.
>
> -----Original Message-----
> Sent: 20 March 2001 08:36
> To: Multiple recipients of list ORACLE-L
>
> Hi Gurus,
>
> We want to change the default date format of oracle from
> 'dd-mon-yy' to 'MM/DD/YYYY' format for one of our database ( 8.1.6 ).
>
> I have set NLS_DATE_FORMAT as "MM/DD/YYYY" (also tried RRRR for year!)
> in init file. It doesn't seems to have any effect when i query sysdate
> (SQL PLUS) from a client PC (Win 95 / Oracle 8 client / SQL PLUS) .
> Still in SQL PLUS it is displaying the date as 20-MAR-01 ( default
> format - dd-mon-yy)
>
> Ofcourse, after modifying, i bounced the db and listener.
>
> Interesting thing is when i query sysdate from SQLPLUS of my server
> (where Oracle is installed ), it is giving me the date as
>
> SELECT SYSDATE FROM DUAL;
>
> 03/20/200 ( it should be 03/20/2001 )
>
> What went wrong? Did anyone faced similar problem....?
> Do i need to modify registry in client PCs ( Win 9X)...??
>
> Thank You.
> Arul.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Arul kumar
> INET: rakumar_at_mahindrabt.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Martin Kendall
> INET: Martin.Kendall_at_Rubus.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Arul kumar
  INET: rakumar_at_mahindrabt.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jeffery Stevenson
  INET: jeff_at_mpv.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).