ORA-06502 ORA-24247 calling UTL_MAIL from Oracle 11gR2
April 27, 2012 12 Comments
Environment: Oracle database 126.96.36.199.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 188.8.131.52.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 = 'email@example.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 => 'firstname.lastname@example.org', lower_port => 25, upper_port => 25 ); COMMIT; END;
After these steps, you should be able to successfully send e-mails from within the database:
p_sender => ‘email@example.com’,
p_recipients => ‘firstname.lastname@example.org’,
p_subject => ‘This is the subject line!’,
p_message => ‘Hello World!’);
anonymous block completed