Oracle UTL_SMTP
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Send emails from inside the database. For demos written by Bryn Llewellyn [click here].
AUTHID CURRENT_USER
Constants
Name Data Type Value
ALL_SCHEMES VARCHAR2(80) 'CRAM-MD5 PLAIN LOGIN'
NON_CLEARTEXT_PASSWORD_SCHEMES VARCHAR2(80) 'CRAM-MD5'
Data Types -- connection
TYPE connection IS RECORD(
host            VARCHAR2(255),  -- remote host name
port            PLS_INTEGER,    -- remote port number
tx_timeout      PLS_INTEGER,    -- Transfer time-out (sec.)
private_tcp_con utl_tcp.connection,
private_state   PLS_INTEGER);
/

-- reply & replies
TYPE reply IS RECORD (
code PLS_INTEGER,  -- 3-digit reply code
text VARCHAR2(508) -- text message);
/

TYPE replies IS TABLE OF reply INDEX BY BINARY_INTEGER;
/
Dependencies
DBMS_AQELM UTL_ENCODE UTL_TCP
DBMS_ISCHED UTL_RAW WWV_FLOW_MAIL
PLITBLM UTL_SMT_LIB WWV_FLOW_SECURITY
Documented Yes
Exceptions
Error Code Reason
ORA-24249 UNSUPPORTED_SCHEME: Unsupported authentication scheme
ORA-24250 NO_SUPPORTED_SCHEME: No supported authentication scheme
ORA-29277 INVALID_OPERATION: Operation is invalid
ORA-29278 TRANSIENT_ERROR: Transient server error in 400 range
ORA-29279 PERMANENT_ERROR: Permanent server error in 500 range
First Available 8.1.7
Reply Codes
Codes Description
211 System status, or system help reply
214 Help message [Information on how to use the receiver or the meaning of a particular non-standard command; this reply is useful only to the human user]
220 <domain> Service ready
221 <domain> Service closing transmission channel
250 Requested mail action okay, completed
251 User not local; will forward to <forward-path>
252 OK, pending messages for node <node> started. Cannot VRFY user (for example, info is not local), but will take message for this user and attempt delivery.
253 OK, <messages> pending messages for node <node> started
354 Start mail input; end with <CRLF>.<CRLF>
355 Octet-offset is the transaction offset
421 <domain> Service not available, closing transmission channel (This may be a reply to any command if the service knows it must shut down)
450 Requested mail action not taken: mailbox unavailable [for example, mailbox busy]
451 Requested action terminated: local error in processing
452 Requested action not taken: insufficient system storage
453 You have no mail.
454 TLS not available due to temporary reason. Encryption required for requested authentication mechanism.
458 Unable to queue messages for node <node>
459 Node <node> not allowed: reason
500 synt error, command unrecognized (This may include errors such as command line too long.)
501 synt error in parameters or arguments
502 Command not implemented
503 Bad sequence of commands
504 Command parameter not implemented
521 <Machine> does not accept mail.
530 Must issue a STARTTLS command first. Encryption required for requested authentication.
534 Authentication mechanism is too weak.
538 Encryption required for requested authentication mechanism.
550 Requested action not taken: mailbox unavailable [for mailbox not found, no access]
551 User not local; please try <forward-path>
552 Requested mail action terminated: exceeded storage allocation
553 Requested action not taken: mailbox name not allowed [for example, mailbox synt incorrect]
554 Transaction failed
Security Model Owned by SYS with EXECUTE granted to PUBLIC

Best practice is to REVOKE EXECUTE from PUBLIC and grant on an as-need basis to specific application owners only
Source {ORACLE_HOME}/rdbms/admin/utlsmtp.sql
Subprograms
 
AUTH
Sends AUTH command to authenticate to the SMTP server

Overload 1
utl_smtp.auth(
c        IN OUT NOCOPY connection,
username IN     VARCHAR2,
password IN     VARCHAR2,
schemes  IN     VARCHAR2 DEFAULT NON_CLEARTEXT_PASSWORD_SCHEMES)
RETURN REPLY;
TBD
Overload 2 utl_smtp.auth(
c        IN OUT NOCOPY connection,
username IN     VARCHAR2,
password IN     VARCHAR2,
schemes  IN     VARCHAR2 DEFAULT NON_CLEARTEXT_PASSWORD_SCHEMES);
TBD
 
CLOSE_CONNECTION
Closes the SMTP connection. Use to abort current SMTP operation mid-session: Otherwise use quit. utl_smtp.close_connection(c IN OUT NOCOPY connection);
See SEND EMAIL Demo below
 
CLOSE_DATA
Closes the data session
Overload 1
utl_smtp.close_data(c IN OUT NOCOPY connection) RETURN reply;
See Demos below
Overload 2 utl_smtp.close_data(c IN OUT NOCOPY connection);
See Demos below
 
COMMAND
Perform generic SMTP commands

Overload 1
utl_smtp.command(c IN connection, cmd IN VARCHAR2, arg IN VARCHAR2 DEFAULT NULL) RETURN reply;
TBD
Overload 2 utl_smtp.command(
c   IN connection,
cmd IN VARCHAR2,
arg IN VARCHAR2 DEFAULT NULL);
PROCEDURE send_email(
 pFromUser IN VARCHAR2,
 pToUser   IN VARCHAR2,
 pSubject  IN VARCHAR2 DEFAULT NULL,
 pBody     IN VARCHAR2 DEFAULT NULL) IS

 conn          UTL_SMTP.CONNECTION;
 crlf          VARCHAR2(2)    := CHR(13) || CHR(10);
 EmailServer   VARCHAR2(60)   := 'smtp.mlib.org';
 mesg          VARCHAR2(4000) := 'Hello World';
 pwd           VARCHAR2(200)  := 'sEcReT';
 SenderAddress VARCHAR2(200)  := '<sender@domain.com>';
 SenderName    VARCHAR2(50)   := 'damorgan12c';
 pToList       VARCHAR2(4000);
 vToReceivers  VARCHAR2(200);
 uname         VARCHAR2(200);
BEGIN
  conn := utl_smtp.open_connection(EmailServer, 25);
  utl_smtp.ehlo(conn, EmailServer);
  --utl_smtp.helo( conn, EmailServer );

  utl_smtp.command(conn, 'AUTH LOGIN');
  utl_smtp.command(conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode( utl_raw.cast_to_raw(uname))));
  utl_smtp.command(conn, utl_raw.cast_to_varchar2(
    utl_encode.base64_encode( utl_raw.cast_to_raw(pwd))));
  utl_smtp.mail(conn, SenderAddress);
  utl_smtp.rcpt(conn, pToList);

  mesg:= 'Date: '|| TO_CHAR(SYSDATE, 'DD MON RR HH24:MI:SS' )|| crlf ||
         'From: "' || SenderName || '" ' || SenderAddress || crlf ||
         'Subject: ' || pSubject || crlf ||
         'To: '|| pToList || crlf||
         pBody || crlf || crlf;

  utl_smtp.data(conn, mesg);
  utl_smtp.quit(conn);
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001,SQLERRM);
END send_email;
/
 
COMMAND_REPLIES
Perform generic SMTP commands utl_smtp.command_replies(
c   IN connection,
cmd IN VARCHAR2,
arg IN VARCHAR2 DEFAULT NULL) RETURN REPLIES;
TBD
 
DATA
Specifies the body of an e-mail message

Overload 1
utl_smtp.data(
c    IN OUT NOCOPY connection
body IN     VARCHAR2 CHARACTER SET ANY_CS)
RETURN reply;
See Demos Below
Overload 2 utl_smtp.data(
c    IN OUT NOCOPY connection
body IN     VARCHAR2 CHARACTER SET ANY_CS);
See Demos Below
 
EHLO
Performs initial handshaking with SMTP server after connecting

Overload 1
utl_smtp.ehlo(c IN OUT NOCOPY connection, domain IN VARCHAR2) RETURN REPLIES;
DECLARE
 conn utl_smtp.connection;
 rply utl_smtp.reply;
BEGIN
  rply := utl_smtp.open_connection('mail.uw.edu', 25, conn);
  rply := utl_smtp.ehlo(conn, 'UW');
  utl_smtp.quit(conn);
END;
/
Overload 2 utl_smtp.ehlo(c IN OUT NOCOPY connection, domain IN VARCHAR);
DECLARE
 conn utl_smtp.connection;
 rply utl_smtp.reply;
BEGIN
  rply := utl_smtp.open_connection('mail.uw.edu', 25, conn);
  utl_smtp.ehlo(conn, 'UW');
  utl_smtp.quit(conn);
END;
/
 
HELO
Performs initial handshaking with SMTP server after connecting

Overload 1
utl_smtp.helo(c IN OUT NOCOPY connection, domain IN VARCHAR2) RETURN REPLY;
DECLARE
 conn utl_smtp.connection;
 rply utl_smtp.reply;
BEGIN
  rply := utl_smtp.open_connection('mail.uw.edu', 25, conn);
  rply := utl_smtp.helo(conn, 'UW');
  utl_smtp.quit(conn);
END;
/
Overload 2 utl_smtp.helo(c IN OUT NOCOPY connection, domain IN VARCHAR2);
See Demos Below
 
HELP
Sends HELP Command utl_smtp.help(
c       IN OUT NOCOPY connection,
command IN            VARCHAR2 DEFAULT NULL)
RETURN replies;
DECLARE
 conn  utl_smtp.connection;
 rplys utl_smtp.replies;
BEGIN
  conn := utl_smtp.open_connection('mail.uw.edu', 25, conn);
  rplys := utl_smtp.help(conn, 'AUTH LOGIN');

  FOR i IN rplys.FIRST .. rplys.LAST LOOP
    dbms_output.put_line(rplys);
  END LOOP;

  utl_smtp.quit(conn);
END;
/
 
MAIL
Initiates a mail transaction with the server

Overload 1
utl_smtp.mail(
c          IN OUT NOCOPY connection,
sender     IN     VARCHAR2,
parameters IN     VARCHAR2 DEFAULT NULL)
RETURN reply;
See Demos Below
Overload 2 utl_smtp.mail(
c          IN OUT NOCOPY connection,
sender     IN     VARCHAR2,
parameters IN     VARCHAR2 DEFAULT NULL);
See Demos Below
 
NOOP
No operation. A call that can be used for testing that does precisely nothing.
Overload 1
utl_smtp.noop(c IN OUT NOCOPY connection) RETURN reply;
DECLARE
 eServer VARCHAR2(60) := 'smtp.mlib.org';
 conn    utl_smtp.connection;
 rply    utl_smtp.reply;
BEGIN
  conn := utl_smtp.open_connection(eServer, 25);
  rply := utl_smtp.noop(conn);
END;
/
Overload 2 utl_smtp.noop(c IN OUT NOCOPY connection);
DECLARE
 eServer VARCHAR2(60) := 'smtp.mlib.org';
 conn    utl_smtp.connection;
BEGIN
  conn := utl_smtp.open_connection(eServer, 25);
  utl_smtp.noop(conn);
END;
/
 
OPEN_CONNECTION
Opens a connection to an SMTP server

Overload 1
utl_smtp.open_connection(
host                          IN  VARCHAR2,
port                          IN  PLS_INTEGER DEFAULT 25,
c                             OUT connection,
tx_timeout                    IN  PLS_INTEGER DEFAULT NULL,
wallet_path                   IN  VARCHAR2    DEFAULT NULL,
wallet_password               IN  VARCHAR2    DEFAULT NULL,
secure_connection_before_smtp IN  BOOLEAN     DEFAULT FALSE)
RETURN reply;
See Demos Below
Overload 2 utl_smtp.open_connection(
host                          IN  VARCHAR2,
port                          IN  PLS_INTEGER DEFAULT 25,
c                             OUT connection,
tx_timeout                    IN  PLS_INTEGER DEFAULT NULL,
wallet_path                   IN  VARCHAR2    DEFAULT NULL,
wallet_password               IN  VARCHAR2    DEFAULT NULL,
secure_connection_before_smtp IN  BOOLEAN     DEFAULT FALSE)
RETURN connection;
See Demos Below
 
OPEN_DATA
Sends the DATA command
Overload 1
utl_smtp.open_data(c IN OUT NOCOPY connection) RETURN reply;
See Demos Below
Overload 2 utl_smtp.open_data(c IN OUT NOCOPY connection);
See Demos Below
 
QUIT
Terminates an SMTP session and disconnects from the server
Overload 1
utl_smtp.quit(c IN OUT NOCOPY connection) RETURN reply;
See Demos Below
Overload 2 utl_smtp.quit(c IN OUT NOCOPY connection);
See Demos Below
 
RCPT
Specifies the recipient of an e-mail message

Overload 1
utl_smtp.rcpt(
c          IN OUT NOCOPY connection,
recipient  IN     VARCHAR2,
parameters IN     VARCHAR2 DEFAULT NULL)
RETURN reply;
See Demos Below
Overload 2 utl_smtp.rcpt(
c          IN OUT NOCOPY connection
recipient  IN     VARCHAR2,
parameters IN     VARCHAR2 DEFAULT NULL);
See Demos Below
 
RSET
Aborts the current mail transaction
Overload 1
utl_smpt.rset(c IN OUT NOCOPY connection) RETURN reply;
TBD
Overload 2 utl_smpt.rset(c IN OUT NOCOPY connection);
TBD
 
STARTTLS
Sends the STARTTLS command to secure the SMTP connection using SSL/TLS
Overload 1
utl_smtp.starttls(c IN OUT NOCOPY connection) RETURN REPLY;
TBD
Overload 2 utl_smtp.starttls(c IN OUT NOCOPY connection);
TBD
 
VRFY
Verifies the validity of a destination e-mail address utl_smtp.vrfy(c IN OUT NOCOPY connection, recipient IN VARCHAR2) RETURN reply;
TBD
 
WRITE_DATA
Writes a portion of the e-mail message utl_smtp.write_data(c IN OUT NOCOPY connection, data IN VARCHAR2 CHARACTER SET ANY_CS);
See Demos Below
 
WRITE_RAW_DATA
Writes a portion of the e-mail message with RAW data utl_smtp.write_raw_data(c IN OUT NOCOPY connection, data IN RAW);
See Demos Below
 
Demo
Enable Java In The Database To enable UTL_SMTP in the database java must be enabled run

$ORACLE_HOME/javavm/install/initjvm.sql
$ORACLE_HOME/javavm/install/init_jis.sql
$ORACLE_HOME/rdbms/admin/initplsj.sql
Send Email CREATE OR REPLACE PROCEDURE send_mail (
pSender    VARCHAR2,
pRecipient VARCHAR2,
pSubject   VARCHAR2,
pMessage   VARCHAR2) IS

mailhost  CONSTANT VARCHAR2(30) := 'smtp01.us.oracle.com';
crlf      CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
mesg      VARCHAR2(1000);
mail_conn utl_smtp.connection;

BEGIN
   mail_conn := utl_smtp.open_connection(mailhost, 25);

   mesg := 'Date: ' ||
        TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf ||
           'From: <'|| pSender ||'>' || crlf ||
           'Subject: '|| pSubject || crlf ||
           'To: '||pRecipient || crlf || '' || crlf || pMessage;

   utl_smtp.helo(mail_conn, mailhost);
   utl_smtp.mail(mail_conn, pSender);
   utl_smtp.rcpt(mail_conn, pRecipient);
   utl_smtp.data(mail_conn, mesg);
   utl_smtp.quit(mail_conn);
EXCEPTION
  WHEN INVALID_OPERATION THEN
    utl_smtp.close_connection(mail_conn);
  WHEN TRANSIENT_ERROR THEN
    utl_smtp.close_connection(mail_conn);
  WHEN PERMANENT_ERROR THEN
    utl_smtp.close_connection(mail_conn);
  WHEN OTHERS THEN
    utl_smtp.close_connection(mail_conn);
END send_mail;
/
Demo from OTN 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('smtp-server.morganslibrary.org');
  utl_smtp.helo(c, 'morganslibrary.org');
  utl_smtp.mail(c, 'mailsys@morganslibrary.com');
  utl_smtp.rcpt(c, 'recipient@oracle.com');
  utl_smtp.open_data(c);
  send_header('From', '"Sender" <sender@morganslibrary.org>');
  send_header('To', '"Recipient" <recipient@oracle.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;
/

Related Topics
DBMS_NETWORK_ACL_ADMIN
Packages
UTL_ENCODE
UTL_MAIL
UTL_RAW
UTL_SMTP with SSL
UTL_TCP

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved