Re: How to set Date format in UTL_SMTP

On Mar 21, 10:36 am, "aru" <arumugam..._at_gmail.com> wrote:
> On Mar 21, 2:03 am, "Vladimir M. Zakharychev"
>
>
>
> <vladimir.zakharyc..._at_gmail.com> wrote:
> > On Mar 20, 7:46 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>
> > > "aru" <arumugam..._at_gmail.com> a écrit dans le message de news: 1174389921.659772.72..._at_b75g2000hsg.googlegroups.com...
> > > | Hi,
> > > | I have one strange problem with UTL_SMTP.
> > > |
> > > | I am using the following code to set the date value and format in
> > > | header buffer:
> > > |
> > > | header:= 'Date: '||TO_CHAR(SYSDATE,'fmDy, DD Mon YYYY fxHH24:MI:SS')||
> > > | crlf||
> > > | 'From: '||sender||''||crlf||
> > > | 'Subject: '||subject||crlf||
> > > | 'To: '||recipient||crlf||
> > > | 'CC: '||ccrecipient ;
> > > |
> > > | With this method, the mails sent from database carry SYSDATE of the
> > > | machine where the Oracle Database is running. In my case, database is
> > > | running in India.
> > > |
> > > | People in US complain that they get future-dated mail. Since the
> > > | database is running in India and there is time difference of 12 hr
> > > | 30mins between the regions, this is expected. But when I send mail
> > > | using Java Mail API, I did not have this kind of issue. Anything can
> > > | be done for this???
> > > |
> > > | Help from somebody is highly appreciated.
> > > |
> > > | Thanks,
> > > | Ary
> > > |
>
> > > Use:
>
> > > to_char(systimestamp, 'Dy, dd Mon yy hh24:mi:ss tzhtzm')
>
> > > Then there is the time zone in your date.
>
> > > Regards
> > > Michel Cadot
>
> > To be on the completely safe side:
>
> > to_char(systimestamp, 'Dy, dd Mon yyyy hh24:mi:ss
> > tzhtzm','NLS_DATE_LANGUAGE=American')
>
> > so that returned weekday and month names are in American/English
> > regardless the NLS settings of the database and/or session. Not every
> > Oracle db in the world runs in English... :) And to be completely
> > compatible with RFC-2822, 4-digit year should be used.
>
> > Regards,
> > Vladimir M. Zakharychev
> > N-Networks, makers of Dynamic PSP(tm)
> > http://www.dynamicpsp.com
>
> Thanks for posting your responses...I will try this.
> -aru

Hi All,
I found a solution for this..
Changing the "header" details to:

header:=

     'From: '||sender||''||crlf||
  'Subject: '||subject||crlf||
       'To: '||recipient||crlf||
       'CC: '||ccrecipient ;

i.e. remove the 'Date: '||TO_CHAR(SYSDATE,'fmDy, DD Mon YYYY fxHH24:MI:SS')||crlf||'.

This solved the problem of seeing future-dated messages.

I verified the content of the mail after making this change with the mail sent thru JavaMail API, they looked similar. I guess that specifying "Date" in header field forces the mail server to use the user specified data. Any thoughts??

Thanks,
Arumugam Received on Thu Mar 22 2007 - 06:48:02 CDT