The DBMS_CLOUD and UTL_SMTP packages in the Autonomous Database

New Autonomous Database features are being added all the time. For now, ADB for shared infrastructure supports 18c while 19c can be used only in preview mode.

The preview period for ADB 19c ended yeaterday: January 15th, 2020. From now on, it is no longer possible to provision new preview instances nor clone existing instances to a preview instance. However, existing preview instances will remain available until January 30th when the final termination process will happen. And now, we are awaiting for the Oracle 20c preview version.

So, what else is new in the Autonomous Database (Shared Infrastructure):

The DBMS_CLOUD REST API functions provide a generic API that lets you call any REST API with the following supported cloud services:

Oracle Cloud Infrastructure
Amazon Web Services
Azure Cloud
Oracle Cloud Infrastructure Classic

DBMS_CLOUD supports GET, PUT, POST, HEAD and DELETE HTTP methods. The REST API method to be used for an HTTP request is typically documented in the Cloud REST API documentation.

Check also the summary of the DBMS_CLOUD_ADMIN package and especially the CREATE_DATABASE_LINK procedure as this is the supported way to create a database link in ADB.

To run DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK with a user other than ADMIN you need to grant EXECUTE and CREATE DATABASE LINK privileges to that user. For example, run the following command as ADMIN to grant privileges to JULIAN:

GRANT EXECUTE ON DBMS_CLOUD_ADMIN TO JULIAN;
GRANT CREATE DATABASE LINK TO JULIAN;

Behind the curtains, Oracle runs C##CLOUD$SERVICE.DBMS_CLOUD_DBLINK_INTERNAL.

Note that packages like DBMS_CLOUD, DBMS_CLOUD_ADMIN, DBMS_CLOUD_CORE, DBMS_CLOUD_DBLINK, etc. are owned by C##CLOUD$SERVICE (owns 23 packages), not by SYS!

In ADB, there are few restrictions for some PL/SQL packages. Oracle have removed the UTL_TCP package.

UTL_HTTP Restrictions:

Connections through IP addresses are not allowed
– Only HTTPS connections are allowed (HTTP and HTTP_PROXY are disallowed)
– The only allowed ports are 443 and 8443
– The SET_WALLET and SET_AUTHENTICATION_FROM_WALLET APIs are disallowed
– The WALLET_PATH and WALLET_PASSWORD arguments for the CREATE_REQUEST_CONTEXT, REQUEST, and REQUEST_PIECES APIs are ignored

UTL_SMTP Restrictions:

– The only supported email provider is Oracle Cloud Infrastructure Email Delivery service
– Mail with an IP address in the host name is not allowed
– The only allowed ports are 25 and 587

DBMS_NETWORK_ACL_ADMIN Restrictions:

– Granting ACL privileges on IP addresses is not allowed
– The http_proxy and use_passwords ACL privileges are not allowed

This Oracle example is using DBMS_CLOUD.SEND_REQUEST in order to create and delete an Oracle Cloud Infrastructure Object Storage bucket.

Here are 2 new additions to the ADB feature list:

1. Access Control Lists Honored By ADB Built-in Tools

You can control and restrict access to your Autonomous Database by setting network access control lists (ACLs). When you provision your Autonomous Database you can either choose the database to be accessible from all IP addresses or you can restrict access to your database to a whitelisted set of clients. You can change your decision after provisioning and set or change the access rules.

Here are 3 links for additional details:

1. VCNs and Subnets for details on Virtual Cloud Networks (VCN).
2. Oracle Services: Service Gateway Access for details on setting up a Service Gateway.
3. Transit Routing: Private Access to Oracle Services for details on Transit Routing.

2. Send Emails from ADB using UTL_SMTP

There are 4 steps you must follow in order to send an email from ADB:

1. Configure Email Delivery Service
2. Allow SMTP Access for ADMIN via an Access Control Entry (ACE)
3. Create a PL/SQL Procedure to Send Email
4. Send a Test Email

Check How to Send an Email using UTL_SMTP in Autonomous Database by Can Tuzla for all the details and examples.

At the end, note that now we have the “Next Maintenance” field which shows the date and time for the upcoming maintenance window. All ADB instances are automatically assigned to a maintenance window and different instances can have different maintenance windows.