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;