Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: