A stored procedure to send email

A stored procedure to send an email message using the Oracle utl_smtp package.

Here is a stored procedure to send an email message using Oracle's utl_smtp package. I tested this on Oracle 8.1.7 running on HP UX 10.20 system, with the Exchange server is installed on an NT 4 SP6 machine.

                
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'; 
    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; 

If the mail host name is verified as OK, you use the stored procedure by running this (in SQL*Plus, for example):

var s number
exec send_mail ('OracleServer@hotmail.com','tlinde@amfam.com','test text msg',:s);

The recipient should be a valid email, the sender can be a fictitious Oracle server email.

For More Information

  • What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was first published in January 2002

Pro+

Features

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

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