Solved

Issue with date format while sending email via SMTP

Posted on 2007-11-27
6
2,373 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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 125 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example, show how to take different types of Oracle backups using RMAN.
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.

717 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