Setting Up UTL_MAIL in Oracle Database: A Step-by-Step Guide

UTL_MAIL is a built-in package in Oracle Database (within the SYS schema) that allows you to send emails directly from the database. This package is particularly useful for automating email notifications or alerts in your Oracle applications.

In this guide, we’ll walk you through the steps to configure the UTL_MAIL package, provide an example for using Microsoft Outlook’s SMTP server, and demonstrate a real-time scenario for sending auto-alerts.

Prerequisites

Before proceeding, ensure:

1. Oracle Database is installed and configured correctly.
2. You have access to an SMTP server to send emails (e.g., Gmail SMTP, Microsoft Outlook SMTP, or an organizational SMTP server).
3. You’re logged in as a user with SYSDBA privileges.

All SQL commands should be executed from the SQL> prompt in SQL*Plus or any other Oracle SQL client tool.

Steps to Configure UTL_MAIL in Oracle Database

1. Log in as a SYSDBA user Use the following command to connect as a SYSDBA user:

sqlplus '/ as sysdba'

2. Execute the utlmail.sql script Run the UTL_MAIL installation script located in the $ORACLE_HOME/rdbms/admin directory:

@?/rdbms/admin/utlmail.sql

3. Execute the prvtmail.plb script Run the PL/SQL script to complete the package installation:

@?/rdbms/admin/prvtmail.plb

4. Set the SMTP server details Configure the SMTP server information in the spfile.ora or init.ora file. Replace SMTP_SERVER_IP_ADDRESS and SMTP_PORT with your SMTP server details:

ALTER SYSTEM SET smtp_out_server = 'SMTP_SERVER_IP_ADDRESS:SMTP_PORT' SCOPE=BOTH;

• Example (Gmail):

ALTER SYSTEM SET smtp_out_server = 'smtp.gmail.com:587' SCOPE=BOTH;

• Example (Microsoft Outlook):

ALTER SYSTEM SET smtp_out_server = 'smtp.office365.com:587' SCOPE=BOTH;

• The default SMTP port is 25, but some servers (e.g., Gmail and Outlook) require 587 or 465 for secure connections.

5. Verify UTL_MAIL configuration Confirm the configuration by checking the smtp_out_server parameter:

SHOW PARAMETER smtp_out_server;

Sending an Email Using UTL_MAIL

After configuring UTL_MAIL, you can send an email by following these steps:

1. Set the SMTP server for Outlook Use the following command to configure the SMTP server:

ALTER SYSTEM SET smtp_out_server = 'smtp.office365.com:587' SCOPE=BOTH;

2. Send an email using the <strong>utl_mail.send</strong> procedure Example PL/SQL block for sending an email:

BEGIN
  utl_mail.send(
    sender     => 'your_email@yourdomain.com', 
    recipients => 'recipient@domain.com', 
    subject    => 'Outlook SMTP Test', 
    message    => 'This is a test email sent using the Microsoft Outlook SMTP server.'
  );
END;
/

Ensure that your_email@yourdomain.com is a valid email address in your organization.

3. Authenticate with Outlook if required If authentication is needed, configure Oracle Wallet to store the credentials securely. Refer to Oracle documentation for wallet setup.

Real-Time Scenario: Auto Alert for Oracle Alerts

You can use UTL_MAIL to set up an automated email alert system for Oracle Database events, such as monitoring tablespace usage or critical database errors.

Example: Auto Alert for Tablespace Usage

1. Create a PL/SQL procedure to monitor tablespace usage

CREATE OR REPLACE PROCEDURE send_tablespace_alert IS
  v_message VARCHAR2(4000);
BEGIN
  FOR ts IN (
    SELECT tablespace_name, 
           ROUND((used_space / total_space) * 100, 2) AS usage_percent
    FROM (
      SELECT a.tablespace_name, 
             NVL(b.bytes / 1024 / 1024, 0) AS used_space, 
             a.bytes / 1024 / 1024 AS total_space
      FROM dba_data_files a
      LEFT JOIN dba_segments b
      ON a.tablespace_name = b.tablespace_name
    )
    WHERE ROUND((used_space / total_space) * 100, 2) > 80
  ) LOOP
    v_message := 'Tablespace ' || ts.tablespace_name || 
                 ' is ' || ts.usage_percent || '% full.';
    utl_mail.send(
      sender     => 'alert@yourdomain.com', 
      recipients => 'dba@yourdomain.com', 
      subject    => 'Tablespace Usage Alert', 
      message    => v_message
    );
  END LOOP;
END;
/

2. Schedule the procedure using DBMS_SCHEDULER

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'TABLESPACE_ALERT_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN send_tablespace_alert; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=HOURLY; INTERVAL=1',
    enabled         => TRUE
  );
END;
/

With this setup, the database will automatically send an email alert whenever a tablespace exceeds 80% usage.

Troubleshooting Common Issues

1. SMTP server not reachable
   • Ensure the SMTP server and port are accessible from the Oracle server.
   • Test connectivity using tools like ping or telnet:

telnet smtp.office365.com 587

2. Authentication required
   • If the SMTP server requires authentication (e.g., Gmail or Outlook), you may need to configure Oracle Wallet for secure email transmission.

3. Package not found error
   • Verify that the UTL_MAIL package is installed correctly:

SELECT object_name FROM all_objects WHERE object_name = 'UTL_MAIL';

4. Email not sent

• Check the smtp_out_server parameter and ensure it matches your SMTP server details.
• Confirm the sender’s email address is valid.

Conclusion

The UTL_MAIL package in Oracle Database is a powerful tool for sending automated emails. By following the steps outlined above, you can configure and use UTL_MAIL effectively in your Oracle environment. Make sure to test the setup thoroughly and address any SMTP server requirements for secure communication.

With UTL_MAIL configured, you can integrate email notifications seamlessly into your Oracle applications to keep users informed and improve workflow automation.