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.