ORA-06502 ORA-24247 calling UTL_MAIL from Oracle 11gR2

Environment: Oracle database 11.2.0.3.0, Oracle Linux 6.2

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.

Suppose that you created the following wrapper procedure in PL/SQL:

CREATE OR REPLACE PROCEDURE UTILS.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;
/

To get this procedure working on Oracle 11g, there are several steps you need to take.

First, you need to actually 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 Apr 27 14:49:33 2012
SQL> connect / as sysdba
Connected.
SQL> @?/rdbms/admin/utlmail.sql
SQL> @?/rdbms/admin/prvtmail.plb
SQL> grant execute on utl_mail to public;

Next, 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 a “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;

Finally, you need to create an Access Control List (ACL) for your e-mail server and grant the necessary users access to this ACL. Without an ACL, you will receive the following error: “ORA-24247: network access denied by access control list (ACL)“.

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;

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'mail_access.xml',
    host         => 'mymailserver@mydomain.com',
    lower_port   => 25,
    upper_port   => 25
    );
   COMMIT;
END;

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

begin
utils.send_mail(
p_sender => ‘ora11gtest@mydomain.com’,
p_recipients => ‘matthiash@mydomain.com’,
p_subject => ‘This is the subject line!’,
p_message => ‘Hello World!’);
end;
*Action:
anonymous block completed

Matthias

About these ads