The UTL_SMTP module provides the capability to send email over the Simple Mail Transfer Protocol (SMTP).
The UTL_SMTP module includes the following routines.
Routine Name | Description |
---|---|
CLOSE_DATA procedure | Ends an email message. |
COMMAND procedure | Execute an SMTP command. |
COMMAND_REPLIES procedure | Execute an SMTP command where multiple reply lines are expected. |
DATA procedure | Specify the body of an email message. |
EHLO procedure | Perform initial handshaking with an SMTP server and return extended information. |
HELO procedure | Perform initial handshaking with an SMTP server. |
HELP procedure | Send the HELP command. |
MAIL procedure | Start a mail transaction. |
NOOP procedure | Send the null command. |
OPEN_CONNECTION function | Open a connection. |
OPEN_CONNECTION procedure | Open a connection. |
OPEN_DATA procedure | Send the DATA command. |
QUIT procedure | Terminate the SMTP session and disconnect. |
RCPT procedure | Specify the recipient of an email message. |
RSET procedure | Terminate the current mail transaction. |
VRFY procedure | Validate an email address. |
WRITE_DATA procedure | Write a portion of the email message. |
WRITE_RAW_DATA procedure | Write a portion of the email message consisting of RAW data. |
Public variable | Data type | Description |
---|---|---|
connection | RECORD | Description of an SMTP connection. |
reply | RECORD | SMTP reply line. |
ALTER MODULE SYSIBMADM.UTL_SMTP PUBLISH TYPE connection AS ROW
(
/* name or IP address of the remote host running SMTP server */
host VARCHAR(255),
/* SMTP server port number */
port INTEGER,
/* transfer timeout in seconds */
tx_timeout INTEGER,
);
ALTER MODULE SYSIBMADM.UTL_SMTP PUBLISH TYPE reply AS ROW
(
/* 3 digit reply code received from the SMTP server */
code INTEGER,
/* the text of the message received from the SMTP server */
text VARCHAR(508)
);
CREATE OR REPLACE PROCEDURE send_mail(
IN p_sender VARCHAR(4096),
IN p_recipient VARCHAR(4096),
IN p_subj VARCHAR(4096),
IN p_msg VARCHAR(4096),
IN p_mailhost VARCHAR(4096))
SPECIFIC send_mail
LANGUAGE SQL
BEGIN
DECLARE v_conn UTL_SMTP.CONNECTION;
DECLARE v_crlf VARCHAR(2);
DECLARE v_port INTEGER CONSTANT 25;
SET v_crlf = CHR(13) || CHR(10);
SET v_conn = UTL_SMTP.OPEN_CONNECTION(p_mailhost, v_port, 10);
CALL UTL_SMTP.HELO(v_conn, p_mailhost);
CALL UTL_SMTP.MAIL(v_conn, p_sender);
CALL UTL_SMTP.RCPT(v_conn, p_recipient);
CALL UTL_SMTP.DATA(
v_conn,
'Date: ' || TO_CHAR(SYSDATE, 'Dy, DD Mon YYYY HH24:MI:SS') || v_crlf ||
'From: ' || p_sender || v_crlf ||
'To: ' || p_recipient || v_crlf ||
'Subject: ' || p_subj || v_crlf ||
p_msg);
CALL UTL_SMTP.QUIT(v_conn);
END@
CALL send_mail('bwayne@mycorp.com','pparker@mycorp.com','Holiday Party',
'Are you planning to attend?','smtp.mycorp.com')@
CREATE OR REPLACE PROCEDURE send_mail_2(
IN p_sender VARCHAR(4096),
IN p_recipient VARCHAR(4096),
IN p_subj VARCHAR(4096),
IN p_msg VARCHAR(4096),
IN p_mailhost VARCHAR(4096)) SPECIFIC send_mail_2
LANGUAGE SQL
BEGIN
DECLARE v_conn UTL_SMTP.CONNECTION;
DECLARE v_crlf VARCHAR(2);
DECLARE v_port INTEGER CONSTANT 25;
SET v_crlf = CHR(13) || CHR(10);
SET v_conn = UTL_SMTP.OPEN_CONNECTION(p_mailhost, v_port, 10);
CALL UTL_SMTP.HELO(v_conn, p_mailhost);
CALL UTL_SMTP.MAIL(v_conn, p_sender);
CALL UTL_SMTP.RCPT(v_conn, p_recipient);
CALL UTL_SMTP.OPEN_DATA(v_conn);
CALL UTL_SMTP.WRITE_DATA(v_conn, 'From: ' || p_sender || v_crlf);
CALL UTL_SMTP.WRITE_DATA(v_conn, 'To: ' || p_recipient || v_crlf);
CALL UTL_SMTP.WRITE_DATA(v_conn, 'Subject: ' || p_subj || v_crlf);
CALL UTL_SMTP.WRITE_DATA(v_conn, v_crlf || p_msg);
CALL UTL_SMTP.CLOSE_DATA(v_conn);
CALL UTL_SMTP.QUIT(v_conn);
END@
CALL send_mail_2('bwayne@mycorp.com','pparker@mycorp.com','Holiday Party',
'Are you planning to attend?','smtp.mycorp.com')@