[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Issue with date format while sending email via SMTP

Posted on 2007-11-27
6
Medium Priority
?
2,470 Views
Last Modified: 2013-12-18
I have issue to format date using Oracle SMTP package. I tried several date formats, but none is working.
When MS email is opened the date format in "Sent:" always shows the incorrect date/time:

Following is the example:

CREATE OR REPLACE PROCEDURE send_email (
   subjecttext     IN       VARCHAR2,
   email_address   IN       VARCHAR2,
   message_body    IN       LONG
)
IS
   sendoraddress   VARCHAR2 (30)       := ' sender@email.com';
   emailserver     VARCHAR2 (30)       := 'smtp.address.com';
   port            NUMBER              := 25;
   conn            UTL_SMTP.connection;
   mesg_header     VARCHAR2 (4000);
   subject         VARCHAR2 (200);
   crlf            VARCHAR2 (2)        := CHR (13) || CHR (10);
BEGIN
   subject := subjecttext;
   conn := UTL_SMTP.open_connection (emailserver, port);
   UTL_SMTP.helo (conn, emailserver);
   UTL_SMTP.mail (conn, sendoraddress);
   UTL_SMTP.rcpt (conn, email_address);

   mesg_header :=
            'Date: '
         || TO_CHAR (SYSDATE, 'MM DD YYYY HH:MI:SS')
         || crlf
         || 'From:'
         || sendoraddress
         || crlf
         || 'Subject:'
         || subject
         || crlf
         || 'To: '
         || email_address
         || crlf
         || ''
         || crlf;
   END IF;

   UTL_SMTP.DATA (conn, mesg_header || crlf || crlf || message_body);
   UTL_SMTP.quit (conn);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (   'ERROR SQLCODE '
                            || SQLCODE
                            || ' '
                            || SUBSTR (SQLERRM, 1, 200)
                           );
END;
/
0
Comment
Question by:tribuna33
6 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 20361772
what are you expecting and what are you getting?
0
 
LVL 1

Expert Comment

by:michaeljoneill
ID: 20362095
Please define "not working" more precisely.  

Regardless, I would try HH24 instead of HH in your cast: TO_CHAR (SYSDATE, 'MM DD YYYY HH24:MI:SS') , and I would use utl_tcp.crlf instead of your own private variable crlf.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20362723
according to RFC 822,  your date/time format must be ....


'dd Mon yy hh24:mi'

I derived this from section 5.1 of the rfc



date-time   =  [ day "," ] date time        ; dd mm yy
                                                 ;  hh:mm:ss zzz

     day         =  "Mon"  / "Tue" /  "Wed"  / "Thu"
                 /  "Fri"  / "Sat" /  "Sun"

     date        =  1*2DIGIT month 2DIGIT        ; day month year
                                                 ;  e.g. 20 Jun 82

     month       =  "Jan"  /  "Feb" /  "Mar"  /  "Apr"
                 /  "May"  /  "Jun" /  "Jul"  /  "Aug"
                 /  "Sep"  /  "Oct" /  "Nov"  /  "Dec"

     time        =  hour zone                    ; ANSI and Military

     hour        =  2DIGIT ":" 2DIGIT [":" 2DIGIT]
                                                 ; 00:00:00 - 23:59:59

     zone        =  "UT"  / "GMT"                ; Universal Time
                                                 ; North American : UT
                 /  "EST" / "EDT"                ;  Eastern:  - 5/ - 4
                 /  "CST" / "CDT"                ;  Central:  - 6/ - 5
                 /  "MST" / "MDT"                ;  Mountain: - 7/ - 6
                 /  "PST" / "PDT"                ;  Pacific:  - 8/ - 7
                 /  1ALPHA                       ; Military: Z = UT;
                                                 ;  A:-1; (J not used)
                                                 ;  M:-12; N:+1; Y:+12
                 / ( ("+" / "-") 4DIGIT )        ; Local differential
                                                 ;  hours+min. (HHMM)


0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20363291
where is your email server hosted ? it is in the same location where you are running the code from or not ?

May be the mail server is using a different time zone to display the date/time in emails.

Thanks
0
 

Author Comment

by:tribuna33
ID: 20367893
I found the solution - I commented date format and it works.
thanks to all for your help.
 
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 375 total points
ID: 20368009
of course commenting to remove it completely works, all of the header fields are optional but you should be able to format and use whatever date you want too.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month20 days, 4 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question