对DBA而言,尽管在os级别下发送邮件是轻而易举的事情,然而很多时候我们也需要在PL/SQL中来发送邮件,比如监控job的执行状况等。本文根据网友(源作者未考证)的代码将其改装并封装到了package,感谢这位网友的无私奉献。文章首先给出演示调用该包发送邮件的情形后面给出了完整的代码。经测试Oracle 10g,Oracle 11g下均可用。关于os下发送邮件可参考:不可或缺的 sendEmail
1、调用SENDMAIL_PKG来发送邮件
- gx_admin@SYBO2SZ> set serveroutput on;
- gx_admin@SYBO2SZ> DECLARE
- 2 P_RECEIVER VARCHAR2(32767);
- 3 P_SUB VARCHAR2(32767);
- 4 P_TXT VARCHAR2(32767);
- 5 ERR_NUM NUMBER;
- 6 ERR_MSG VARCHAR2(32767);
- 7
- 8 BEGIN
- 9 P_RECEIVER := 'robinson.chen@12306.com';
- 10 P_SUB := 'Test mail';
- 11 P_TXT := 'This is a test mail.';
- 12 ERR_NUM := NULL;
- 13 ERR_MSG := NULL;
- 14
- 15 SENDMAIL_PKG.SENDMAIL ( P_RECEIVER, P_SUB, P_TXT, ERR_NUM, ERR_MSG );
- 16
- 17 DBMS_OUTPUT.Put_Line('ERR_NUM = ' || TO_CHAR(ERR_NUM));
- 18 DBMS_OUTPUT.Put_Line('ERR_MSG = ' || ERR_MSG);
- 19
- 20 DBMS_OUTPUT.Put_Line('');
- 21
- 22 COMMIT;
- 23 END;
- 24 /
- ERR_NUM = 0
- ERR_MSG =
- PL/SQL procedure successfully completed.
2、邮件发送结果
3、原代码
- --specification section
- CREATE OR REPLACE PACKAGE "SENDMAIL_PKG"
- IS
- PROCEDURE sendmail (p_receiver VARCHAR2,
- p_sub VARCHAR2,
- p_txt VARCHAR2,
- err_num OUT NUMBER,
- err_msg OUT VARCHAR2);
- END;
- /
- --body section
- CREATE OR REPLACE PACKAGE BODY "SENDMAIL_PKG"
- IS
- PROCEDURE sendmail (p_receiver VARCHAR2,
- p_sub VARCHAR2,
- p_txt VARCHAR2,
- err_num OUT NUMBER,
- err_msg OUT VARCHAR2)
- IS
- /* p_receiver => receiver
- p_sub => mail subject
- p_txt => mail content
- */
- p_user VARCHAR2 (30) := NULL;
- p_pass VARCHAR2 (30) := NULL;
- p_sendor VARCHAR2 (40) := 'DBA@gotrade.com';
- p_server VARCHAR2 (20)
- -- := system_pkg.get_sys_para_value ('TC_SMTP_IP'); --'192.168.7.65';
- :='192.168.7.65';
- p_port NUMBER := 25;
- p_need_smtp NUMBER := 0;
- p_subject VARCHAR2 (4000);
- l_crlf VARCHAR2 (2) := UTL_TCP.crlf;
- l_sendoraddress VARCHAR2 (4000);
- l_splite VARCHAR2 (10) := '++';
- boundary CONSTANT VARCHAR2 (256) := '-----BYSUK';
- first_boundary CONSTANT VARCHAR2 (256) := '--' || boundary || l_crlf;
- last_boundary CONSTANT VARCHAR2 (256)
- := '--' || boundary || '--' || l_crlf ;
- multipart_mime_type CONSTANT VARCHAR2 (256)
- := 'multipart/mixed; boundary="' || boundary || '"' ;
- TYPE address_list IS TABLE OF VARCHAR2 (100)
- INDEX BY BINARY_INTEGER;
- my_address_list address_list;
- ---------------------------------------split mail address----------------------------------------------
- PROCEDURE p_splite_str (p_str VARCHAR2, p_splite_flag INT DEFAULT 1)
- IS
- l_addr VARCHAR2 (254) := '';
- l_len INT;
- l_str VARCHAR2 (4000);
- j INT := 0;
- BEGIN
- /*Handle recieve mail address, such like blank, semicolon*/
- l_str :=
- TRIM (RTRIM (REPLACE (REPLACE (p_str, ';', ','), ' ', ''), ','));
- l_len := LENGTH (l_str);
- FOR i IN 1 .. l_len
- LOOP
- IF SUBSTR (l_str, i, 1) <> ','
- THEN
- l_addr := l_addr || SUBSTR (l_str, i, 1);
- ELSE
- j := j + 1;
- IF p_splite_flag = 1
- THEN
- --Add symbol '<>' for each mail address. else could not send to many reciever
- l_addr := '<' || l_addr || '>';
- my_address_list (j) := l_addr;
- END IF;
- l_addr := '';
- END IF;
- IF i = l_len
- THEN
- j := j + 1;
- IF p_splite_flag = 1
- THEN
- l_addr := '<' || l_addr || '>';
- my_address_list (j) := l_addr;
- END IF;
- END IF;
- END LOOP;
- END;
- -----------------------------------write mail header and mail content----------------------------------
- PROCEDURE write_data (p_conn IN OUT NOCOPY UTL_SMTP.connection,
- p_name IN VARCHAR2,
- p_value IN VARCHAR2,
- p_splite VARCHAR2 DEFAULT ':',
- p_crlf VARCHAR2 DEFAULT l_crlf)
- IS
- BEGIN
- /* utl_raw.cast_to_raw to handle chinese code*/
- UTL_SMTP.write_raw_data (
- p_conn,
- UTL_RAW.cast_to_raw (
- CONVERT (p_name || p_splite || p_value || p_crlf,
- 'ZHS16CGB231280')));
- END;
- ----------------------------------------write mime mail tail-----------------------------------------------------
- PROCEDURE end_boundary (conn IN OUT NOCOPY UTL_SMTP.connection,
- LAST IN BOOLEAN DEFAULT FALSE)
- IS
- BEGIN
- UTL_SMTP.write_data (conn, UTL_TCP.crlf);
- IF (LAST)
- THEN
- UTL_SMTP.write_data (conn, last_boundary);
- END IF;
- END;
- ---------------------------------------------send mail procedure--------------------------------------------
- PROCEDURE p_email (p_sendoraddress2 VARCHAR2, --sender address
- p_receiveraddress2 VARCHAR2) --reciever address
- IS
- l_conn UTL_SMTP.connection; --create a connection
- BEGIN
- /*Initial mail server*/
- l_conn := UTL_SMTP.open_connection (p_server, p_port);
- UTL_SMTP.helo (l_conn, p_server);
- /* smtp authentication*/
- IF p_need_smtp = 1
- THEN
- UTL_SMTP.command (l_conn, 'AUTH LOGIN', '');
- UTL_SMTP.command (
- l_conn,
- UTL_RAW.cast_to_varchar2 (
- UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (p_user))));
- UTL_SMTP.command (
- l_conn,
- UTL_RAW.cast_to_varchar2 (
- UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (p_pass))));
- END IF;
- /*configure sender and reciever mail address*/
- UTL_SMTP.mail (l_conn, p_sendoraddress2);
- UTL_SMTP.rcpt (l_conn, p_receiveraddress2);
- /*configure mail header*/
- UTL_SMTP.open_data (l_conn);
- /*configure date*/
- --write_data(l_conn, 'Date', to_char(sysdate-1/3, 'dd Mon yy hh24:mi:ss'));
- /*configure sender*/
- write_data (l_conn, 'From', p_sendor);
- /*configure reciever*/
- write_data (l_conn, 'To', p_receiver);
- /*add mail subject*/
- SELECT REPLACE (
- '=?GB2312?B?'
- || UTL_RAW.cast_to_varchar2 (
- UTL_ENCODE.base64_encode (RAWTOHEX (p_sub)))
- || '?=',
- UTL_TCP.crlf,
- '')
- INTO p_subject
- FROM DUAL;
- write_data (l_conn, 'Subject', p_subject);
- write_data (l_conn, 'Content-Type', multipart_mime_type);
- UTL_SMTP.write_data (l_conn, UTL_TCP.crlf);
- UTL_SMTP.write_data (l_conn, first_boundary);
- write_data (l_conn, 'Content-Type', 'text/html');
- UTL_SMTP.write_data (l_conn, UTL_TCP.crlf);
- write_data (
- l_conn,
- '',
- REPLACE (REPLACE (p_txt, l_splite, CHR (10)), CHR (10), l_crlf),
- '',
- '');
- end_boundary (l_conn);
- /*close write data*/
- UTL_SMTP.close_data (l_conn);
- /*close connection*/
- UTL_SMTP.quit (l_conn);
- END;
- ---------------------------------------------main procedure -----------------------------------------------------
- BEGIN
- err_num := 0;
- l_sendoraddress := '<' || p_sendor || '>';
- p_splite_str (p_receiver); --handle mail address
- FOR k IN 1 .. my_address_list.COUNT
- LOOP
- p_email (l_sendoraddress, my_address_list (k));
- END LOOP;
- END;
- END;
- /
更多参考
使用 DBMS_PROFILER 定位 PL/SQL 瓶颈代码
PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录