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 last published in December 2002

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close