Knowledge Base : Send Mail from Oracle

How to send email from an Oracle Database via SMTP

SQL> DECLARE
 c utl_smtp.connection;
 PROCEDURE send_header(name VARCHAR2, header VARCHAR2) AS
 BEGIN
 utl_smtp.write_data(c,name ||':'|| header || UTL_TCP.CRLF);
 END;
 BEGIN
 c := utl_smtp.open_connection('mail.loopback.org');
 utl_smtp.helo(c, 'loopback.org');
 utl_smtp.mail(c, 'test@test.com');
 utl_smtp.rcpt(c, 'test@test.com');
 utl_smtp.open_data(c);
 send_header('From', '"Sender" <test@test.com>');
 send_header('To', '"Recipient" <test@test.com>');
 send_header('Subject', 'Hello');
 utl_smtp.write_data(c, UTL_TCP.CRLF || 'Hello, world!');
 utl_smtp.close_data(c);
 utl_smtp.quit(c);
 EXCEPTION
 WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
 BEGIN
 utl_smtp.quit(c);
 EXCEPTION
 WHEN utl_smtp.transient_error 
 OR utl_smtp.permanent_error THEN
 NULL;
 END;
 raise_application_error(-20000, SQLERRM);
 END;

In case you get ORA-24247: network access denied by access control list (ACL), you have to create ACLs:

begin
 dbms_network_acl_admin.create_acl (
 acl => 'utl_mail.xml',
 description => 'Allow mail to be send',
 principal => 'SCOTT',
 is_grant => TRUE,
 privilege => 'connect'
 );
 commit;
 end;
begin
dbms_network_acl_admin.add_privilege (
 acl => 'utl_mail.xml',
 principal => 'SCOTT',
 is_grant => TRUE,
 privilege => 'resolve'
 );
 commit;
 end;
begin
dbms_network_acl_admin.assign_acl(
 acl => 'utl_mail.xml',
 host => 'smtp server host name or address'
 );
 commit;
 end;