CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle UTL_SMTP
Version 11.1
 
General Information
Purpose Send emails from inside the database
Source {ORACLE_HOME}/rdbms/admin/utlsmtp.sql
First Availability 8.1.7
Dependencies
DBMS_AQELM UTL_MAIL WWV_FLOW_MAIL
EMD_NOTIFICATION UTL_SMT_LIB  
PLITBLM UTL_TCP  
Exceptions
Exception Name Error Code Reason
 INVALID_OPERATION 29277 Operation is invalid
 PERMANENT_ERROR 29279 Permanent server error in 500 range
 TRANSIENT_ERROR 29278 Transient server error in 400 range
Record 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);
/

DECLARE
 mail_conn UTL_SMTP.CONNECTION;
BEGIN
  NULL;
END;
/
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;
/

Reply Codes
Reply Code 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 Syntax error, command unrecognized (This may include errors such as command line too long.)
501 Syntax 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 syntax incorrect]
554 Transaction failed
Security Model Execute is granted to PUBLIC
Run as AUTHID CURRENT_USER
Best practice is to revoke execute from PUBLIC
 
CLOSE_DATA
Closes the data session

Function
utl_smtp.close_data(c IN OUT NOCOPY connection) RETURN reply
See Demos below
Closes the data session

Procedure
utl_smtp.close_data(c IN OUT NOCOPY connection);
See Demos below
 
COMMAND
Perform generic SMTP commands

Function
utl_smtp.command(
c   IN connection,
cmd IN VARCHAR2,
arg IN VARCHAR2 DEFAULT NULL) RETURN reply;
TBD
Perform generic SMTP commands

Procedure
utl_smtp.command(
c   IN connection,
cmd IN VARCHAR2,
arg IN ARCHAR2 DEFAULT NULL);
TBD
 
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

Function
utl_smtp.data(
c    IN OUT NOCOPY connection
body IN     VARCHAR2 CHARACTER SET ANY_CS)
RETURN reply;
See Demos below
Specifies the body of an e-mail message

Procedure
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

Function
utl_smtp.ehlo(c IN OUT NOCOPY connection, domain IN VARCHAR2)
RETURN replies;
TBD
Performs initial handshaking with SMTP server after connecting

Procedure
utl_smtp.ehlo(c IN OUT NOCOPY connection, domain IN VARCHAR);
TBD
 
HELO
performs initial handshaking with SMTP server after connecting

Function
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);
  utl_smtp.helo(mail_conn, 'UW');
END;
/
performs initial handshaking with SMTP server after connecting

Procedure
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;
TBD
 
MAIL
Initiates a mail transaction with the server

Function
utl_smtp.mail(
c          IN OUT NOCOPY connection,
sender     IN     VARCHAR2,
parameters IN     VARCHAR2 DEFAULT NULL)
RETURN reply;
See Demos below
Initiates a mail transaction with the server

Procedure
utl_smtp.mail(
c          IN OUT NOCOPY connection,
sender     IN     VARCHAR2,
parameters IN     VARCHAR2 DEFAULT NULL);
See Demos below
 
NOOP
NULL Command

Function
utl_smtp.noop(c IN OUT NOCOPY connection) RETURN reply;
TBD
NULL Command

Procedure
utl_smtp.noop(c IN OUT NOCOPY connection);
TBD
 
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)
RETURN reply;
See Demos below
Opens a connection to an SMTP server

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)
RETURN connection;
See Demos below
 
OPEN_DATA
Sends the DATA command

Function
utl_smtp.open_data(c IN OUT NOCOPY connection) RETURN reply;
See Demos below
Sends the DATA command

Procedure
utl_smtp.open_data(c IN OUT NOCOPY connection);
See Demos below
 
QUIT
Terminates an SMTP session and disconnects from the server

Function
utl_smtp.quit(c IN OUT NOCOPY connection) RETURN reply;
See Demos below
Terminates an SMTP session and disconnects from the server

Procedure
utl_smtp.quit(c IN OUT NOCOPY connection);
See Demos below
 
RCPT
Specifies the recipient of an e-mail message

Function
utl_smtp.rcpt(
c          IN OUT NOCOPY connection,
recipient  IN     VARCHAR2,
parameters IN     VARCHAR2 DEFAULT NULL)
RETURN reply;
See Demos below
Specifies the recipient of an e-mail message

Procedure
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

Function
utl_smpt.rset(c IN OUT NOCOPY connection) RETURN reply;
TBD
Aborts the current mail transaction

Procedure
utl_smpt.rset(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
 
UTL_SMTP 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
    NULL;
  WHEN TRANSIENT_ERROR THEN
    NULL;
  WHEN PERMANENT_ERROR THEN
    NULL;
  WHEN OTHERS THEN
    NULL;
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.psoug.org');
  utl_smtp.helo(c, 'psoug.org');
  utl_smtp.mail(c, 'mailsys@psoug.com');
  utl_smtp.rcpt(c, 'recipient@oracle.com');
  utl_smtp.open_data(c);
  send_header('From', '"Sender" <sender@psoug.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
UTL_MAIL
UTL_TCP
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [60 users online]    © 2010 psoug.org