Resolving ORA-24247 and ORA-06512

Resolving ORA-24247 and ORA-06512:
-----------------------------------
When you start working with Oracle 11g rel2 environment.You might face some issues related to ACL
access control list(extra security layer).One of my developer complained he is not able to send mails from stored procedure
and getting the error as given below:

Connecting to the database TEST_USER_DEV 11g.
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "TEST_USER.SP_GM_INIT_MAIL", line 43
ORA-06512: at line 15

I resolve this error as follows:


Step 1: connect to the Database and create a procedure as given below:
--------

SQL> select name from v$database;

NAME
---------
TEST_DB

SQL> set serveroutput on
SQL> create or replace procedure mailserver_acl(
aacl varchar2,
acomment varchar2,
aprincipal varchar2,
aisgrant boolean,
aprivilege varchar2,
aserver varchar2,
aport number)
is
begin
begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL(aacl);
dbms_output.put_line('ACL dropped.....');
exception
when others then
dbms_output.put_line('Error dropping ACL: '||aacl);
dbms_output.put_line(sqlerrm);
end;
begin
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(aacl,acomment,aprincipal,aisgrant,aprivilege);
dbms_output.put_line('ACL created.....');
exception
when others then
dbms_output.put_line('Error creating ACL: '||aacl);
dbms_output.put_line(sqlerrm);
end;
begin
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(aacl,aserver,aport);
dbms_output.put_line('ACL assigned.....');
exception
when others then
dbms_output.put_line('Error assigning ACL: '||aacl);
dbms_output.put_line(sqlerrm);
end;
commit;
dbms_output.put_line('ACL commited.....');
end;
/

Procedure created.


SQL> show errors
No errors.

Step 2: create an ACL as given below:
--------

SQL> begin
mailserver_acl(
'mailserver_acl.xml',
'ACL for used Email Server to connect',
'TEST_USER',
TRUE,
'connect',
'rb-smtp-int.MAIL_SERVER.com',
25);
end;
/
Error dropping ACL: mailserver_acl.xml
ORA-31001: Invalid resource handle or path name "/sys/acls/mailserver_acl.xml"
ACL created.....
ACL assigned.....
ACL commited.....

PL/SQL procedure successfully completed.

Now tell the developer to try and send mail it should work.It is just a short and sweet process
to solve ORA-24247 along with ORA-06512.



Best regards,

Rafi.