Two of the most important aspects of any application migration are project-planning and map-gap analysis. Everyone wants to have all their application functionality intact after migration. We tend to ignore the sideway functionality of applications, such as email service, FTP sites, any least important outbound integrations, database links, and so on. The list can be huge and depends on your application. These leftovers all come to mind just before the cut-over. As addons, they’re most often ignored during the testing too.

It happens to professionals too

Having these scenarios often reminds us of last-minute exam preparation. I faced a similar situation during one of the migration projects: A day before cut-over, I realized that the email functionality wasn’t working because the home ground for application was changed from the on-premises data center to Oracle Cloud Infrastructure (OCI), and the SMTP configuration was ported without the adjustment.

The email functionality was using the UTIL_SMTP package on the database, and I had to make the modification on my OCI database-as-a-service to overcome the issue. At this point, OCI Email delivery service came in handy. I created SMTP credentials and approved the sender. Getting the configuration was never difficult.

Making these changes wasn’t enough, and PL/SQL was complaining about the certificate. When you feel like you’re done, and something pops up is never a good feeling. No matter what, our friend MOS always has some things to help us with. The following note helped me to conquer this last-minute challenge and I could focus on my next day’s cut-over.

How to send email from your OCI database system through UTL_SMTP

Downloading and importing the certificate to the database server

  1. Download the SSL root and intermediate certificates.

    • Root certificate: DigiCert Global Root G2
    • Intermediate certificate: DigiCert Global G2 TLS RSA SHA256 2020 CA1

  2. Create the auto login wallet with the following command:

    orapki wallet create -wallet /home/oracle/SMTP -pwd "PAssword_#123" -auto_login

  3. Add the certificates to the wallet with the following commands:

    orapki wallet add -wallet /home/oracle/SMTP -trusted_cert -cert "/home/oracle/SMTP/SMTP_SERVER_CERTS/DigiCertGlobalRootCA.crt" -pwd PAssword_#123

orapki wallet add -wallet /home/oracle/SMTP -trusted_cert -cert "/home/oracle/SMTP/SMTP_SERVER_CERTS/DigiCertSHA2SecureServerCA.crt" -pwd PAssword_#123

Modify the PL/SQL for your SMTP configuration, credential, sender, and receiver addresses


DECLARE
mailhost VARCHAR2(64) := 'SMTP Server for e.g smtp.email.us-ashburn-1.oci.oraclecloud.com';
sender VARCHAR2(64) := '<Approved sender email id as mentioned in step C>';
recipient VARCHAR2(64) := '<Recepient email id>';
wallet_pwd VARCHAR2(64) := '<Wallet password as per the Step F>';
wallet_loc VARCHAR2(64) := 'file:<Wallet file location created in step F for e.g /home/oracle/SMTP>';
user_name VARCHAR2(500) := '<User OCID from step B>';
user_pwd VARCHAR2(64) := '<Password from Step B>';
mail_connection utl_smtp.connection;
BEGIN
-- Make a secure connection using the SSL port configured with your SMTP server
-- Note: The sample code here uses the default of 465 but check your SMTP server settings
mail_connection := utl_smtp.open_connection
(
host => mailhost,
port => 587,
wallet_path => wallet_loc,
wallet_password => wallet_pwd,
secure_connection_before_smtp => FALSE
);
utl_smtp.starttls(mail_connection);
-- Call the Auth procedure to authorized a user for access to the mail server
-- Schemes should be set appropriatelty for your mail server
-- See the UTL_SMTP documentation for a list of constants and meanings
UTL_SMTP.AUTH(
c => mail_connection,
username => user_name,
password => user_pwd,
schemes => 'PLAIN');
-- Set up and make the the basic smtp calls to send a test email
utl_smtp.helo(mail_connection, mailhost);
utl_smtp.mail(mail_connection, sender);
utl_smtp.rcpt(mail_connection, recipient);
utl_smtp.open_data(mail_connection);
utl_smtp.write_data(mail_connection, 'This is a test message using SSL with SMTP.' || chr(13));
utl_smtp.write_data(mail_connection, 'This test requires an Oracle Wallet be properly configured.' || chr(13));
utl_smtp.close_data(mail_connection);
utl_smtp.quit(mail_connection);
exception when others then
dbms_output.put_line('Error in the anonymous plsql block: '|| sqlerrm);
end;
/

So whether you want to have a new setup for your Database or porting the existing setup you are  ready to try yourself. Please do give it a shot if you wish to experience this on OCI.   Email service is very easy to configure and it would help you big time if you are looking for a quick, easy and robust email solution.