Re: NLS_DATE_FORMAT
Eric Richmond, hi, try to set it in regedit, or click my computer-property-advanced-enviroment variable-add nls_date_format. Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] www.happyit.net === 2002-09-09 11:18:00 ,you wrote£º=== On NT/2000, how can you set the NLS date format at the session level? Isn't there a file that you can set it in? What do you write in the file? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric Richmon 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: zhu chao 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).
RE: NLS_DATE_FORMAT
To set it at the session level, You need, Alter session set nls_date_format='DD-MON- HH24:MI'; You can give any valid format string. If you want this to happen automatically in your sqlplus session you can add this to your glogin.sql script. Hope this helps. Viral. From: Farnsworth, Dave [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: NLS_DATE_FORMAT Date: Mon, 09 Sep 2002 11:48:24 -0800 Set it in the SIDinit.ora. Dave -Original Message- Sent: Monday, September 09, 2002 2:18 PM To: Multiple recipients of list ORACLE-L On NT/2000, how can you set the NLS date format at the session level? Isn't there a file that you can set it in? What do you write in the file? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric Richmon 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: Farnsworth, Dave 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). _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viral Desai 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).
RE: NLS_DATE_FORMAT
Set it in the SIDinit.ora. Dave -Original Message- Sent: Monday, September 09, 2002 2:18 PM To: Multiple recipients of list ORACLE-L On NT/2000, how can you set the NLS date format at the session level? Isn't there a file that you can set it in? What do you write in the file? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric Richmon 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: Farnsworth, Dave 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).
Re: nls_date_format problem in sqlldr
Ruth Gramolini wrote: Good morning, We are having a problem load a file containing dates in MON DD format into a database having and nls_date_format=MMDD. Is there a way to do an alter session command in the control file or the parameter file to that we can load this data? We have had the production database down for 4 days with various problems and this is the last straw. Thanks in advance, Ruth You can specify in the SQL*LOADER control file the date mask with date columns. I know that syntactic schemas are not extremely explicit, but have a harder look at field descriptions in the manual. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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).
RE: nls_date_format problem in sqlldr
Ruth: Are you using a delimited flat file via SQL Loader? Would the to_date function do the trick? My 0.03 Euro's worth, Ken -Original Message- Sent: Thursday, January 10, 2002 7:25 AM To: Multiple recipients of list ORACLE-L Subject:nls_date_format problem in sqlldr Good morning, We are having a problem load a file containing dates in MON DD format into a database having and nls_date_format=MMDD. Is there a way to do an alter session command in the control file or the parameter file to that we can load this data? We have had the production database down for 4 days with various problems and this is the last straw. Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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: Ken Janusz 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).
RE: nls_date_format problem in sqlldr
Hello Ruth The answer is not to change the nls_date_format but to tell the loader the format you use for your date. We use the following in our ctl files: xx_xx_TAR_NECHONUT date(8) 'MMDD', maybe you can use: my_date date(8) 'MMMDD' . (I did not check if it works). Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] -Original Message- From: Ruth Gramolini [SMTP:[EMAIL PROTECTED]] Sent: Thu, January 10, 2002 3:25 PM To: Multiple recipients of list ORACLE-L Subject: nls_date_format problem in sqlldr Good morning, We are having a problem load a file containing dates in MON DD format into a database having and nls_date_format=MMDD. Is there a way to do an alter session command in the control file or the parameter file to that we can load this data? We have had the production database down for 4 days with various problems and this is the last straw. Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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). -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This e-mail was scanned by the eSafe Mail Gateway -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= 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).
Re: nls_date_format problem in sqlldr
Ruth, If the source file is being loaded with SQL LOADER that you can format the date load in the controlfile. We get our dates in the MMDD format and load in to the database default by defining the source data: SaleDate TERMINATED BY "," "TO_DATE(:SALEDATE,'MMDD')", ...Good Luck. Ron ROR mª¿ªm [EMAIL PROTECTED] 01/10/02 08:25AM Good morning,We are having a problem load a file containing dates in MON DD formatinto a database having and nls_date_format=MMDD. Is there a way to doan alter session command in the control file or the parameter file to thatwe can load this data?We have had the production database down for 4 days with various problemsand this is the last straw.Thanks in advance,Ruth-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Ruth Gramolini INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: nls_date_format problem in sqlldr
We got around this issue but if anyone knows whether this is possible I would like to know for future reference. Thanks anyway, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 10, 2002 8:25 AM Good morning, We are having a problem load a file containing dates in MON DD format into a database having and nls_date_format=MMDD. Is there a way to do an alter session command in the control file or the parameter file to that we can load this data? We have had the production database down for 4 days with various problems and this is the last straw. Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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: Ruth Gramolini 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).
Re: nls_date_format problem in sqlldr
Just set the environment variable before running sqlldr: export NLS_DATE_FORMAT='MON DD ' Ruth Gramolini [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ate.vt.us cc: Sent by: Subject: nls_date_format problem in sqlldr [EMAIL PROTECTED] 01/10/02 05:25 AM Please respond to ORACLE-L Good morning, We are having a problem load a file containing dates in MON DD format into a database having and nls_date_format=MMDD. Is there a way to do an alter session command in the control file or the parameter file to that we can load this data? We have had the production database down for 4 days with various problems and this is the last straw. Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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: 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).
Re: nls_date_format in 8.1.6
Oracle DBA wrote:
we have set nls_date_format to dd-mm- in parameter file .
but all the funtions which have some hard coded date value in the format of
dd-mm-
are giving not a valid month error
example is
create or replace function test_bkj(t_date date)
return varchar2
is
begin
if (t_date '12-12-2001')
then
t_retval := 'Less Than';
else
t_retval := 'Greater Than';
end if;
return t_retval;
end;
SQL select test_bkj('12-12-2001') from dual;
select test_bkj('12-12-2001') from dual
*
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at AVLMKT.TEST_BKJ, line 6
ORA-06512: at line 1
when we change the date format in funtion to 'dd/MON/' keeping the
nls_date_format to 'dd-mm-'
we are getting wrong results from function .
create or replace function test_bkj(t_date date)
return varchar2
is
begin
if (t_date '12-DEC-2001')
then
t_retval := 'Less Than';
else
t_retval := 'Greater Than';
end if;
return t_retval;
end;
SQL select test_bkj('31-12-2001') from dual;
TEST_BKJ('31/12/2001')
Less Than
while the results should have been Greater than . (pl see the
if-else condition )
its giving Less Than for any date comparison.
Pl help .
Thanks in advance
Brajesh Jaiswal
Hmmm ... My feeling is that the problem you have is linked to the
comparisons INSIDE the function, not the way the argument is passed.
Your second example shows rather clearly that an implicit TO_CHAR() is
applied to your argument - and not an implicit TO_DATE() to the
constant. Avoid implicit conversions. Let me also say that I find
extremely dangerous to rely on init.ora settings (or in fact anything
since people can change the format at will through ALTER SESSION). Since
you pass a date to your function (which is as it should be) you should
use explicit conversions in your function and compare the date passed as
argument to TO_DATE('12-12-2001', 'dd-mm-') or whatever. Ready to
bet that your problems will vanish.
--
Regards,
Stephane Faroult
Oriole Ltd
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
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).
Re: nls_date_format in 8.1.6
Hmm, try to replace - if (t_date '12-12-2001')
with - if (t_date to_date('12-12-2001','dd-mm-'))
Jan Pruner
On Sat 5. January 2002 13:35, you wrote:
we have set nls_date_format to dd-mm- in parameter file .
but all the funtions which have some hard coded date value in the format of
dd-mm-
are giving not a valid month error
example is
create or replace function test_bkj(t_date date)
return varchar2
is
begin
if (t_date '12-12-2001')
then
t_retval := 'Less Than';
else
t_retval := 'Greater Than';
end if;
return t_retval;
end;
SQL select test_bkj('12-12-2001') from dual;
select test_bkj('12-12-2001') from dual
*
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at AVLMKT.TEST_BKJ, line 6
ORA-06512: at line 1
when we change the date format in funtion to 'dd/MON/' keeping the
nls_date_format to 'dd-mm-'
we are getting wrong results from function .
create or replace function test_bkj(t_date date)
return varchar2
is
begin
if (t_date '12-DEC-2001')
then
t_retval := 'Less Than';
else
t_retval := 'Greater Than';
end if;
return t_retval;
end;
SQL select test_bkj('31-12-2001') from dual;
TEST_BKJ('31/12/2001')
---
-
Less Than
while the results should have been Greater than . (pl see the
if-else condition )
its giving Less Than for any date comparison.
Pl help .
Thanks in advance
Brajesh Jaiswal
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jan Pruner
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).
Re: nls_date_format in 8.1.6
Have a talk with your developers.
Implicit conversions of any kind should not be
allowed in production code. I think you know why, now.
Jared
On Saturday 05 January 2002 04:35, Oracle DBA wrote:
we have set nls_date_format to dd-mm- in parameter file .
but all the funtions which have some hard coded date value in the format of
dd-mm-
are giving not a valid month error
example is
create or replace function test_bkj(t_date date)
return varchar2
is
begin
if (t_date '12-12-2001')
then
t_retval := 'Less Than';
else
t_retval := 'Greater Than';
end if;
return t_retval;
end;
SQL select test_bkj('12-12-2001') from dual;
select test_bkj('12-12-2001') from dual
*
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at AVLMKT.TEST_BKJ, line 6
ORA-06512: at line 1
when we change the date format in funtion to 'dd/MON/' keeping the
nls_date_format to 'dd-mm-'
we are getting wrong results from function .
create or replace function test_bkj(t_date date)
return varchar2
is
begin
if (t_date '12-DEC-2001')
then
t_retval := 'Less Than';
else
t_retval := 'Greater Than';
end if;
return t_retval;
end;
SQL select test_bkj('31-12-2001') from dual;
TEST_BKJ('31/12/2001')
---
-
Less Than
while the results should have been Greater than . (pl see the
if-else condition )
its giving Less Than for any date comparison.
Pl help .
Thanks in advance
Brajesh Jaiswal
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
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).
Re: NLS_DATE_FORMAT not effective..help!
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/' format for one of our database ( 8.1.6 ). I have set NLS_DATE_FORMAT as "MM/DD/" (also tried 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).
RE: NLS_DATE_FORMAT not effective..help!
Try: Alter session set nls_date_format=date_mask; HTH Jorma 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/' format for one of our database ( 8.1.6 ). I have set NLS_DATE_FORMAT as "MM/DD/" (also tried 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: 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).
Re: NLS_DATE_FORMAT not effective..help!
You can set if for the instance or even for the session. RBG - Original Message - To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Wednesday, March 21, 2001 6:35 AM 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/' format for one of our database ( 8.1.6 ). I have set NLS_DATE_FORMAT as "MM/DD/" (also tried 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: Ruth Gramolini 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).
RE: NLS_DATE_FORMAT not effective..help!
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/''', 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/' format for one of our database ( 8.1.6 ). I have set NLS_DATE_FORMAT as "MM/DD/" (also tried 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).
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/' format for one of our database ( 8.1.6 ). I have set NLS_DATE_FORMAT as "MM/DD/" (also tried 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: Jeffery Stevenson 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).
Re: NLS_DATE_FORMAT not effective..help!
Yea, to see it on a clint's SQL*Plus You have to modify \Hkey_local_machine\software\oracle\NLS_DATE_FORMAT At least this is on NT, I don't know about 95, 98 Gints Plivna Arul kumar rakumar@mahinTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] drabt.comcc: Sent by: Subject: NLS_DATE_FORMAT not effective..help! [EMAIL PROTECTED] om 01.03.20 10:36 Please respond to ORACLE-L Hi Gurus, We want to change the default date format of oracle from 'dd-mon-yy' to 'MM/DD/' format for one of our database ( 8.1.6 ). I have set NLS_DATE_FORMAT as "MM/DD/" (also tried 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: 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).
RE: NLS_DATE_FORMAT not effective..help!
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/' format for one of our database ( 8.1.6 ). I have set NLS_DATE_FORMAT as "MM/DD/" (also tried 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).
RE: NLS_DATE_FORMAT not effective..help!
Arul In init.ora have you specified MM/DD/ WITHOUT Quotes , as follows simply :- NLS_DATE_FORMAT=MM/DD/ -Original Message- From: Martin Kendall [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, March 20, 2001 6:36 PM To: Multiple recipients of list ORACLE-L Subject: RE: NLS_DATE_FORMAT not effective..help! 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/' format for one of our database ( 8.1.6 ). I have set NLS_DATE_FORMAT as "MM/DD/" (also tried 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: VIVEK_SHARMA 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).
