How Can I Send Email From An Oracle Procedure?

As of Oracle 8.1, you can now send email from within an Oracle application. This is done by using the UTL_SMTP package. However, it should be noted that there are some shortcomings with this package that may or may not be a problem for you:

1. You cannot send a Subject line with your email.
2. You cannot add attachments to your message.
Hopefully, these two deficiencies will be rectified in future releases!

Another gotchya is that you have to include an end-of-line sequence after each line in your message.

In order to simplify sending emails, I created a procedure called (cleverly enough!) "email". It accepts the email recipient, a subject, and a message as arguments. I hardcoded my email address and mail server. Please note that the subject gets included as the first line in the body of the message as a convenience to the recipient.

In order to make this more generic in a production environment, you might also want to add the Sender and Mail Server information as arguments to the procedure.
You might also want to build in the logic to break the message into separate lines with the eol sequence at the end of each line if your messages are going to be larger than one line and your recipient's mail viewer won't wrap a long line correctly.

PROCEDURE email(p_target IN VARCHAR2,
p_subj IN VARCHAR2,
p_message IN VARCHAR2) IS

--PURPOSE: SEND p_message TO p_target VIA EMAIL.

v_eol VARCHAR2(2) := chr(13)||chr(10); -- EOL CHARACTERS
v_sender VARCHAR2(50) := 'YOUR_EMAIL_ADDRESS_HERE';
mailhost VARCHAR2(35) := 'YOUR_MAIL_SERVER_HERE';
mail_connection utl_smtp.connection;

BEGIN
-- ESTABLISH CONNECTION AND PERFORM HANDSHAKING
mail_connection := utl_smtp.open_connection(mailhost,25);
utl_smtp.helo(mail_connection,mailhost);
utl_smtp.mail(mail_connection,v_sender);
utl_smtp.rcpt(mail_connection,p_target);

-- BUILD THE MAIL MESSAGE AND SEND IT OUT
utl_smtp.mail_connection,'Subj:'
||p_subj||v_eol||v_eol||p_message||v_eol);

-- SEVER THE CONNECTION
utl_smtp.quit(mail_connection);

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002,'ERROR IN EMAIL => '||SQLCODE||':
'||SQLERRM);
END;