1 PACKAGE utl_smtp AUTHID CURRENT_USER AS
2
3 /*******************************************************************
4 * OVERVIEW
5 *
6 * This package provides SMTP client-side access functionality in PL/SQL.
7 * With this package, a PL/SQL program can send electronic mails via SMTP.
8 * This package does not allow the PL/SQL program to receive e-mails via
9 * SMTP. The user of this package should be familiar with the SMTP protocol
10 * as defined in RFC 821 and RFC 1869.
11 *
12 * This package is meant to provide an API to SMTP protocol directly. Users
13 * may find it useful to define additional helper routines to encapsulate
14 * the interaction with a SMTP server.
15 *
16 * USES
17 *
18 * A SMTP connection is initiated by a call to open_connection, which
19 * returns a SMTP connection. After a connection is established, the
20 * following calls are required to send a mail:
21 *
22 * helo() - identify the domain of the sender
23 * mail() - start a mail, specify the sender
24 * rcpt() - specify the recipient
25 * open_data() - start the mail body
26 * write_data() - write the mail body (multiple calls allowed)
27 * close_data() - close the mail body and send the mail
28 *
29 * The SMTP connection is closed by calling quit().
30 *
31 * A note on API style and raising PL/SQL exception:
32 *
33 * Most of the API has a function form and a procedure form. The function
34 * form returns the reply message after the command is sent, in the form
35 * of "XXX <an optional reply message>", where XXX is the reply code.
36 * The procedure form of the same API calls the function form of the API,
37 * checks the reply code and raises transient_error or permanent_error
38 * exception if the reply code is in 400 or 500 range. The function form
39 * of the API does not raise either of the 2 exceptions.
40 *
41 * All API may raise invalid_operation exception if it is called in either
42 * of the situations:
43 *
44 * 1. calling API other than write_data(), write_raw_data() or close_data()
45 * after open_data(0 is called, or
46 * 2. calling write_data(), write_raw_data() or close_data() without
47 * first calling open_data()
48 *
49 * EXAMPLES
50 * Retrieve the home page from http://www.acme.com/
51 *
52 * DECLARE
53 * c utl_smtp.connection;
54 *
55 * PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
56 * BEGIN
57 * utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF);
58 * END;
59 *
60 * BEGIN
61 * c := utl_smtp.open_connection('smtp-server.acme.com');
62 * utl_smtp.helo(c, 'foo.com');
63 * utl_smtp.mail(c, '[email protected]');
64 * utl_smtp.rcpt(c, '[email protected]');
65 * utl_smtp.open_data(c);
66 * send_header('From', '"Sender" [email protected]>');
67 * send_header('To', '"Recipient" [email protected]>');
68 * send_header('Subject', 'Hello');
69 * utl_smtp.write_data(c, utl_tcp.CRLF || 'Hello, world!');
70 * utl_smtp.close_data(c);
71 * utl_smtp.quit(c);
72 * EXCEPTION
73 * WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
74 * BEGIN
75 * utl_smtp.quit(c);
76 * EXCEPTION
77 * WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
78 * NULL; -- When the SMTP server is down or unavailable, we don't
79 * -- have a connection to the server. The quit call will
80 * -- raise an exception that we can ignore.
81 * END;
82 * raise_application_error(-20000,
83 * 'Failed to send mail due to the following error: ' || sqlerrm);
84 * END;
85 */
86
87 /*
88 * SMTP connection type
89 */
90 TYPE connection IS RECORD (
91 host VARCHAR2(255), -- Host name of SMTP server
92 port PLS_INTEGER, -- Port number of SMTP server
93 tx_timeout PLS_INTEGER, -- Transfer time-out (in seconds)
94 private_tcp_con utl_tcp.connection, -- For internal use only
95 private_state PLS_INTEGER -- For internal use only
96 );
97
98 /*
99 * SMTP reply structure
100 */
101 TYPE reply IS RECORD (
102 code PLS_INTEGER, -- 3-digit reply code
103 text VARCHAR2(508) -- reply text
104 );
105 -- multiple reply lines
106 TYPE replies IS TABLE OF reply INDEX BY BINARY_INTEGER;
107
108 /*
109 * Exceptions
110 */
111 invalid_operation EXCEPTION; -- Operation is invalid
112 transient_error EXCEPTION; -- Transient server error in 400 range
113 permanent_error EXCEPTION; -- Permanent server error in 500 range
114 unsupported_scheme EXCEPTION; -- Unsupported authentication scheme
115 no_supported_scheme EXCEPTION; -- No supported authentication scheme
116 invalid_operation_errcode CONSTANT PLS_INTEGER:= -29277;
117 transient_error_errcode CONSTANT PLS_INTEGER:= -29278;
118 permanent_error_errcode CONSTANT PLS_INTEGER:= -29279;
119 unsupported_scheme_errcode CONSTANT PLS_INTEGER:= -24249;
120 no_supported_scheme_errcode CONSTANT PLS_INTEGER:= -24250;
121 PRAGMA EXCEPTION_INIT(invalid_operation, -29277);
122 PRAGMA EXCEPTION_INIT(transient_error, -29278);
123 PRAGMA EXCEPTION_INIT(permanent_error, -29279);
124 PRAGMA EXCEPTION_INIT(unsupported_scheme, -24249);
125 PRAGMA EXCEPTION_INIT(no_supported_scheme, -24250);
126
127 /*
128 * SMTP authentication schemes for use with the AUTH API.
129 *
130 * UTL_SMTP may support additional authentication schemes in the future and
131 * these strings will change accordingly.
132 */
133
134 /* The list of all authentication schemes that UTL_SMTP supports, in the
135 * order of their relative security strength. This list contains some
136 * schemes in which cleartext passwords are sent over SMTP. They should be
137 * used only in SMTP connections that are secured by Secure Scoket Layer /
138 * Transport Layer Security (SSL/TLS).
139 */
140 ALL_SCHEMES CONSTANT VARCHAR2(80) := 'CRAM-MD5 PLAIN LOGIN';
141
142 /* The list of authentication schemes that UTL_SMTP supports and in which
143 * no cleartext passwords are sent over SMTP. They may be used in SMTP
144 * connections that are not secured by SSL/TLS. Note that these schemes
145 * may still be weak when used in an insecure SMTP connection.
146 */
147 NON_CLEARTEXT_PASSWORD_SCHEMES CONSTANT VARCHAR2(80) := 'CRAM-MD5';
148
149 /**
150 * Opens a SMTP connection to a SMTP server. When connection is made
151 * succesfully, the SMTP host name and port number will be stored in
152 * the connection.
153 *
154 * Communication with the SMTP server may be secured using Secure Socket
155 * Layer / Transport Layer Security (SSL/TLS). To start SSL/TLS in the
156 * connection, the user must specify an Oracle wallet when opening the
157 * connection. The wallet must contain the trusted certificate of the
158 * certificate authority who signs the remote server's certificate for
159 * UTL_SMTP to validate the remote server's certificate.
160 *
161 * There are two ways SSL/TLS may be started in SMTP. In the first way,
162 * SSL/TLS is started before any SMTP communication. To connect to a SMTP
163 * server port configured this way (port 465 by convention), set
164 * secure_connection_before_smtp to TRUE.
165 *
166 * In the second way, initial SMTP handshake is done in an insecure
167 * connection and SSL/TLS is started by the STARTTLS command. To connect to
168 * a SMTP server port configured this way (port 25 or 587 by convention),
169 * set secure_connection_before_smtp to FALSE and call the STARTTLS
170 * function/procedure. See STARTTLS for details.
171 *
172 * UTL_SMTP supports client authentication over SSL/TLS using the client
173 * certificate in the wallet. The client certificate will be sent to the
174 * remote server if it is present in the wallet and if the caller has the
175 * "use-client-certificates" ACL privilege on the wallet.
176 *
177 * For more information on Oracle wallets, see Oracle Wallet Manager
178 * documentation.
179 *
180 * PARAMETERS
181 * host SMTP host name to connect to
182 * port port number of the SMTP server to connect to
183 * c SMTP connection (OUT)
184 * tx_timeout a time in seconds this package should wait before
185 * giving up in a read or write operation in this
186 * connection. In read operations, this package will give
187 * up if no data is available for reading immediately.
188 * In write operations, this package will give if the
189 * output buffer is full and no data be sent in the
190 * network without being blocked. 0 indicates not to wait
191 * at all. NULL indicates to wait forever.
192 * wallet_path the directory path that contains the Oracle wallet for
193 * SSL/TLS. The format is "file:<directory-path>".
194 * wallet_password the password to open the wallet. When the wallet is
195 * auto-login enabled, the password may be set to NULL.
196 * secure_connection_before_smtp if TRUE, secure the connection with
197 * SSL/TLS before SMTP communication, else not.
198 * RETURN
199 * SMTP connection when connection is established, or
200 * the SMTP reply (welcome) message
201 * EXCEPTIONS
202 * transient_error - transient server error
203 * permanent_error - permanent server error
204 * invalid_operation - invalid operation
205 * NOTES
206 * Time-out on write operations is not supported in the current release
207 * of this package.
208 */
209 FUNCTION open_connection(host IN VARCHAR2,
210 port IN PLS_INTEGER DEFAULT 25,
211 c OUT connection,
212 tx_timeout IN PLS_INTEGER DEFAULT NULL,
213 wallet_path IN VARCHAR2 DEFAULT NULL,
214 wallet_password IN VARCHAR2 DEFAULT NULL,
215 secure_connection_before_smtp IN BOOLEAN
216 DEFAULT FALSE)
217 RETURN reply;
218 FUNCTION open_connection(host IN VARCHAR2,
219 port IN PLS_INTEGER DEFAULT 25,
220 tx_timeout IN PLS_INTEGER DEFAULT NULL,
221 wallet_path IN VARCHAR2 DEFAULT NULL,
222 wallet_password IN VARCHAR2 DEFAULT NULL,
223 secure_connection_before_smtp IN BOOLEAN
224 DEFAULT FALSE)
225 RETURN connection;
226
227 /**
228 * Sends a generic SMTP command and retrieves a single reply line. If
229 * multiple reply lines are returned from the SMTP server, the last reply
230 * line is returned.
231 *
232 * PARAMETERS
233 * c SMTP connection
234 * cmd SMTP command
235 * arg optional argument to the SMTP command
236 * RETURN
237 * the SMTP reply
238 * EXCEPTIONS
239 * transient_error - transient server error
240 * permanent_error - permanent server error
241 * invalid_operation - invalid operation
242 */
243 FUNCTION command(c IN OUT NOCOPY connection,
244 cmd IN VARCHAR2,
245 arg IN VARCHAR2 DEFAULT NULL)
246 RETURN reply;
247 PROCEDURE command(c IN OUT NOCOPY connection,
248 cmd IN VARCHAR2,
249 arg IN VARCHAR2 DEFAULT NULL);
250
251 /**
252 * Sends a generic SMTP command and retrieves multiple reply lines.
253 *
254 * PARAMETERS
255 * c SMTP connection
256 * cmd SMTP command
257 * arg optional argument to the SMTP command
258 * RETURN
259 * the SMTP reply
260 * EXCEPTIONS
261 * invalid_operation - invalid operation
262 */
263 FUNCTION command_replies(c IN OUT NOCOPY connection,
264 cmd IN VARCHAR2,
265 arg IN VARCHAR2 DEFAULT NULL)
266 RETURN replies;
267
268 /**
269 * Sends HELO command.
270 *
271 * PARAMETERS
272 * c SMTP connection
273 * domain domain of the sender
274 * RETURN
275 * the SMTP reply
276 * EXCEPTIONS
277 * transient_error - transient server error
278 * permanent_error - permanent server error
279 * invalid_operation - invalid operation
280 */
281 FUNCTION helo(c IN OUT NOCOPY connection,
282 domain IN VARCHAR2) RETURN reply;
283 PROCEDURE helo(c IN OUT NOCOPY connection,
284 domain IN VARCHAR2);
285
286 /**
287 * Sends EHLO command.
288 *
289 * PARAMETERS
290 * c SMTP connection
291 * domain domain of the sender
292 * RETURN
293 * the SMTP reply
294 * EXCEPTIONS
295 * transient_error - transient server error
296 * permanent_error - permanent server error
297 * invalid_operation - invalid operation
298 */
299 FUNCTION ehlo(c IN OUT NOCOPY connection,
300 domain IN VARCHAR2) RETURN replies;
301 PROCEDURE ehlo(c IN OUT NOCOPY connection,
302 domain IN VARCHAR2);
303
304 /**
305 * Sends STARTTLS command to secure the SMTP connection using SSL/TLS.
306 * SSL/TLS requires an Oracle wallet which should be specified when the
307 * connection was opened by the open_connection function.
308 *
309 * PARAMETERS
310 * c SMTP connection
311 * RETURN
312 * the SMTP reply
313 * EXCEPTIONS
314 * transient_error - transient server error
315 * permanent_error - permanent server error
316 * invalid_operation - invalid operation
317 */
318 FUNCTION starttls(c IN OUT NOCOPY connection) RETURN reply;
319 PROCEDURE starttls(c IN OUT NOCOPY connection);
320
321 /**
322 * Sends AUTH command to authenticate to the SMTP server. The UTL_SMTP
323 * package will go through the user's choices of authentication schemes,
324 * skip any that is not supported by the SMTP server and use the first one
325 * supported. To determine the schemes the SMTP server supports from its
326 * EHLO reply, the user must call the EHLO function/procedure first.
327 * Otherwise, UTL_SMTP will use the first scheme in the list.
328 *
329 * PARAMETERS
330 * c SMTP connection
331 * username the username
332 * password the password
333 * schemes the list of space-separated authentication schemes to use
334 * in the preferred order. See the ALL_SCHEMES and
335 * NON_CLEARTEXT_PASSWORD_SCHEMES constants for suggestions.
336 * RETURN
337 * the SMTP reply
338 * EXCEPTIONS
339 * transient_error - transient server error
340 * permanent_error - permanent server error
341 * invalid_operation - invalid operation
342 * bad_argument - no supported authentication scheme found
343 * NOTES
344 * Currently only PLAIN, LOGIN and CRAM-MD5 authentication schemes are
345 * supported by UTL_SMTP.
346 * Since the SMTP server may change the authentication schemes it supports
347 * after the SMTP connection is secured by SSL/TLS after the STARTTLS command
348 * (for example, adding PLAIN and LOGIN), the caller should call the EHLO
349 * function/procedure again for UTL_SMTP to update the list after the
350 * STARTTLS function/procedure is called.
351 */
352 FUNCTION auth(c IN OUT NOCOPY connection,
353 username IN VARCHAR2,
354 password IN VARCHAR2,
355 schemes IN VARCHAR2
356 DEFAULT NON_CLEARTEXT_PASSWORD_SCHEMES)
357 RETURN reply;
358 PROCEDURE auth(c IN OUT NOCOPY connection,
359 username IN VARCHAR2,
360 password IN VARCHAR2,
361 schemes IN VARCHAR2
362 DEFAULT NON_CLEARTEXT_PASSWORD_SCHEMES);
363 /**
364 * Sends MAIL command.
365 *
366 * PARAMETERS
367 * c SMTP connection
368 * sender the sender
369 * parameters the optional parameters to MAIL command
370 * RETURN
371 * the SMTP reply
372 * EXCEPTIONS
373 * transient_error - transient server error
374 * permanent_error - permanent server error
375 * invalid_operation - invalid operation
376 */
377 FUNCTION mail(c IN OUT NOCOPY connection,
378 sender IN VARCHAR2,
379 parameters IN VARCHAR2 DEFAULT NULL) RETURN reply;
380 PROCEDURE mail(c IN OUT NOCOPY connection,
381 sender IN VARCHAR2,
382 parameters IN VARCHAR2 DEFAULT NULL);
383
384 /**
385 * Sends RCPT command.
386 *
387 * PARAMETERS
388 * c SMTP connection
389 * recipient the recipient
390 * parameters the optional parameters to RCPT command
391 * RETURN
392 * the SMTP reply
393 * EXCEPTIONS
394 * transient_error - transient server error
395 * permanent_error - permanent server error
396 * invalid_operation - invalid operation
397 */
398 FUNCTION rcpt(c IN OUT NOCOPY connection,
399 recipient IN VARCHAR2,
400 parameters IN VARCHAR2 DEFAULT NULL) RETURN reply;
401 PROCEDURE rcpt(c IN OUT NOCOPY connection,
402 recipient IN VARCHAR2,
403 parameters IN VARCHAR2 DEFAULT NULL);
404
405 /**
406 * Sends DATA command. The data will be closed by the sequence
407 * <CR><LF>.<CR><LF>
408 *
409 * PARAMETERS
410 * c SMTP connection
411 * body the data body
412 * RETURN
413 * the SMTP reply after the sequence <CR><LF>.<CR><LF> is sent
414 * EXCEPTIONS
415 * transient_error - transient server error
416 * permanent_error - permanent server error
417 * invalid_operation - invalid operation
418 */
419 FUNCTION data(c IN OUT NOCOPY connection,
420 body IN VARCHAR2 CHARACTER SET ANY_CS)
421 RETURN reply;
422 PROCEDURE data(c IN OUT NOCOPY connection,
423 body IN VARCHAR2 CHARACTER SET ANY_CS);
424
425 /**
426 * Sends DATA command. This call opens the data session that the
427 * caller make subsequent write_data() calls to write large piece of
428 * data, following by close_data() to close the data session.
429 *
430 * PARAMETERS
431 * c SMTP connection
432 * RETURN
433 * the SMTP reply
434 * EXCEPTIONS
435 * transient_error - transient server error
436 * permanent_error - permanent server error
437 * invalid_operation - invalid operation
438 */
439 FUNCTION open_data(c IN OUT NOCOPY connection) RETURN reply;
440 PROCEDURE open_data(c IN OUT NOCOPY connection);
441
442 /**
443 * Sends data. This call must be preceeded by the call open_data().
444 *
445 * PARAMETERS
446 * c SMTP connection
447 * data the data body
448 * RETURN
449 * None
450 * EXCEPTIONS
451 * invalid_operation - invalid operation
452 */
453 PROCEDURE write_data(c IN OUT NOCOPY connection,
454 data IN VARCHAR2 CHARACTER SET ANY_CS);
455 PROCEDURE write_raw_data(c IN OUT NOCOPY connection,
456 data IN RAW);
457
458 /**
459 * Sends DATA command. This call opens the data session that the
460 * caller make subsequent write_data() calls to write large piece of
461 * data, following by close_data() to close the data session.
462 *
463 * PARAMETERS
464 * c SMTP connection
465 * RETURN
466 * the SMTP reply
467 * EXCEPTIONS
468 * transient_error - transient server error
469 * permanent_error - permanent server error
470 * invalid_operation - invalid operation
471 */
472 FUNCTION close_data(c IN OUT NOCOPY connection) RETURN reply;
473 PROCEDURE close_data(c IN OUT NOCOPY connection);
474
475 /**
476 * Sends RSET command.
477 *
478 * PARAMETERS
479 * c SMTP connection
480 * RETURN
481 * the SMTP reply
482 * EXCEPTIONS
483 * transient_error - transient server error
484 * permanent_error - permanent server error
485 * invalid_operation - invalid operation
486 */
487 FUNCTION rset(c IN OUT NOCOPY connection) RETURN reply;
488 PROCEDURE rset(c IN OUT NOCOPY connection);
489
490 /**
491 * Sends VRFY command.
492 *
493 * PARAMETERS
494 * c SMTP connection
495 * recipient the reccipient to verify
496 * RETURN
497 * the SMTP reply
498 * EXCEPTIONS
499 * invalid_operation - invalid operation
500 */
501 FUNCTION vrfy(c IN OUT NOCOPY connection,
502 recipient IN VARCHAR2) RETURN reply;
503
504 /**
505 * Sends HELP command.
506 *
507 * PARAMETERS
508 * c SMTP connection
509 * command the command to get help message
510 * RETURN
511 * the SMTP reply
512 * EXCEPTIONS
513 * invalid_operation - invalid operation
514 */
515 FUNCTION help(c IN OUT NOCOPY connection,
516 command IN VARCHAR2 DEFAULT NULL) RETURN replies;
517
518 /**
519 * Sends NOOP command.
520 *
521 * PARAMETERS
522 * c SMTP connection
523 * RETURN
524 * the SMTP reply
525 * EXCEPTIONS
526 * transient_error - transient server error
527 * permanent_error - permanent server error
528 * invalid_operation - invalid operation
529 */
530 FUNCTION noop(c IN OUT NOCOPY connection) RETURN reply;
531 PROCEDURE noop(c IN OUT NOCOPY connection);
532
533 /**
534 * Sends QUIT command and closes the SMTP connection.
535 *
536 * PARAMETERS
537 * c SMTP connection
538 * RETURN
539 * the SMTP reply
540 * EXCEPTIONS
541 * transient_error - transient server error
542 * permanent_error - permanent server error
543 * invalid_operation - invalid operation
544 */
545 FUNCTION quit(c IN OUT NOCOPY connection) RETURN reply;
546 PROCEDURE quit(c IN OUT NOCOPY connection);
547
548 /**
549 * Closes the SMTP connection. It will cause the current SMTP operation to
550 * be aborted. Use this procedure only to abort an email in the middle of
551 * the data session. To end the SMTP connection properly, use the quit() API
552 * instead.
553 *
554 * PARAMETERS
555 * c SMTP connection
556 * RETURN
557 * None
558 * EXCEPTIONS
559 * None
560 */
561 PROCEDURE close_connection(c IN OUT NOCOPY connection);
562
563 END;
[Home] [Help]