Oh yes you can. Here is the script I run as SYSTEM and it provides a common
date format for
all logins - UNLESS they override it with a SUBSEQUENT Alter Session or it
is defined in their REGISTRY
(Windows users).
Instead of ON DATABASE, you can also limit it to a particular Schema.
Otherwise, I am really sorry if I have completely misunderstood the problem
:-)
CREATE OR REPLACE TRIGGER on_logon
AFTER LOGON
ON DATABASE
declare
v_cur integer;
v_cur2 integer;
begin
v_cur := dbms_sql.open_cursor;
dbms_sql.parse(v_cur,'alter session set
nls_date_format=''MM/DD/YYYY''',
dbms_sql.native);
v_cur2 := dbms_sql.execute(v_cur);
end;
/
-----Original Message-----
Sent: 21 March 2001 11:35
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
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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
