Sending electronic mail from PL/SQL is a long awaited feature that finally arrived with version 8.1 of the database. Finally the outside world can be notified directly of events within the database using standard PL/SQL packages. Things like notifying users of important data changes or notifying the DBA of impending lack of storage space can now be performed within PL/SQL.Although sending mail from the database has been available for quite some time using methods like creating text files or mail commands for CRON jobs to process later or similar interfaces, now it can be done from PL/SQL. There are several interfaces used along the way as shown in Figure 1. Figure 1: Interfaces between PL/SQL and the Mail Server. The main PL/SQL package called UTL_SMTP derives its name from the Simple Mail Transfer Protocol (SMTP) which is the external protocol it mimics within the database. Underlying the UTL_SMTP package is the UTL_TCP package, which mimics the external Transmission Protocol/Internet Protocol (TCP/IP). The interaction of the two PL/SQL packages is a mirror of the two external protocols they mimic in that one provides a specific interface for the other.TCP/IP transfers data of all sorts over networks between computers; the SMTP protocol offers up a specific set of commands to send and receive e-mail via TCP/IP.The UTL_TCP package transfers data back and forth between the database and the outside world via TCP/IP; the UTL_SMTP package provides a set of e-mail specific commands on top of the UTL_TCP package.Note: SMTP is neither a mail server nor a mail program, it is only a protocol used to interact with a mail server. Even though SMTP itself can send and relay e-mail the UTL_SMTP package is restricted to sending mail only.Right in the middle of this entire interaction is Oracle's Jserver in the database. The infrastructure relies on Java to be intermediary between the database and the TCP/IP, SMTP facilities.This topic makes no attempt to explain the inner workings of Jserver, Java, TCP/IP or SMTP it focuses strictly on the UTL_SMTP and UTL_TCP package and their interaction to send e-mail from PL/SQL.Prerequisites In order to use PL/SQL to send e-mail there are a few things the must be installed first.TCP/IP - Since SMTP relies on TCP/IP it naturally follows that UTL_SMTP require it as well. Thus this package can only be used on TCP/IP networks. Oracle Jserver - In order to use the UTL_SMTP package the Jserver option of Oracle must be installed because several Java functions and procedures are called from UTL_SMTP and UTL_TCP. SMTP - The SMTP must be installed and accessible from the machine where the database is installed. A Simple Example Let's begin with a simple example (only ten lines of code) to demonstrate just how easy it is to send mail from PL/SQL.SQL> DECLARE 2 v_connection UTL_SMTP.CONNECTION; 3 BEGIN 4 v_connection := UTL_SMTP.OPEN_CONNECTION('mailhost.bc.ca',25); 5 UTL_SMTP.HELO(v_connection,'mailhost.bc.ca'); 6 UTL_SMTP.MAIL(v_connection,'[email protected]'); 7 UTL_SMTP.RCPT(v_connection,'[email protected]'); 8 UTL_SMTP.DATA(v_connection,'Sent From PL/SQL'); 9 UTL_SMTP.QUIT(v_connection); 10 END; 11 / PL/SQL procedure successfully completed. Soon after running the above code we receive the nondescript offering in our In-Box. Opening the message reveals that this message has indeed come from PL/SQL. This example is lacking several important components of a basic e-mail message like sender name, and subject.Simple Mail Transfer Protocol (SMTP) SMTP contains a series of commands for interacting with a mail server. For example the RCPT command is used to identify the recipient of an e-mail and the MAIL command actually sends an e-mail. Each of these commands relies on a connection established via TCP/IP to the mail server.The basic steps in sending an e-mail using SMTP are as follows :Begin a transaction with the mail server using the MAIL command. Specify recipients of the mail using the RCPT command. Providing the text or data of the message using the DATA command. An end-of-mail indicator within the data denotes the end of the transaction. SMTP is making use of TCP/IP to establish the connection and transfer data back and forth. This is synonymous with the UTL_SMTP package relying on the UTL_TCP package. Each SMTP call returns a reply code indicating either success or specific reason for failure.Now that we have and overview of how SMTP works let's examine its counterpart within the database, UTL_SMTP.UTL_SMTP Package The UTL_SMTP package provides a series of PL/SQL procedures and functions on top of the SMTP commands responsible for sending mail. The steps described above to send an e-mail using SMTP would look like this in UTL_SMTP.Establish a connection to the mail server using the OPEN_CONECTION, HELO and MAIL procedures or functions. Specify recipients using the RCPT procedure or function. Provide the text or data of the message using the DATA procedure or function and close the connection with the QUIT procedure or function. Notice that in each case it is possible to use either a procedure or function. The procedure simply executes the operation while the function executes the operation and returns a reply from SMTP. The UTL_SMTP package is making calls to the UTL_TCP package to establish the connection and transfer data back and forth.Click here to examine each operation used to send the e-mail and it's invocation within PL/SQL in detail.
通过在存储过程中调用此存储过程来监控存储过程中所产生的错误，以便尽快解决。 CREATE OR REPLACE
PROCEDURE send_mail (
p_recipient IN VARCHAR2,
p_message IN VARCHAR2,
p_subject IN VARCHAR2 DEFAULT 'Oracle Perf Report '
|| TO_CHAR (SYSDATE,
'yyyy-mm-dd hh24:mi:ss'
),
p_sender IN VARCHAR2 DEFAULT '[email protected]'
)
IS
v_mailhost VARCHAR2 (30) := 'test.sina.com';
--SMTP服务器地址
mail_conn UTL_SMTP.connection;
msg VARCHAR2 (4000);
p_user VARCHAR2 (30) := '[email protected]';
--登录SMTP服务器的用户名
p_pass VARCHAR2 (30) := 'test'; --登录SMTP服务器的密码
BEGIN
/* 创建要发送的邮件内容 注意报头信息和邮件正文之间要空一行 */
msg :=
'Date:'
|| TO_CHAR (SYSDATE, 'dd mon yy hh24:mi:ss')
|| UTL_TCP.crlf
|| 'From: '
|| p_sender
|| '<'
|| p_sender
|| '>'
|| UTL_TCP.crlf
|| 'To: '
|| p_recipient
|| '<'
|| p_recipient
|| '>'
|| UTL_TCP.crlf
|| 'Subject: '
|| p_subject
|| UTL_TCP.crlf
|| UTL_TCP.crlf
|| p_message;
-- dbms_output.put_line(msg);
mail_conn := UTL_SMTP.open_connection (v_mailhost, 25);
UTL_SMTP.helo (mail_conn, v_mailhost);
/* smtp服务器登录校验 */
UTL_SMTP.command (mail_conn, 'AUTH LOGIN');
UTL_SMTP.command
(mail_conn,
UTL_RAW.cast_to_varchar2
(UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (p_user)
)
)
);
UTL_SMTP.command
(mail_conn,
UTL_RAW.cast_to_varchar2
(UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (p_pass)
)
)
);
UTL_SMTP.mail (mail_conn, p_sender);
UTL_SMTP.rcpt (mail_conn, p_recipient);
/* 发送数据 */
UTL_SMTP.DATA (mail_conn, msg);
UTL_SMTP.quit (mail_conn);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack);
NULL;
END send_mail;
Figure 1: Interfaces between PL/SQL and the Mail Server.
The main PL/SQL package called UTL_SMTP derives its name from the Simple Mail Transfer Protocol (SMTP) which is the external protocol it mimics within the database. Underlying the UTL_SMTP package is the UTL_TCP package, which mimics the external Transmission Protocol/Internet Protocol (TCP/IP). The interaction of the two PL/SQL packages is a mirror of the two external protocols they mimic in that one provides a specific interface for the other.TCP/IP transfers data of all sorts over networks between computers; the SMTP protocol offers up a specific set of commands to send and receive e-mail via TCP/IP.The UTL_TCP package transfers data back and forth between the database and the outside world via TCP/IP; the UTL_SMTP package provides a set of e-mail specific commands on top of the UTL_TCP package.Note: SMTP is neither a mail server nor a mail program, it is only a protocol used to interact with a mail server. Even though SMTP itself can send and relay e-mail the UTL_SMTP package is restricted to sending mail only.Right in the middle of this entire interaction is Oracle's Jserver in the database. The infrastructure relies on Java to be intermediary between the database and the TCP/IP, SMTP facilities.This topic makes no attempt to explain the inner workings of Jserver, Java, TCP/IP or SMTP it focuses strictly on the UTL_SMTP and UTL_TCP package and their interaction to send e-mail from PL/SQL.Prerequisites
In order to use PL/SQL to send e-mail there are a few things the must be installed first.TCP/IP - Since SMTP relies on TCP/IP it naturally follows that UTL_SMTP require it as well. Thus this package can only be used on TCP/IP networks.
Oracle Jserver - In order to use the UTL_SMTP package the Jserver option of Oracle must be installed because several Java functions and procedures are called from UTL_SMTP and UTL_TCP.
SMTP - The SMTP must be installed and accessible from the machine where the database is installed.
A Simple Example
Let's begin with a simple example (only ten lines of code) to demonstrate just how easy it is to send mail from PL/SQL.SQL> DECLARE
2 v_connection UTL_SMTP.CONNECTION;
3 BEGIN
4 v_connection := UTL_SMTP.OPEN_CONNECTION('mailhost.bc.ca',25);
5 UTL_SMTP.HELO(v_connection,'mailhost.bc.ca');
6 UTL_SMTP.MAIL(v_connection,'[email protected]');
7 UTL_SMTP.RCPT(v_connection,'[email protected]');
8 UTL_SMTP.DATA(v_connection,'Sent From PL/SQL');
9 UTL_SMTP.QUIT(v_connection);
10 END;
11 /
PL/SQL procedure successfully completed.
Soon after running the above code we receive the nondescript offering in our In-Box.
Opening the message reveals that this message has indeed come from PL/SQL.
This example is lacking several important components of a basic e-mail message like sender name, and subject.Simple Mail Transfer Protocol (SMTP)
SMTP contains a series of commands for interacting with a mail server. For example the RCPT command is used to identify the recipient of an e-mail and the MAIL command actually sends an e-mail. Each of these commands relies on a connection established via TCP/IP to the mail server.The basic steps in sending an e-mail using SMTP are as follows :Begin a transaction with the mail server using the MAIL command.
Specify recipients of the mail using the RCPT command.
Providing the text or data of the message using the DATA command. An end-of-mail indicator within the data denotes the end of the transaction.
SMTP is making use of TCP/IP to establish the connection and transfer data back and forth. This is synonymous with the UTL_SMTP package relying on the UTL_TCP package. Each SMTP call returns a reply code indicating either success or specific reason for failure.Now that we have and overview of how SMTP works let's examine its counterpart within the database, UTL_SMTP.UTL_SMTP Package
The UTL_SMTP package provides a series of PL/SQL procedures and functions on top of the SMTP commands responsible for sending mail. The steps described above to send an e-mail using SMTP would look like this in UTL_SMTP.Establish a connection to the mail server using the OPEN_CONECTION, HELO and MAIL procedures or functions.
Specify recipients using the RCPT procedure or function.
Provide the text or data of the message using the DATA procedure or function and close the connection with the QUIT procedure or function.
Notice that in each case it is possible to use either a procedure or function. The procedure simply executes the operation while the function executes the operation and returns a reply from SMTP. The UTL_SMTP package is making calls to the UTL_TCP package to establish the connection and transfer data back and forth.Click here to examine each operation used to send the e-mail and it's invocation within PL/SQL in detail.