对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记录