Q

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; 

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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close