In this post I want to show a simple way to send emails from an oracle database using the PL/SQL package UTL_SMTP.
Let’s start with a simple example:
CREATE OR REPLACE PROCEDURE SENDMAIL (dest in varchar2,msg in varchar2) is mailhost VARCHAR2(10) := 'localhost'; sender VARCHAR2(20) := 'email@example.com'; mail_conn utl_smtp.connection; r utl_smtp.replies; BEGIN mail_conn := utl_smtp.open_connection(mailhost, 25); r:=utl_smtp.ehlo(mail_conn, mailhost); for i in r.first..r.last loop dbms_output.put_line('helo code='||r(i).code||' text='||r(i).text); end loop; utl_smtp.mail(mail_conn, sender); utl_smtp.rcpt(mail_conn, dest); utl_smtp.open_data(mail_conn); utl_smtp.write_data(mail_conn, 'From: '||sender||chr(13)|| CHR(10)); utl_smtp.write_data(mail_conn, 'Subject: '||'messaggio di test'||chr(13)|| CHR(10)); utl_smtp.write_data(mail_conn, 'To: '||dest||chr(13)|| CHR(10)); utl_smtp.write_data(mail_conn, chr(13)|| CHR(10)); utl_smtp.write_data(mail_conn, msg||chr(13)|| CHR(10)); utl_smtp.close_data(mail_conn); utl_smtp.quit(mail_conn); END; /
The procedure gets as inputs the recipient’s address and the message to send.
Let’s analyze it step by step:
First of all, an smtp server to use is declared.
The sender’s address is hard-coded.
The first call opens the connection to the smtp server. To do this I’ve used the open_connection function that returns an handler to the connection. This handler will be always used for the whole program.
The next call is performed to initialize the connection starting the handshake phase.
There are two possible ehlo commands. The one I used in the most complete as it returns a PL/SQL table containing all the server’s abilities.
After receiving them I print them on the screen using DBMS_OUTPUT. The other option was the following procedure:
That’s identical to the previous one but has no output.
Once met the server we use the MAIL procedure to start the email sending process.
RCPT procedure selects the recipient’s mailbox. This procedure can be used more than once if we have more than one recipient.
The OPEN_DATA procedure opens the email’s body writing session.
After OPEN_DATA we can call as many WRITE_DATA as we need closed from a single call to COLSE_DATA.
In our example we use WRITE_DATA to write the following fields of the email: From, Subject, To.
The empty row closed the email’s header and starts the body.
In the body we could, once again, call WRITE_DATA as many times as we need. We get the text as an input parameter.
The send procedure is completed by the call to CLOSE_DATA, then we leave the server using QUIT.
Let’s run the procedure:
SQL> exec SENDMAIL('firstname.lastname@example.org','Dear friend, I''m writing...') BEGIN SENDMAIL('email@example.com','Dear friend, I''m writing...'); END; * ERROR at line 1: ORA-24247: network access denied by access control list (ACL) ORA-06512: at "SYS.UTL_TCP", line 17 ORA-06512: at "SYS.UTL_TCP", line 267 ORA-06512: at "SYS.UTL_SMTP", line 161 ORA-06512: at "SYS.UTL_SMTP", line 197 ORA-06512: at "SCOTT.SENDMAIL", line 7 ORA-06512: at line 1
This error will be thrown only in Oracle11g, due to security configuration. Access Control Lists are lists that check whether a PL/SQL program can access critical external resources or not.
We must grant to the program the right to use resource.
Let’s create a specific ACL for the SMTP resource and let’s associate it to our server:
BEGIN DBMS_NETWORK_ACL_ADMIN.CREATE_ACL ( acl => 'SMTP_ACL.xml', description => 'ACL for SMTP', principal => 'MAXR', is_grant => TRUE, privilege => 'connect', start_date => null, end_date => null); END; / BEGIN DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => 'SMTP_ACL.xml', host => 'localhost', lower_port => 25, upper_port => 25); END; /
With the first statement I created the ACL that is stored in an xml document(SMTP_ACL.xml) then I associated it to the schema MAXR granting to him the right to connect to the resource.
With the second statement I assign the ACL to the resource specifying the range of ports.
Once made this security configuration I try again:
SQL> exec SENDMAIL('firstname.lastname@example.org','Dear friend, I''m writing...') helo code=250 text=Hello localhost helo code=250 text=AUTH LOGIN helo code=250 text=ENHANCEDSTATUSCODES helo code=250 text=PIPELINING
The email has successful reached the destination.
SSL connection are supported since Oracle11gR2 using STARTTLS:
(for Oracle’s docs)
STARTTLS Function and Procedure
This subprogram sends the STARTTLS command to secure the SMTP connection using SSL/TLS. SSL/TLS requires an Oracle wallet which must be specified when the connection was opened by the OPEN_CONNECTION Functions.
UTL_SMTP.STARTTLS (c IN OUT NOCOPY connection) RETURN reply;
UTL_SMTP.STARTTLS (c IN OUT NOCOPY connection);
c SMTP connection
reply SMTP reply
The STARTTLS command must only be issued on an unencrypted connection and when the SMTP server indicates the support of the command in the reply of the EHLO command. The wallet to be used for encryption must have been specified when the initial SMTP connection was opened by the OPEN_CONNECTION function.
c := utl_smtp.open_connection(
host => ‘smtp.example.com’,
port => 25,
wallet_path => ‘file:/oracle/wallets/smtp_wallet’,
wallet_password => ‘password’,
secure_connection_before_smtp => FALSE);