Inviare email dal DB utilizzando UTL_SMTP

This article is also available in English here.

Voglio mostrare brevemente come si può utilizzare il package UTL_SMTP per inviare email da un programma PL/SQL.
Partiamo subito con un esempio:

CREATE OR REPLACE PROCEDURE SENDMAIL (dest in varchar2,msg in varchar2) is
    mailhost    VARCHAR2(10) := 'localhost';
    sender      VARCHAR2(20) := 'massimo@massimo.it';
    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;
/ 

La procedura riceve in input l’indirizzo del destinatario ed il messaggio da inviare.
Analizziamola passo per passo:

Innanzitutto viene definito il server smtp da utilizzare. Nel mio caso ho installato un smtp server locale e dunque l’indirizzo è banalmente localhost.
Poi ho cablato nel codice l’indirizzo del mittente.
La prima chiamata è l’apertura della connessione con il server smtp. E’realizzata mediante la funzione open_connection e ritorna un handler alla connessione che sarà sempre utilizzato di qui in avanti.
La chiamata successiva è il comando ehlo che inizializza la connessione avviando la fase di handshake.
Ci sono due possibili comando ehlo. Quello mostrato in esempio è il più completo perché restituisce in output una PL/SQL table che mostra tutti le caratteristiche ricevuta dal server all’handshake.
Dopo averle ricevute le stampo a video con delle DBMS_OUTPUT. L’altra possibilità era utilizzare il comando

utl_smtp.helo(mail_conn, mailhost);

Che è identico al precedente ma non ha output.

Dopo avere fatto la conoscenza col server utilizziamo il comando MAIL per avviare il processo di invio di una email.
Il successivo comando RCPT seleziona la mailbox in cui desideriamo depositare il nostro messaggio. Questo comando può essere ripetuto più volte se i destinatari del messaggio sono più di uno.
Il comando OPEN_DATA apre la session di scrittura del corpo dell’email.
Dopo la OPEN_DATA è possibile eseguire un numero a piacere di comandi WRITE_DATA chiusi dal comando COLSE_DATA.
Nel nostro caso passiamo con write successive ciò che sarà visualizzato nei campi: From, Subject e To dell’email.
La riga vuota successiva serve a chiudere l’header dell’email ed a passare al corpo effettivo.
Anche nel corpo potremmo scrivere un numero a piacere di righe, noi accettiamo il testo come parametro di input.
Come detto completiamo l’invio con il comando CLOSE_DATA e salutiamo il server con il comando QUIT.

Eseguiamo la procedura:

SQL> exec SENDMAIL('massimo.ruocchio@mymail.it','Caro amico ti scrivo...')
BEGIN SENDMAIL('massimo.ruocchio@mymail.it','Caro amico ti scrivo...'); END;

*
ERRORE alla riga 1:
ORA-24247: accesso alla rete negato dalla lista di controllo dell'accesso (ACL)
ORA-06512: a "SYS.UTL_TCP", line 17
ORA-06512: a "SYS.UTL_TCP", line 246
ORA-06512: a "SYS.UTL_SMTP", line 115
ORA-06512: a "SYS.UTL_SMTP", line 138
ORA-06512: a "MAXR.SENDMAIL", line 7
ORA-06512: a line 1

L’errore, che si verificherà solo se state utilizzando Oracle11g, è dovuto ad una configurazione di sicurezza di Oracle. Le Access Control List sono liste che controllano la possibilità da parte di un programma PL/SQL di accedere a risorse esterne.
In pratica prima che un programma PL/SQL possa accedere ad una risorsa esterna bisogna concedergliene l’utilizzo.
Bisogna creare una ACL specifica per la risorsa SMTP ed associarla al nostro 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;
/

Con la prima istruzione ho creato l’ACL che viene conservata in un documento xml (SMTP_ACL.xml) e l’ho associata all’utente oracle MAXR concedendo a questo la grant di connect alla risorsa.
Con la seconda istruzione specifico quale risorsa (localhost nel mio caso) deve essere accessibile mediante l’ACL appena definita e su quale range di porte.
Fatta questa autorizzazione riprovo a mandare la mia email:

SQL> exec  SENDMAIL('massimo.ruocchio@mymail.it','Caro amico ti scrivo...')
helo code=250 text=Hello localhost
helo code=250 text=AUTH LOGIN
helo code=250 text=ENHANCEDSTATUSCODES
helo code=250 text=PIPELINING

Procedura PL/SQL completata correttamente.

L’email è correttamente arrivata a destinazione.

Per concludere una nota importante: Fino ad Oracle11gR1 UTL_SMTP non supporta connessioni SSL, quindi se volete utilizzare i server SMTP più diffusi come gmail (che appunto richiedono una connessione SSL) dovete utilizzare un server locale (ad esempio stunnel) a cui connettervi in modalità standard usando UTL_SMTP e che sia configurato in modo da girare le connessioni al server finale (ad esempio gmail) in modalità SSL.

Non ho avuto modo di provarlo ma in Oracle 11gR2 UTL_SMTP è stato implementato per gestire anche l’SSL. C’è la funzione/procedura STARTTLS:

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.

Syntax

UTL_SMTP.STARTTLS (c IN OUT NOCOPY connection) RETURN reply;

UTL_SMTP.STARTTLS (c IN OUT NOCOPY connection);

Parameters
c SMTP connection

Return Values
reply SMTP reply

Usage Notes

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.

Examples

DECLARE
c utl_smtp.connection;
BEGIN
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);
utl_smtp.starttls(c);
END

Alla prossima,
Massimo