Swarthmore College

1
Swarthmore College

• Using the UTL_SMTP Email Utility

2
UTL_SMTP allows Emails to be sent via

• Banner Custom Forms
• Table Triggers
• Web Self Service
• Your Own Workflow Processes
• Cron or DBMS_JOBS
• You can send emails formatted as text and/or
  HTML based on MIME type.

3
What is MIME?

• MIME means Multipurpose Internet Mail
  Extensions, and refers to an official Internet
  standard that specifies how messages must be
  formatted so that they can be exchanged between
  different email systems. MIME messages can
  contain text, images, audio, video, or other
  application-specific data. I am going to focus
  on sending text formatted or HTML formatted
  messages using MIME.

4
(No Transcript)
5
(No Transcript)
6

• PROCEDURE send_test_message
• IS
• mailhost VARCHAR2(64)
  'mailhost.fictional-domain.com'
• sender VARCHAR2(64)
  'me_at_fictional-domain.com'
• recipient VARCHAR2(64)
  'you_at_fictional-domain.com'
• mail_conn utl_smtp.connection
• BEGIN
• 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.open_data(mail_conn)
• utl_smtp.write_data(mail_conn, 'This is a
  test message.' chr(13))
• utl_smtp.write_data(mail_conn, 'This is line
  2.' chr(13))
• utl_smtp.close_data(mail_conn)
• utl_smtp.quit(mail_conn)
• EXCEPTION WHEN utl_smtp.transient_error
  OR utl_smtp.permanent_error THEN BEGIN
  utl_smtp.quit(c) EXCEPTION
  WHEN utl_smtp.transient_error OR
  utl_smtp.permanent_error THEN NULL
  -- When the SMTP server is down or unavailable,
  we don't -- have a connection
  to the server. The quit call will
  -- raise an exception that we can ignore.
  END raise_application_error(-2000
  0, 'Failed to send mail due to the
  following error ' sqlerrm)
• END

7
Contanct Information

• Ed Siegle
• esiegle1_at_swarthmore.edu