Sending Email

Sending Emails from the Oracle® Database and PL/SQL is performed usually with the UTL_SMTP package.

Note: As of version 1.3.7 sending S/MIME signed emails is easier with ORA_SMIME_APEX_MAIL

This API will remain as legacy email sending API

A flaw of UTL_SMTP is that it is a very low-level tool and PL/SQL developers must construct the email body from scratch. This tutorial chapter illustrates basic usage of UTL_SMTP so you can prepare for the next two chapters where we will demonstrate how to send Signed and Encrypted S/MIME emails.

Preparing for using UTL_SMTP

The first step that we have to perform in order to send emails from PL/SQL inside the Oracle Database is to allow external network access to the user (schema) that will invoke UTL_SMTP ( see “Managing Fine-grained Access to External Network Services” in Oracle Database Security Guide)

Below is a sample set of grants that allows the user ‘USER’ to send emails by connecting every host on port 25 (default SMPT port):

BEGIN
--- creating Acess Control List
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
  acl => 'send_mails.xml', -- our made up name of the ACL
 description => 'Allow mail to be send', 
 principal => 'USER', 
 is_grant => TRUE, 
 privilege => 'connect');
COMMIT;
 
----Grant the connect and resource privilege as below
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'send_mails.xml', principal => 'USER', is_grant => TRUE, privilege => 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'send_mails.xml',principal => 'USER',is_grant => TRUE, privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL( acl => 'send_mails.xml', host => '*');
END;
 
COMMIT;

A simple email with UTL_SMTP

Sending a simple Hello world email with UTL_SMTP requires some low-level email structure knowledge. The example below illustrates this:

CREATE OR REPLACE PROCEDURE SEND_MAIL (msg_to VARCHAR2, 
                                       msg_subject VARCHAR2,
                                       msg_text VARCHAR2 )
IS
  c UTL_SMTP.connection;
  rc INTEGER; 
  msg_from VARCHAR2(50) := 'Oracle12'; 
  mailhost VARCHAR2(30) := '127.0.0.1'; -- local database host 
BEGIN
  c := UTL_SMTP.open_connection(mailhost, 25); -- SMTP on port 25 
  UTL_SMTP.helo(c, mailhost);
  UTL_SMTP.mail(c, msg_from);
  UTL_SMTP.rcpt(c, msg_to);
 
  UTL_SMTP.open_data(c)
  UTL_SMTP.write_data(c,'From: Oracle Database' || UTL_TCP.crlf);
  UTL_SMTP.write_data(c,'To: ' || msg_to || UTL_TCP.crlf);
  UTL_SMTP.write_data(c,'Subject: ' || msg_subject);
 
  UTL_SMTP.write_data(c, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
  UTL_SMTP.write_data(c, msg_text);
  UTL_SMTP.write_data(c, UTL_TCP.crlf || UTL_TCP.crlf);
 
  UTL_SMTP.close_data(c);
  UTL_SMTP.quit(c);
END;
/

Summary

This chapter is a starting point for using DidiSoft ORA_SMIME for sending S/MIME emails using PL/SQL from Oracle® Database.

From here you can continue to the chapters that illustrate how to send Signed S/MIME and Encrypted S/MIME emails constructed with PL/SQL.