{"id":2484,"date":"2025-01-25T14:50:13","date_gmt":"2025-01-25T14:50:13","guid":{"rendered":"https:\/\/w3buddy.com\/?p=2484"},"modified":"2026-01-15T13:19:06","modified_gmt":"2026-01-15T07:49:06","slug":"setting-up-utl_mail-in-oracle-database-a-step-by-step-guide","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/setting-up-utl_mail-in-oracle-database-a-step-by-step-guide\/","title":{"rendered":"Setting Up UTL_MAIL in Oracle Database: A Step-by-Step Guide"},"content":{"rendered":"\n

UTL_MAIL<\/strong> is a built-in package in Oracle Database (within the SYS<\/code> 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.<\/p>\n\n\n\n

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

Prerequisites<\/h3>\n\n\n\n

Before proceeding, ensure:<\/p>\n\n\n\n

    \n
  1. Oracle Database is installed<\/strong> and configured correctly.<\/li>\n\n\n\n
  2. You have access to an SMTP server to send emails (e.g., Gmail SMTP<\/strong>, Microsoft Outlook SMTP<\/strong>, or an organizational SMTP server).<\/li>\n\n\n\n
  3. You\u2019re logged in as a user with SYSDBA privileges.<\/li>\n<\/ol>\n\n\n\n

    All SQL commands should be executed from the SQL><\/code> prompt in SQL*Plus or any other Oracle SQL client tool.<\/p>\n\n\n\n

    Steps to Configure UTL_MAIL in Oracle Database<\/h3>\n\n\n\n
      \n
    1. Log in as a SYSDBA user Use the following command to connect as a SYSDBA<\/strong> user:<\/li>\n<\/ol>\n\n\n\n
      sqlplus '\/ as sysdba'<\/code><\/pre>\n\n\n\n
        \n
      1. Execute the utlmail.sql script Run the UTL_MAIL installation script located in the $ORACLE_HOME\/rdbms\/admin<\/strong> directory:<\/li>\n<\/ol>\n\n\n\n
        @?\/rdbms\/admin\/utlmail.sql<\/code><\/pre>\n\n\n\n
          \n
        1. Execute the prvtmail.plb<\/strong> script Run the PL\/SQL script to complete the package installation:<\/li>\n<\/ol>\n\n\n\n
          @?\/rdbms\/admin\/prvtmail.plb<\/code><\/pre>\n\n\n\n
            \n
          1. Set the SMTP server details<\/strong> Configure the SMTP server information in the spfile.ora or init.ora file. Replace SMTP_SERVER_IP_ADDRESS<\/strong> and SMTP_PORT<\/strong> with your SMTP server details:<\/li>\n<\/ol>\n\n\n\n
            ALTER SYSTEM SET smtp_out_server = 'SMTP_SERVER_IP_ADDRESS:SMTP_PORT' SCOPE=BOTH;<\/code><\/pre>\n\n\n\n
              \n
            • Example (Gmail):<\/strong><\/li>\n<\/ul>\n\n\n\n
              ALTER SYSTEM SET smtp_out_server = 'smtp.gmail.com:587' SCOPE=BOTH;<\/code><\/pre>\n\n\n\n
                \n
              • Example (Microsoft Outlook):<\/strong><\/li>\n<\/ul>\n\n\n\n
                ALTER SYSTEM SET smtp_out_server = 'smtp.office365.com:587' SCOPE=BOTH;<\/code><\/pre>\n\n\n\n
                  \n
                • The default SMTP port is 25<\/strong>, but some servers (e.g., Gmail and Outlook) require 587 <\/strong>or 465 <\/strong>for secure connections.<\/li>\n<\/ul>\n\n\n\n
                    \n
                  1. Verify UTL_MAIL configuration<\/strong> Confirm the configuration by checking the smtp_out_server parameter:<\/li>\n<\/ol>\n\n\n\n
                    SHOW PARAMETER smtp_out_server;<\/code><\/pre>\n\n\n\n
                    Sending an Email Using UTL_MAIL<\/h2>\n\n\n\n
                    After configuring UTL_MAIL, you can send an email by following these steps:<\/p>\n\n\n\n
                      \n
                    1. Set the SMTP server for Outlook<\/strong> Use the following command to configure the SMTP server:<\/li>\n<\/ol>\n\n\n\n
                      ALTER SYSTEM SET smtp_out_server = 'smtp.office365.com:587' SCOPE=BOTH;<\/code><\/pre>\n\n\n\n
                        \n
                      1. Send an email using the <\/strong>utl_mail.send<\/strong><\/code> procedure<\/strong> Example PL\/SQL block for sending an email:<\/li>\n<\/ol>\n\n\n\n
                        BEGIN\n  utl_mail.send(\n    sender     => 'your_email@yourdomain.com', \n    recipients => 'recipient@domain.com', \n    subject    => 'Outlook SMTP Test', \n    message    => 'This is a test email sent using the Microsoft Outlook SMTP server.'\n  );\nEND;\n\/<\/code><\/pre>\n\n\n\n
                        Ensure that your_email@yourdomain.com<\/strong> is a valid email address in your organization.<\/p>\n\n\n\n
                          \n
                        1. Authenticate with Outlook if required<\/strong> If authentication is needed, configure Oracle Wallet to store the credentials securely. Refer to Oracle documentation for wallet setup.<\/li>\n<\/ol>\n\n\n\n
                          Real-Time Scenario: Auto Alert for Oracle Alerts<\/h3>\n\n\n\n
                          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.<\/p>\n\n\n\n
                          Example: Auto Alert for Tablespace Usage<\/h4>\n\n\n\n
                            \n
                          1. Create a PL\/SQL procedure to monitor tablespace usage<\/strong><\/li>\n<\/ol>\n\n\n\n
                            CREATE OR REPLACE PROCEDURE send_tablespace_alert IS\n  v_message VARCHAR2(4000);\nBEGIN\n  FOR ts IN (\n    SELECT tablespace_name, \n           ROUND((used_space \/ total_space) * 100, 2) AS usage_percent\n    FROM (\n      SELECT a.tablespace_name, \n             NVL(b.bytes \/ 1024 \/ 1024, 0) AS used_space, \n             a.bytes \/ 1024 \/ 1024 AS total_space\n      FROM dba_data_files a\n      LEFT JOIN dba_segments b\n      ON a.tablespace_name = b.tablespace_name\n    )\n    WHERE ROUND((used_space \/ total_space) * 100, 2) > 80\n  ) LOOP\n    v_message := 'Tablespace ' || ts.tablespace_name || \n                 ' is ' || ts.usage_percent || '% full.';\n    utl_mail.send(\n      sender     => 'alert@yourdomain.com', \n      recipients => 'dba@yourdomain.com', \n      subject    => 'Tablespace Usage Alert', \n      message    => v_message\n    );\n  END LOOP;\nEND;\n\/<\/code><\/pre>\n\n\n\n
                              \n
                            1. Schedule the procedure using DBMS_SCHEDULER<\/strong><\/li>\n<\/ol>\n\n\n\n
                              BEGIN\n  DBMS_SCHEDULER.CREATE_JOB (\n    job_name        => 'TABLESPACE_ALERT_JOB',\n    job_type        => 'PLSQL_BLOCK',\n    job_action      => 'BEGIN send_tablespace_alert; END;',\n    start_date      => SYSTIMESTAMP,\n    repeat_interval => 'FREQ=HOURLY; INTERVAL=1',\n    enabled         => TRUE\n  );\nEND;\n\/<\/code><\/pre>\n\n\n\n
                              With this setup, the database will automatically send an email alert whenever a tablespace exceeds 80% usage.<\/p>\n\n\n\n
                              Troubleshooting Common Issues<\/h2>\n\n\n\n
                                \n
                              1. SMTP server not reachable<\/strong>\n
                                  \n
                                • Ensure the SMTP server and port are accessible from the Oracle server.<\/li>\n\n\n\n
                                • Test connectivity using tools like ping or telnet:<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n
                                  telnet smtp.office365.com 587<\/code><\/pre>\n\n\n\n
                                    \n
                                  1. Authentication required<\/strong>\n
                                      \n
                                    • If the SMTP server requires authentication (e.g., Gmail or Outlook), you may need to configure Oracle Wallet for secure email transmission.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n
                                        \n
                                      1. Package not found error<\/strong>\n
                                          \n
                                        • Verify that the UTL_MAIL<\/strong> package is installed correctly:<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n
                                          SELECT object_name FROM all_objects WHERE object_name = 'UTL_MAIL';<\/code><\/pre>\n\n\n\n
                                            \n
                                          1. Email not sent<\/strong><\/li>\n<\/ol>\n\n\n\n
                                              \n
                                            • Check the smtp_out_server parameter and ensure it matches your SMTP server details.<\/li>\n\n\n\n
                                            • Confirm the sender’s email address is valid.<\/li>\n<\/ul>\n\n\n\n
                                              Conclusion<\/h3>\n\n\n\n
                                              The UTL_MAIL<\/strong> 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.<\/p>\n\n\n\n
                                              With UTL_MAIL configured, you can integrate email notifications seamlessly into your Oracle applications to keep users informed and improve workflow automation.<\/p>\n","protected":false},"excerpt":{"rendered":"
                                              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\u2019ll walk you through the steps to configure the UTL_MAIL package, provide an example for […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-2484","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/2484","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=2484"}],"version-history":[{"count":1,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/2484\/revisions"}],"predecessor-version":[{"id":2485,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/2484\/revisions\/2485"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=2484"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=2484"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=2484"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}