Oracle

email by UTL_SMTP wrong send date

Most of the DBAs have scripts that send emails. Sometimes the send time for those emails do not show the correct time. When this happens you start to suspect the time zone of the DB host or the email server or the client email program. We had such issue before and I found a support document that helped me a lot.

The solution of the problem is explicitly defining the timezone information in the PL/SQL package that is used to send emails.

Mail Sent Using UTL_SMTP Not Displaying Correct SENT DATE (Doc ID 468323.1)

Wrong Date
The send time is off by one hour from the local time.

  
   message_header:= 'Date: ' || TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss') || crlf ||

Time zone added
The send time is set correctly. But When the environment variable NLS_DATE_LANGUAGE is TURKISH we had problems in the month part of the date. i.e schedlued jobs.

   message_header:= 'Date: ' || TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss') || ' +0200'  ||   crlf ||

Time zone added Month fixed
Now the send date is corrected in the jobs also.

  message_header := 'Date: ' || to_char(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), 'Europe/Istanbul'),'dd Mon yy hh24:mi:ss TZHTZM','NLS_DATE_LANGUAGE=AMERICAN')  || crlf ||
Advertisements

One thought on “email by UTL_SMTP wrong send date

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s