Skip to end of metadata
Go to start of metadata

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;