Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux
While sending mail using utl_mail or utl_stmp in oracle 11g, you may get access denied error.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
begin utl_mail.send(sender => ‘admin@dbaclass.com’, recipients => ‘admin@dbaclass.com’, subject => ‘MAIL from ADMIn of dbaclass’, message => ‘Do visit dbaclass’); end; / * ERROR at line 1: ORA-24247: network access denied by access control list (ACL) ORA-06512: at “SYS.UTL_MAIL”, line 662 ORA-06512: at “SYS.UTL_MAIL”, line 679 ORA-06512: at line 2 |
Solution:
From 11g onward, to send mail using UTL_MAIL or UTL_SMTP , ACL need to be created in the database
|
1 2 3 |
SQL> alter system set smtp_out_server='mail.abuzer.local' scope=both; System altered. |
|
1 2 3 4 5 6 7 8 |
--- creating ACL as below exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('send_mail.xml','Allow mail to be send', 'USR_ABUZER', TRUE, 'connect'); commit; ----Grant the connect and resource privilege as below exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('send_mail.xml','USR_ABUZER',TRUE, 'connect'); exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('send_mail.xml','USR_ABUZER',TRUE, 'resolve'); exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('send_mail.xml','*',587); commit; |
|
1 2 3 4 5 6 7 8 9 10 |
conn usr_abuzer/password Connected. SQL> begin subject => 'TEST MAIL', message => 'TEST MESSAGE'); end; / PL/SQL procedure successfully completed. |
Author: Buğra PARLAYAN
Burgra Parlayan is an experienced Database and Weblogic Administrator. After completing his technical / relevant training he has got involved with a serious amount of projects. He successfully managed database upgrade, database migration, database performance tuning projects for various public institutions.Currently he has been employed by one of the leading financial institutions called Turkiye Hayat & Emeklilik as responsible administrator for Oracle Database and Oracle Middleware. He has been sharing his experience and knowledge by face to face training, personal blog and various social networking accounts to support the Oracle ecosystem continuously since 2010.
