While sending mail using utl_mail or utl_stmp in oracle 11g, you may get access denied error:
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.
Set the SMTP_OUT_SERVER parameter
SQL> alter system set smtp_out_server='mailhost.dbaclass.com' scope=both; System altered.
Now create ACLS
Suppose the user APPUSER want to send mail from procedure.
--- creating ACL as below
exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('send_mail.xml','Allow mail to be send', 'APPUSER', TRUE, 'connect');
commit;
----Grant the connect and resource privilege as below
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('send_mail.xml','APPUSER',TRUE, 'connect');
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('send_mail.xml','APPUSER',TRUE, 'resolve');
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('send_mail.xml','*',25);
commit;
Now try to send mail:
SQL> conn appuser
Enter password:
Connected.
SQL> begin
utl_mail.send(sender => 'admin@dbaclass.com',
recipients => 'admin@dbaclass.com',
subject => 'MAIL from ADMIn of dbaclass',
message => 'Do visit dbaclass');
end;
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.
Good Information . Solved my issue.
You know what? THANK YOU! I spent an hour looking 6 differnt oracle docs/notes, Ask Tom etc on this and was deeply confused! You gave me a simple 5 lines that worked FIRST TRY! Thank you, thank you, THANK YOU! (Ask Tom did not have the two ADD_PRIVILEGE lines!)
Thank you Robert.
Thank you so much for this post!!! I can repeat the same words from Robert above. After spending a couple of hours going through multiple Oracle docs, Your articel solved my issue in a minute.
I cannot thank you enough!!!
Thank u for your kind words Nadine . You can also contribute articles to our knowledgebase , to share knowledge across fellow DBAs.
Unable to send email from 18xe PDB
1) Connected to DB
2) Changed session to XEPDB1
3) Created ACLs like below
exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(‘send_mail.xml’,’Allow mail to be send’, ‘APPUSER’, TRUE, ‘connect’);
commit;
—-Grant the connect and resource privilege as below
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(‘send_mail.xml’,’APPUSER’,TRUE, ‘connect’);
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(‘send_mail.xml’,’APPUSER’,TRUE, ‘resolve’);
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(‘send_mail.xml’,”,587);
4) Code snippet
BEGIN
execute immediate ‘alter session set smtp_out_server=”smtp.office365.com:587”’;
UTL_MAIL.SEND(sender=>’srinivas.p@company.com’,
recipients=>’srinivas.p@company.com’,
subject=>’Test Mail’,
message=>’This is just a test mail’);
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,’The following error:’||sqlerrm);
END;
/
Fixed my issue in 19c database. Thank you so much.