| General Information |
| Purpose |
Send emails from inside the database. For demos written by Bryn Llewellyn [click here]. |
| Source |
{ORACLE_HOME}/rdbms/admin/utlsmtp.sql |
| First Availability |
8.1.7 |
| Constants |
| Constant Name |
Data Type |
Value |
| ALL_SCHEMES |
VARCHAR2(80) |
'CRAM-MD5 PLAIN LOGIN' |
| NON_CLEARTEXT_PASSWORD_SCHEMES |
VARCHAR2(80) |
'CRAM-MD5' |
|
| Dependencies |
| DBMS_AQELM |
PLITBLM |
UTL_RAW |
UTL_TCP |
| DBMS_ISCHED |
UTL_ENCODE |
UTL_SMT_LIB |
WWV_FLOW_MAIL |
|
| Exceptions |
| Exception Name |
Error Code |
Reason |
| INVALID_OPERATION |
29277 |
Operation is invalid |
| NO_SUPPORTED_SCHEME |
24250 |
No supported authentication scheme |
| PERMANENT_ERROR |
29279 |
Permanent server error in 500 range |
| TRANSIENT_ERROR |
29278 |
Transient server error in 400 range |
| UNSUPPORTED_SCHEME |
24249 |
Unsupported authentication scheme |
|
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);
/ |
| 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 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 |
Execute is granted to PUBLIC
Run as AUTHID CURRENT_USER
Best practice is to REVOKE EXECUTE from PUBLIC and grant on an as-need basis to specific application owners only |
| Subprograms |
|
| |
AUTH (11.2.0.2)  |
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 (11.2.0.1) |
| 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) := 'damorgan11g';
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;
/ |
| |
| 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; |
| TBD |
| Overload 2 |
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,
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 (11.2.0.2) |
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 |
| |
| 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
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;
/ |
