Here is a stored procedure to send an email message using Oracle's utl_smtp package. I tested this on Oracle 8.1.7...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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','email@example.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 firstname.lastname@example.org 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.