Today I came across an error ORA-24247 & ORA-06512. You basically come across this error when the right grants are not provided on UTL_* package to user.
If you google it out, you will find the reason as “Privileges as the CONNECT privilege are granted to a role instead of directly to a user”

This is one of the solution. But it didn’t help in my case. The solution in this scenario was use of AUTHID clause.

Solution:
 
Either define the procedure / function / package as invoker's rights (using AUTHID current_user), then roles are enabled, e.g.:

CREATE PROCEDURE proc_name (.......) AUTHID CURRENT_USER AS
 
(or)
 
Grant the priviledge directly to the user instead of granting it to the role

Whether or not the role is enabled and the privileges take effect depends on the context of where the code is executed (and how). In stored procedures / functions, specifically if definer’s rights is enabled (the default without using the AUTHID clause explicitly), roles are not taken into account at runtime.

Below is the error details:

SQL> set lin 200 pages 999
SQL> begin C_OWNER.mail_test(); end;
  2  /
begin C_OWNER.mail_test(); end;
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 267
ORA-06512: at "SYS.UTL_SMTP", line 161
ORA-06512: at "SYS.UTL_SMTP", line 197
ORA-06512: at "C_OWNER.MAIL_TEST", line 11
ORA-06512: at line 1

Solution 1:

SQL> grant execute on SYS.UTL_SMTP to C_OWNER;

Grant succeeded.

SQL> grant execute on SYS.UTL_TCP to C_OWNER;

Grant succeeded.

SQL> begin C_OWNER.mail_test(); end;
  2  /
begin C_OWNER.mail_test(); end;
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 267
ORA-06512: at "SYS.UTL_SMTP", line 161
ORA-06512: at "SYS.UTL_SMTP", line 197
ORA-06512: at "C_OWNER.MAIL_TEST", line 11
ORA-06512: at line 1

Naaa… This didn’t helped.

SQL> set long 10000000
SQL> SELECT DBMS_METADATA.GET_DDL('PROCEDURE','MAIL_TEST','C_OWNER' ) FROM dual;

DBMS_METADATA.GET_DDL('PROCEDURE','MAIL_TEST','C_OWNER')
--------------------------------------------------------------------------------

  CREATE OR REPLACE PROCEDURE "C_OWNER"."MAIL_TEST" IS
  |
  |
  UTL_SMTP.WRITE_DATA(lv_conn, UTL_TCP.CRLF);
  UTL_SMTP.CLOSE_DATA(lv_conn);
  UTL_SMTP.QUIT(lv_conn);
END;

Redefining Procedure as

SQL>  CREATE or replace PROCEDURE "C_OWNER"."MAIL_TEST" AUTHID CURRENT_USER IS
 |
 |
 UTL_SMTP.WRITE_DATA(lv_conn, UTL_TCP.CRLF);
 UTL_SMTP.CLOSE_DATA(lv_conn);
 UTL_SMTP.QUIT(lv_conn);
 END;
 /

Procedure created.

SQL> begin C_OWNER.MAIL_TEST(); end;
  2  /

PL/SQL procedure successfully completed.

SQL>

Ref: ORA-24247 Calling UTL_SMTP or UTL_HTTP or UTL_TCP in a Stored Procedure when ACL Assigned to a Role (Doc ID 754909.1)

Advertisements