Send Email on Autonomous Database
There are a number of options for sending email on Autonomous Database. You can also send text messages or the output of an SQL query to a Slack or MSTeams channel.
- Send Email with Email Delivery Service on Autonomous Database
Describes the steps to send email usingUTL_SMTP
on Autonomous Database. - Send Email with an Email Provider on a Private Endpoint
Describes the steps to send email with an email provider that is on Private Endpoint. - Use Credential Objects to set SMTP Authentication
Describes how to pass a credential objects toUTL_SMTP.SET_CREDENTIAL
APIs. - Send Email from Autonomous Database Using DBMS_CLOUD_NOTIFICATION
Use theDBMS_CLOUD_NOTIFICATION
package to send messages and query results as email.
Parent topic: Send Email and Notifications on Autonomous Database
Send Email with Email Delivery Service on Autonomous Database
Describes the steps to send email using UTL_SMTP
on Autonomous Database.
Oracle Cloud Infrastructure Email Delivery Service is the only supported email provider for public SMTP endpoints.
To send email with Oracle Cloud Infrastructure Email Delivery Service:
See UTL_SMTP for information on
UTL_SMTP
.
See PL/SQL Packages Notes for Autonomous Database for UTL_SMTP
restrictions with Autonomous Database.
- SMTP Send Email Sample Code
Shows sample code for sending email withUTL_SMTP
on Autonomous Database.
Parent topic: Send Email on Autonomous Database
SMTP Send Email Sample Code
Shows sample code for sending email with UTL_SMTP
on Autonomous Database.
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
IS
mail_conn utl_smtp.connection;
username varchar2(1000):= 'ocid1.user.oc1.username';
passwd varchar2(50):= 'password';
msg_from varchar2(50) := 'adam@example.com';
mailhost VARCHAR2(50) := 'smtp.us-ashburn-1.oraclecloud.com';
BEGIN
mail_conn := UTL_smtp.open_connection(mailhost, 587);
utl_smtp.starttls(mail_conn);
UTL_SMTP.AUTH(mail_conn, username, passwd, schemes => 'PLAIN');
utl_smtp.mail(mail_conn, msg_from);
utl_smtp.rcpt(mail_conn, msg_to);
UTL_smtp.open_data(mail_conn);
UTL_SMTP.write_data(mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
UTL_SMTP.write_data(mail_conn, 'To: ' || msg_to || UTL_TCP.crlf);
UTL_SMTP.write_data(mail_conn, 'From: ' || msg_from || UTL_TCP.crlf);
UTL_SMTP.write_data(mail_conn, 'Subject: ' || msg_subject || UTL_TCP.crlf);
UTL_SMTP.write_data(mail_conn, 'Reply-To: ' || msg_to || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(mail_conn, msg_text || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_smtp.close_data(mail_conn);
UTL_smtp.quit(mail_conn);
EXCEPTION
WHEN UTL_smtp.transient_error OR UTL_smtp.permanent_error THEN
UTL_smtp.quit(mail_conn);
dbms_output.put_line(sqlerrm);
WHEN OTHERS THEN
UTL_smtp.quit(mail_conn);
dbms_output.put_line(sqlerrm);
END;
/
Where:
-
mailhost: specifies the SMTP Connection Endpoint from Step 1 in Send Email with Email Delivery Service on Autonomous Database.
-
username: specifies the SMTP credential username from Step 2 in Send Email with Email Delivery Service on Autonomous Database.
-
passwd: specifies the SMTP credential password from Step 2 in Send Email with Email Delivery Service on Autonomous Database.
-
msg_from: specifies one of the approved senders from Step 3 in Send Email with Email Delivery Service on Autonomous Database.
Send Email with an Email Provider on a Private Endpoint
Describes the steps to send email with an email provider that is on Private Endpoint.
To send email from Autonomous Database using a email provider on a private endpoint, the email provider must be accessible from the Oracle Cloud Infrastructure VCN (the Autonomous Database instance's private endpoint). For example, you can access an email provider when:
-
Both the source Autonomous Database instance and the email provider are in the same Oracle Cloud Infrastructure VCN.
-
The source Autonomous Database instance and the email provider are in different Oracle Cloud Infrastructure VCNs that are paired.
-
The email provider is on an on-premises network that is connected to the source Autonomous Database instance's Oracle Cloud Infrastructure VCN using FastConnect or VPN.
As a prerequisite, to send email using an email provider, define the following ingress and egress rules:
-
Define an egress rule in the source database's subnet security list or network security group such that the traffic to the target host is allowed on port 587 or port 25 (depending on which port you are using).
-
Define an ingress rule in the target host's subnet security list or network security group such that the traffic from the source Autonomous Database instance's IP address to port 587 or port 25 is allowed (depending on which port you are using).
To send email from an email provider on private endpoint:
Parent topic: Send Email on Autonomous Database
Use Credential Objects to set SMTP Authentication
Describes how to pass a credential objects to UTL_SMTP.SET_CREDENTIAL
APIs.
The SET_CREDENTIAL
subprogram sends the AUTH command to authenticate to the SMTP server.
The UTL_SMTP.SET_CREDENTIAL
subprogram enables you to pass credential objects to set SMTP authentication. Credential objects are schema objects, hence they can be accessed only by privileged users and enable you to configure schema-level privileges to access control the credentials. Passing credential objects is a appropriate and secure way to store and manage username/password/keys for authentication.
The UTL_SMTP.SET_CREDENTIAL
subprogram is a secure and convenient alternative to UTL_SMTP.AUTH
subprogram.
Example
...
UTL_SMTP.AUTH (l_mail_conn, 'ocid1.user.oc1.username', 'xxxxxxxxxxxx', schemes => 'PLAIN');
...
As shown in the example above, when you invoke AUTH
subprogram, you must pass the username/password in clear text as part of PL/SQL formal parameters. You might need to embed the username/password into various PL/SQL automation or cron scripts. Passing clear text passwords is a compliance issue that is addressed in UTL_SMTP.SET_CREDENTIAL
subprogram.
See AUTH Function and Procedure for more information.
UTL_SMTP.SET_CREDENTIAL
Syntax
PROCEDURE UTL_SMTP.SET_CREDENTIAL (
c IN OUT NOCOPY connection,
credential IN VARCHAR2,
schemes IN VARCHAR2 DEFAULT NON_CLEARTEXT_PASSWORD_SCHEMES
);
FUNCTION UTL_SMTP.SET_CREDENTIAL (
c IN OUT NOCOPY connection,
credential IN VARCHAR2,
schemes IN VARCHAR2 DEFAULT NON_CLEARTEXT_PASSWORD_SCHEMES)
RETURN reply;
Example
-
Create a credential object:
BEGIN DBMS_CLOUD.CREATE_CREDENTIAL ( credential_name => 'HTTP_CRED', username => 'web_app_user', password => '<password>' ); END;
This creates a credential object which creates a stored username/password pair.
See CREATE_CREDENTIAL Procedure for more information.
See Specifying Scheduler Job Credentials for more information.
-
Execute
UTL_SMTP.SET_CREDENTIAL
procedure:DECLARE l_mail_conn UTL_SMTP.CONNECTION; BEGIN l_mail_conn := UTL_SMTP.OPEN_CONNECTION('smtp.example.com', 587); UTL_SMTP.SET_CREDENTIAL(l_mail_conn, 'SMTP_CRED', SCHEMES => 'PLAIN'); ... END;
This example sends the command to authenticate to the SMTP server. The Web server needs this information to authorize the request. The value
l_mail_conn
is the SMTP connection,SMTP_CRED
is the credentials name andPLAIN
is the SMTP authentication scheme.
See UTL_SMTP for more information.
See PL/SQL Packages Notes for Autonomous Database for information on restrictions for UTL_SMTP
on Autonomous Database.
Parent topic: Send Email on Autonomous Database
Send Email from Autonomous Database Using DBMS_CLOUD_NOTIFICATION
Use the DBMS_CLOUD_NOTIFICATION
package to send messages and query results as email.
DBMS_CLOUD_NOTIFICATION
supports sending email only to public SMTP endpoints. Oracle Cloud Infrastructure Email Delivery Service is the only supported email provider.
- Send Messages as Email from Autonomous Database
You can use theDBMS_CLOUD_NOTIFICATION
to send messages as an email. - Send Query Results as Email from Autonomous Database
You can use theDBMS_CLOUD_NOTIFICATION
package to send the results of a query as an email.
Parent topic: Send Email on Autonomous Database
Send Messages as Email from Autonomous Database
DBMS_CLOUD_NOTIFICATION
to send messages as an email.