oracle - ACL error when trying to send mail via Oracle UTL_SMTP

Question

I was trying to send an email via oracle utl_smtp, but every time i execute the apex_mail_p.mail procedure i get an ACL error, to be more precise it's the error ORA-24247. But i've created the acl, added the right previliege and also added the host and port to the ACL. I don't understand why it's giving me an error.

This is the code:

    -- create acl
begin
        dbms_network_acl_admin.create_acl (
                acl             => 'gmail.xml',
                description     => 'Normal Access',
                principal       => 'CONNECT',
                is_grant        => TRUE,
                privilege       => 'connect',
                start_date      => null,
                end_date        => null
        );
end;
/

-- add priviliege to acl
begin
  dbms_network_acl_admin.add_privilege ( 
  acl       => 'gmail.xml',
  principal    => 'MY_PROJECT',
  is_grant    => TRUE, 
  privilege    => 'connect', 
  start_date    => null, 
  end_date    => null); 
end;
/
-- assign host, port to acl
begin
  dbms_network_acl_admin.assign_acl (
  acl => 'gmail.xml',
  host => 'localhost',
  lower_port => 25,
  upper_port => 25);
end;
/



create or replace package apex_mail_p
is
   g_smtp_host      varchar2 (256)     := 'localhost';
   g_smtp_port      pls_integer        := 25;
   g_smtp_domain    varchar2 (256)     := 'gmail.com';
   g_mailer_id constant varchar2 (256) := 'Mailer by Oracle UTL_SMTP';
   -- send mail using UTL_SMTP
   procedure mail (
      p_sender in varchar2
    , p_recipient in varchar2
    , p_subject in varchar2
    , p_message in varchar2
   );
end;
/
create or replace package body apex_mail_p
is
   -- Write a MIME header
   procedure write_mime_header (
      p_conn in out nocopy utl_smtp.connection
    , p_name in varchar2
    , p_value in varchar2
   )
   is
   begin
      utl_smtp.write_data ( p_conn
                          , p_name || ': ' || p_value || utl_tcp.crlf
      );
   end;
   procedure mail (
      p_sender in varchar2
    , p_recipient in varchar2
    , p_subject in varchar2
    , p_message in varchar2
   )
   is
      l_conn           utl_smtp.connection;
      nls_charset    varchar2(255);
   begin
      -- get characterset
      select value
      into   nls_charset
      from   nls_database_parameters
      where  parameter = 'NLS_CHARACTERSET';
      -- establish connection and autheticate
      l_conn   := utl_smtp.open_connection (g_smtp_host, g_smtp_port);
      utl_smtp.ehlo(l_conn, g_smtp_domain);  
      utl_smtp.command(l_conn, 'auth login');
      utl_smtp.command(l_conn,utl_encode.text_encode('[e-mail]', nls_charset, 1));
      utl_smtp.command(l_conn, utl_encode.text_encode('mypassword123', nls_charset, 1));
      -- set from/recipient
      utl_smtp.command(l_conn, 'MAIL FROM: <'||p_sender||'>');
      utl_smtp.command(l_conn, 'RCPT TO: <'||p_recipient||'>');
      -- write mime headers
      utl_smtp.open_data (l_conn);
      write_mime_header (l_conn, 'From', p_sender);
      write_mime_header (l_conn, 'To', p_recipient);
      write_mime_header (l_conn, 'Subject', p_subject);
      write_mime_header (l_conn, 'Content-Type', 'text/plain');
      write_mime_header (l_conn, 'X-Mailer', g_mailer_id);
      utl_smtp.write_data (l_conn, utl_tcp.crlf);
      -- write message body
      utl_smtp.write_data (l_conn, p_message);
      utl_smtp.close_data (l_conn);
      -- end connection
      utl_smtp.quit (l_conn);
   exception
      when others
      then
         begin
           utl_smtp.quit(l_conn);
         exception
           when others then
             null;
         end;
         raise_application_error(-20000,'Failed to send mail due to the following error: ' || sqlerrm);   
   end;
end;
/



begin
   apex_mail_p.mail('[e-mail]', '[e-mail]', 'Test', 'Its only a test');
end;
/

标签： oracleplsqlora-24247

Answer 1

• Replace principal arguments with your database user names,
• Add resolve privilege also,
• Using redundant begin..end block is unnecessary.

So you may consider using the one below :

    -- create acl
    begin
      dbms_network_acl_admin.create_acl (
      acl             => 'gmail.xml',
      description     => 'Normal Access',
      principal       => 'DB_USER',
      is_grant        => TRUE,
      privilege       => 'connect',
      start_date      => null,
      end_date        => null);

    -- add priviliege to acl
      dbms_network_acl_admin.add_privilege ( 
      acl       => 'gmail.xml',
      principal    => 'DB_USER',
      is_grant    => TRUE, 
      privilege    => 'connect', 
      start_date    => null, 
      end_date    => null); 

      dbms_network_acl_admin.add_privilege ( 
      acl       => 'gmail.xml',
      principal    => 'DB_USER',
      is_grant    => TRUE, 
      privilege    => 'resolve', 
      start_date    => null, 
      end_date    => null);       

      -- assign host, port to acl
      dbms_network_acl_admin.assign_acl (
      acl => 'gmail.xml',
      host => 'localhost',
      lower_port => 25,
      upper_port => 25);
    end;
    /