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','firstname.lastname@example.org','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
- email@example.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