Send Email on Autonomous Database on Dedicated Exadata Infrastructure
You can send email on Autonomous Database on Dedicated Exadata Infrastructure using the Email Delivery Service or the
DBMS_CLOUD_NOTIFICATION procedures. This article describes how to send email using both these options.- Send Email with Email Delivery Service on Autonomous Database
Describes the steps to send email usingUTL_SMTPon Autonomous Database on Dedicated Exadata Infrastructure. - SMTP Send Email Sample Code
Shows sample code for sending email withUTL_SMTPon an Autonomous Database. - Send Messages as Email from Autonomous Database
APPLIES TO:
Oracle Public Cloud only - Send Query Results as Email from Autonomous Database
APPLIES TO: Oracle Public Cloud only
Parent topic: Send Email and Notifications from Autonomous Database
Send Email with Email Delivery Service on Autonomous Database
Describes the steps to send email using UTL_SMTP on Autonomous Database on Dedicated Exadata Infrastructure.
To send email using
UTL_SMTP:
See UTL_SMTP for further reference.
See Notes on the Use of Database PL/SQL Packages for more information about using UTL_SMTP with Autonomous Database.
SMTP Send Email Sample Code
Shows sample code for sending email with UTL_SMTP on an Autonomous Database.
Sample Code
-- As ADMIN user, set up ACE to access SMTP host
connect admin/[password]@[ADBD-host]/[pdbname]_tp.atp.oraclecloud.com
begin
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (
host => '[SMTP_Server_address]',
lower_port => 25,
upper_port => 25,
ace => xs$ace_type(
privilege_list => xs$name_list('smtp'),
principal_name => '[database_user]',
principal_type => xs_acl.ptype_db));
end;
/
-- If using secure SMTP, set up ACE to wallet for certificate-based secure (TLS) email
declare
m_wallet_path VARCHAR2(512);
begin
select property_value into m_wallet_path
from database_properties
where property_name like '%SSL%';
m_wallet_path := 'file:' || m_wallet_path;
DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE (
wallet_path => m_wallet_path,
ace => xs$ace_type(
privilege_list => xs$name_list('use_client_certificates'),
principal_name => '[database_user]',
principal_type => xs_acl.ptype_db));
end;
/
-- As ADMIN or regular database user with privileges set up above, use UTL_SMTP to send email
connect [database_user]/[password]@[ADBD-host]/[pdbname]_tp.atp.oraclecloud.com
-- Example of normal (non-secure) email
DECLARE
c UTL_SMTP.CONNECTION;
reply UTL_SMTP.REPLY;
host varchar2(64) := '[SMTP_Server_address]',
port pls_integer := 25;
sender varchar2(64) := '[sender-address@domain.com]';
recipient varchar2(64) := '[recipient-address@domain.com]';
BEGIN
c := UTL_SMTP.OPEN_CONNECTION (host, port);
if (reply.CODE != 220) then
raise_application_error(-20000,'UTL_SMTP.CONNECTION: '
|| reply.CODE || ' - ' || reply.TEXT);
end if;
UTL_SMTP.HELO(c, host);
UTL_SMTP.MAIL(c, sender);
UTL_SMTP.RCPT(c, recipient);
UTL_SMTP.OPEN_DATA(c);
UTL_SMTP.WRITE_DATA(c,'From:[Sender] <' || sender || '>' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(c,'To:[Recipient] <' || recipient || '>' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(c,'Subject: Email from UTL_SMTP App' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(c,'Mail Content');
UTL_SMTP.CLOSE_DATA(c);
dbms_output.put_line('Close Data: ' || reply.code);
UTL_SMTP.QUIT(c);
END;
/
-- Example of secure email using OCI Mail Server as the SMTP server
DECLARE
m_wallet_path VARCHAR2(256);
c UTL_SMTP.CONNECTION;
reply UTL_SMTP.REPLY;
host varchar2(64) := 'smtp.email.[region].oci.oraclecloud.com';
port pls_integer := 25;
sender varchar2(64) := '[sender-address@domain.com]';
recipient varchar2(64) := '[recipient-address@domain.com]';
BEGIN
select property_value into m_wallet_path
from database_properties
where property_name like '%SSL%';
m_wallet_path := 'file:' || m_wallet_path;
-- secure connection with SSL/TLS made *before* SMTP communication
-- use hostname matched against CN or SMTP server's certificate
reply := UTL_SMTP.OPEN_CONNECTION (
host => host,
port => port,
c => c,
wallet_path => m_wallet_path,
wallet_password => NULL,
secure_connection_before_smtp => FALSE,
secure_host => NULL);
if (reply.CODE != 220) then
raise_application_error(-20000,'UTL_SMTP.OPEN_CONNECTION: '
|| reply.CODE ||' - ' || reply.TEXT);
end if;
UTL_SMTP.STARTTLS(c);
-- username is the 'SMTP credential' for the user's email address
-- obtained from OCI Identity -> Users -> email address.
-- Scheme refers to SASL (Simple Authentication and Security Layer)
-- Authentication Schemes for SMTP protocol - PLAIN, CRAM-MD5, LOGIN
-- https://docs.oracle.com/en-us/iaas/Content/Email/Concepts/overview.htm
UTL_SMTP.AUTH(
c => c,
username => '[ocid1.user.verylongaddresscreatedinOCIforuseraddress.com]',
password => '[password-provided-from-create]',
schemes => 'PLAIN');
UTL_SMTP.HELO(c, host);
UTL_SMTP.MAIL(c, sender);
UTL_SMTP.RCPT(c, recipient);
UTL_SMTP.OPEN_DATA(c);
UTL_SMTP.WRITE_DATA(c, 'From: Sender <' || sender || '>' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(c, 'To: Recipient <' || recipient || '>' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(c, 'Subject: Email from UTL_SMTP App' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(c, 'Mail Content');
UTL_SMTP.WRITE_DATA(c, 'Sender is a domain-owned approved sender in OCI Mail Service');
UTL_SMTP.CLOSE_DATA(c);
dbms_output.put_line('Close Data: ' || reply.code);
UTL_SMTP.QUIT(c);
END;
/Send Messages as Email from Autonomous Database
APPLIES TO: Oracle Public Cloud only
You can use the
DBMS_CLOUD_NOTIFICATION to send messages as an email.
Note
You can only use
You can only use
DBMS_CLOUD_NOTIFICATION for Email notifications with Autonomous Database version 19.21 and above.