Example 5-17: Sending an email from inside Oracle8i using PL/SQL

CREATE OR REPLACE PROCEDURE sendEmail(smtp_server  VARCHAR2,
                                      from_userid  VARCHAR2,
                                      to_userid    VARCHAR2,
                                      subject      VARCHAR2,
                                      body         VARCHAR2,
                                      from_name    VARCHAR2 := NULL,
                                      to_name      VARCHAR2 := NULL,
                                      content_type VARCHAR2 := NULL)
IS
  c utl_smtp.connection;
  from_domain VARCHAR2(200) := SUBSTR(from_userid,INSTR(from_userid,'@')+1);

  PROCEDURE header(name VARCHAR2, value VARCHAR2) IS
  BEGIN
    utl_smtp.write_data(c, name || ': ' || value || utl_tcp.CRLF);
  END;

BEGIN
  c := utl_smtp.open_connection( smtp_server );
  utl_smtp.helo(c, from_domain );
  utl_smtp.mail(c, from_userid );
  utl_smtp.rcpt(c, to_userid );
  utl_smtp.open_data(c);
  header('From','"'||NVL(from_name,from_userid)||'" <'||from_userid||'>');
  header('To','"'||NVL(to_name,to_userid)||'" <'||to_userid||'>');
  header('Subject', subject );
  header('Content-Type', NVL(content_type,'text/plain'));
  utl_smtp.write_data(c, utl_tcp.CRLF || body );
  utl_smtp.close_data(c);
  utl_smtp.quit(c);
EXCEPTION
  WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
    utl_smtp.quit(c);
    raise_application_error(-20199,'Error sending mail: ' || sqlerrm);

END;