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 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
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';
-- ESTABLISH CONNECTION AND PERFORM HANDSHAKING
mail_connection := utl_smtp.open_connection(mailhost,25);
-- BUILD THE MAIL MESSAGE AND SEND IT OUT
-- SEVER THE CONNECTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002,'ERROR IN EMAIL => '||SQLCODE||':