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, 'email@example.com'); utl_smtp.rcpt(mail_conn, 'firstname.lastname@example.org'); 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
- Dozens more answers to tough Oracle questions from Eli Leiba are available here.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in December 2002