How to send mail using PL/SQL

First, You must have mail server and port 25 should be open.
You can check if the port is open with the telnet command as follows:
telnet 192.10.1.160 25
Second, You need to create access control list (ACL) in oracle database 11g.
In 10g, you do not have to do that.
Oracle Database 12c has deprecated many of the procedures and functions in the DBMS_NETWORK_ACL_ADMIN package.
Although deprecated, the old functionality is retained for backwards compatibility.
You can use access control entry (ACE) in 12c.

CREATE OR REPLACE PROCEDURE SYSTEM.TEST_SEND_MAIL
IS

  time_str       varchar2(20) ;
  c             utl_smtp.connection;
  v_line        varchar2(4000);
  v_status      number ;
  n             number := 1;
  err_msg       varchar2(4000);

BEGIN

   dbms_output.enable(1000000);
   dbms_output.put_line('');
   dbms_output.put_line('-----------------start-----------------------');



   select to_char(sysdate,'yyyymmdd hh:mi:ss') into time_str from dual ;
   dbms_output.put_line( time_str) ;

   dbms_output.put_line('--------------------end---------------------');
 
   c := utl_smtp.open_connection('192.10.1.160');  -- mail server ip

   utl_smtp.mail(c, 'jeniffer@abc.com');
   utl_smtp.rcpt(c, 'scott@abc.com');
   utl_smtp.rcpt(c, 'stella@abc.com');

  utl_smtp.open_data(c);
  utl_smtp.write_raw_data(c, UTL_RAW.CAST_TO_RAW('From: jeniffer@abc.com'||chr(13)||chr(10)));
  utl_smtp.write_raw_data(c, UTL_RAW.CAST_TO_RAW('To: scott@abc.com'||chr(13)||chr(10)));
  utl_smtp.write_raw_data(c, UTL_RAW.CAST_TO_RAW('Subject: this is a test mail'||chr(13)||chr(10)));

  loop

    dbms_output.get_line(v_line, v_status);
    if v_status = 0
      then
        utl_smtp.write_raw_data(c, UTL_RAW.CAST_TO_RAW(v_line||chr(13)||chr(10)));
        n := n + 1;
      else
      exit;
    end if;

  end loop;

  utl_smtp.close_data(c);
  utl_smtp.quit(c);

END ;
/




sql> execute SYSTEM.TEST_SEND_MAIL ;
PL/SQL procedure successfully completed.
  
  

 

In 11g, If you get error messages like the following
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.UTL_TCP”, line 17

you must create acl and assign connect privilege

1. create your custom procedure

connect / as sysdba

set serveroutput on

create or replace procedure test_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;
/

2. create acl and assign privilege on ip 192.10.1.160 port 25 with your custom procedure.

begin
 test_mailserver_acl(
 'mailserver_acl.xml',
 'ACL for used Email Server to connect',
 'SCOTT',
  TRUE,
 'connect',
 '192.10.1.160',
 25);
end;
/

 

Zamong is free  oracle monitoring software with DMA method.

dma_picture