3 Replies Latest reply: Jan 22, 2013 7:59 AM by Srini Chavali-Oracle RSS

    SMTP Mail sending Utility failure

    Madesh
      I have a scheduled procedure that trigger mails to user using SMTP packages. It was working fine quite a period when we were with Oracle 10g and its not working now when we upgrade to oracle 11g. It throws the below error when we execute the procedure.

      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 246
      ORA-06512: at "SYS.UTL_SMTP", line 127
      ORA-06512: at "SYS.UTL_SMTP", line 150

      Would like to have your expertise advise. Thanks in Advance
        • 1. Re: SMTP Mail sending Utility failure
          dataseven
          hi,

          To get this procedure working on Oracle 11g, there are several steps you need to take.

          SQL> @?/rdbms/admin/utlmail.sql

          SQL> @?/rdbms/admin/prvtmail.plb

          SQL> grant execute on utl_mail to public;

          SQL> alter system set smtp_out_server = 'mymailserver@mydomain.com:25' scope=both

          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 => 'mymailserver@mydomain.com',

          lower_port => 25,

          upper_port => 25

          );

          COMMIT;

          END;


          http://matthiashoys.wordpress.com/2012/04/27/ora-06502-ora-24247-calling-utl_mail-from-oracle-11gr2/

          regards,
          • 2. Re: SMTP Mail sending Utility failure
            Rob_J
            Hi,

            Similar instructions to resolve the [url http://www.ora00600.com/scripts/11g/UTL_SMTP_ORA-06512.html]ORA-24247 issue. Bear in mind that granting it to PUBLIC means that every user can use it, whereas you might just want a specific user to be able to send mail.

            After that it's common to get the [url http://www.ora00600.com/scripts/databaseconfig/ORA-29278.html]ORA-29278: SMTP transient error: 421 Service not available error message so check that out, too. You'll probably only get this if you haven't already configured some of the parameters required to send mail.

            Hope that helps,
            Rob