Enable Oracle database to send emails via SMTP server

Sending e-mails from within the Oracle database using the UTL_MAIL PL/SQL package used to be quite easy in Oracle 10g. However, in Oracle 11gR2, things have changed.To enable SMTP package to send mail we need to perform following steps.

1.       First, you need to actually install the UTL_MAIL package. It’s not installed by default on 11g

2.       Next, you need to add the address and port of the e-mail server to the “smtp_out_server” initialization parameter.

3.       Next to create an Access Control List (ACL) for your e-mail server and grant the necessary users access to this ACL.

4.        Create the wrapper procedure in PL/SQL.

Let’s see above steps in detail.

STEP1: Install the UTL_MAIL package. It’s not installed by default on 11g

$ sqlplus /nolog

 

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 6 10:21:07 2015

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

SQL> connect / as sysdba

 

Connected.

 

SQL> @?/rdbms/admin/utlmail.sql

 

Package created.

 

Synonym created.

 

SQL> @?/rdbms/admin/prvtmail.plb

 

Package created.

 

Package body created.

 

Grant succeeded.

 

Package body created.

 

No errors.

 

SQL> grant execute on utl_mail to public;

 

Grant succeeded.

 

STEP2: add the address and port of the e-mail server to the “smtp_out_server” initialization parameter.

You need to add the address and port of the e-mail server to the “smtp_out_server” initialization parameter. If you do not do this, you will receive “ORA-06502: PL/SQL: numeric or value error” error when you try to use the UTL_MAIL package.

Execute the following with user SYS as SYSDBA:

SQL> alter system set smtp_out_server = 'mymailserver@mydomain.com:25' scope=both;

 

3. Next to create an Access Control List (ACL) for your e-mail server and grant the necessary user’s access to this ACL.

The network utility family of PL/SQL packages, such as UTL_TCP, UTL_INADDR, UTL_HTTP, UTL_SMTP, and UTL_MAIL, allow Oracle users to make network callouts from the database using raw TCP or using higher-level protocols built on raw TCP. As of Oracle 11g a new package DBMS_NETWORK_ACL_ADMIN allows fine-grained control over network hosts access using access control lists (ACL) implemented by XML DB. Below I show major steps that are required to enable ACL for user SCOTT. You have to run them as SYS or SYSDBA user. Read Oracle documentation for more details.

Without an ACL, you will receive the following error:

ORA-24247: network access denied by access control list (ACL)

Create an ACL with one database user in Oracle database

BEGIN

   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (

    acl          => 'mail_access.xml',

    description  => 'Permissions to access e-mail server.',

    principal    => 'PUBLIC',

    is_grant     => TRUE,

    privilege    => 'connect');

   COMMIT;

END;

 

 Assign an ACL to one or more network hosts

BEGIN

   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (

    acl          => 'mail_access.xml',

    host         => 'mymailserver@mydomain.com',

    lower_port   => 25,

    upper_port   => 25

    );

   COMMIT;

END;

 

Confirm the created ACL is correct

SELECT host, lower_port, upper_port, acl

FROM   dba_network_acls

/

 

SELECT acl,

principal,

privilege,

is_grant,

TO_CHAR(start_date, 'DD-MON-YYYY HH24:MI') AS start_date,

TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date

FROM   dba_network_acl_privileges

/

 

4. Create the wrapper procedure in PL/SQL

 

CREATE OR REPLACE PROCEDURE SEND_MAIL (

   p_sender       IN   VARCHAR2,

   p_recipients   IN   VARCHAR2,

   p_cc           IN   VARCHAR2 DEFAULT NULL,

   p_bcc          IN   VARCHAR2 DEFAULT NULL,

   p_subject      IN   VARCHAR2,

   p_message      IN   VARCHAR2,

   p_mime_type    IN   VARCHAR2 DEFAULT 'text/plain; charset=us-ascii'

)

IS

 BEGIN

   UTL_MAIL.SEND (sender          => p_sender,

                  recipients      => p_recipients,

                  cc              => p_cc,

                  bcc             => p_bcc,

                  subject         => p_subject,

                  message         => p_message,

                  mime_type       => p_mime_type

                 );

EXCEPTION

   WHEN OTHERS

   THEN

      RAISE;

END send_mail;

/

 

5. After these steps, you should be able to successfully send e-mails from within the database:

 

Begin

send_mail(
p_sender => ‘ora11gtest@mydomain.com’,
p_recipients => ‘Amit@mydomain.com’,
p_subject => ‘This is the subject line!’,
p_message => ‘Hello World!’);
end;

*Action:
anonymous block completed

 

 

 

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!