PL/SQL UTL_SMTP procedure to send e-mail

I'm trying to use PL/SQL utl_smtp. When I run the procedure, I get a message that says that it was successfully completed. But, when I check to see if I've received the message (I sent it to myself using a different e-mail address), I haven't received it. Can you tell me what's wrong? The following is my code. 
CREATE OR REPLACE PROCEDURE send_mail 
(  sender     IN VARCHAR2, 
    recipient  IN VARCHAR2, 
    subject    IN VARCHAR2, 
    message    IN VARCHAR2) 
IS 
   mailhost     VARCHAR2(30) := 'send.columbia.edu'; 
   mail_conn    utl_smtp.connection; 
 
   crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 ); 
   mesg VARCHAR2( 1000 ); 
BEGIN 
   mail_conn := utl_smtp.open_connection(mailhost, 25); 
 
   mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf || 
          'From:  <'||sender||'>' || crlf || 
          'Subject: '||subject || crlf || 
          'To: '||recipient || crlf || 
          '' || crlf || message; 
   utl_smtp.helo(mail_conn, mailhost); 
   utl_smtp.mail(mail_conn, 'rdk2001@columbia.edu'); 
   utl_smtp.rcpt(mail_conn, 'wkingllc@aol.com'); 
   utl_smtp.data(mail_conn, 'mesg'); 
   utl_smtp.quit(mail_conn); 
END;

    Requires Free Membership to View

    Login

I wrote a very similar procedure that works. Here is my code (you can customize it to your needs): 

CREATE OR REPLACE 
PROCEDURE 
          send_mail (sender    IN VARCHAR2, 
                     recipient IN VARCHAR2, 
                     message   IN VARCHAR2, 
                     nStatus   OUT NUMBER) 
IS 
    mailhost    VARCHAR2(30) := 'email.hai.iec.co.il'; -- host mail addr 
    mail_conn  utl_smtp.connection; 
BEGIN 
    nStatus := 0; 
    mail_conn := utl_smtp.open_connection(mailhost, 25); 
    utl_smtp.helo(mail_conn, mailhost); 
    utl_smtp.mail(mail_conn, sender); 
    utl_smtp.rcpt(mail_conn, recipient); 
    utl_smtp.data(mail_conn, message); 
    utl_smtp.quit(mail_conn); 
EXCEPTION 
    WHEN OTHERS THEN 
        nStatus := SQLCODE; 
END send_mail; 
/

For More Information


This was first published in December 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top