×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Oracle: All versions FAQ

Oracle Supplied Packages

How Can I Send Email From An Oracle Procedure? by carp
Posted: 21 Sep 00

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;

Back to Oracle: All versions FAQ Index
Back to Oracle: All versions Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close