| 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, '[email protected]');
utl_smtp.rcpt(c, '[email protected]');
utl_smtp.open_data(c);
send_header('From', '"Sender" <[email protected]>');
send_header('To', '"Recipient" <[email protected]>');
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;
/ |
