APEXblog.nl - Tips and Tricks

About This Blog:
I (Richard Weug) started this blog primary for myself to save all kinds of Apex tips and tricks in one place. To use it as a place to write down how I used some coding in my own projects, but also to copy and paste all kinds of articles I find on the Internet. (So I never have to wonder on what website did I read about??? When I see something interesting I collect the content so I have my own archive/knowlegde base.

View my profile on LinkedIn

 

PL/SQL stuff

Print
27
Apr

Changes in sending email

Written by Richard Weug. Posted in PL/SQL

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. Environment: Oracle database 11.2.0.3.0, Oracle Linux 6.2

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 = '
 This e-mail address is being protected from spambots. You need JavaScript enabled to view it.
 :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         => '
 This e-mail address is being protected from spambots. You need JavaScript enabled to view it.
 ',
    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 => ‘ This e-mail address is being protected from spambots. You need JavaScript enabled to view it. ’,
p_recipients => ‘ This e-mail address is being protected from spambots. You need JavaScript enabled to view it. ’,
p_subject => ‘This is the subject line!’,
p_message => ‘Hello World!’);
end;
*Action:
anonymous block completed

Original Article written by: Matthias Hoys

Link:  http://matthiashoys.wordpress.com/2012/04/27/ora-06502-ora-24247-calling-utl_mail-from-oracle-11gr2