[color=red]最近需要用Oracle发邮件.于是写了个发简单邮件的过程,如下:[/color]
CREATE OR REPLACE PROCEDURE SEND_SIMPLE_MAIL
(p_sender IN VARCHAR2, --发件人
p_recipient IN VARCHAR2, --收件人
p_subject IN VARCHAR2, --标题
p_server IN VARCHAR2 , --邮件服务器,端口为25,不作设置了
p_body IN VARCHAR2 --邮件内容
)
IS
mail_conn UTL_SMTP.connection;
title varchar2(20000);
v_msg varchar2(20000);
BEGIN
mail_conn := UTL_SMTP.open_connection (p_server, 25); --打开一个邮件连接
UTL_SMTP.ehLO(mail_conn, p_server); -- smtp服务器登录校验
UTL_SMTP.mail(mail_conn,'<'|| p_sender||'>'); --设置发件用户,注意要加上那两个尖括号
UTL_SMTP.rcpt(mail_conn,'<'|| p_recipient||'>'); --设置接收用户
/*编码处理*/
v_msg := 'MIME-Version: 1.0'
|| UTL_TCP.CRLF || 'From: ' || p_sender || '<' || p_sender || '>'
|| UTL_TCP.CRLF || 'To: ' || p_recipient || '<' || p_recipient || '>'
|| UTL_TCP.CRLF || 'Date:' || TO_CHAR(SYSDATE, 'YYYY-MM-dd HH24:MI:SS')
|| UTL_TCP.CRLF || 'Subject:' || p_subject
|| UTL_TCP.CRLF || 'Content-Type: text/plain; charset=GB2312'
|| UTL_TCP.CRLF || 'Content-Transfer-Encoding: 8bit'
|| UTL_TCP.CRLF || UTL_TCP.CRLF
|| p_body;
/* 发送数据 */
UTL_SMTP.OPEN_DATA(MAIL_CONN);
UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN,UTL_RAW.cast_to_raw(convert(v_msg,'ZHS16GBK','ZHS16GBK'))); --转换编码到GBK
UTL_SMTP.CLOSE_DATA(MAIL_CONN);
UTL_SMTP.quit (MAIL_CONN);
EXCEPTION WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
CREATE OR REPLACE PROCEDURE SEND_SIMPLE_MAIL
(p_sender IN VARCHAR2, --发件人
p_recipient IN VARCHAR2, --收件人
p_subject IN VARCHAR2, --标题
p_server IN VARCHAR2 , --邮件服务器,端口为25,不作设置了
p_body IN VARCHAR2 --邮件内容
)
IS
mail_conn UTL_SMTP.connection;
title varchar2(20000);
v_msg varchar2(20000);
BEGIN
mail_conn := UTL_SMTP.open_connection (p_server, 25); --打开一个邮件连接
UTL_SMTP.ehLO(mail_conn, p_server); -- smtp服务器登录校验
UTL_SMTP.mail(mail_conn,'<'|| p_sender||'>'); --设置发件用户,注意要加上那两个尖括号
UTL_SMTP.rcpt(mail_conn,'<'|| p_recipient||'>'); --设置接收用户
/*编码处理*/
v_msg := 'MIME-Version: 1.0'
|| UTL_TCP.CRLF || 'From: ' || p_sender || '<' || p_sender || '>'
|| UTL_TCP.CRLF || 'To: ' || p_recipient || '<' || p_recipient || '>'
|| UTL_TCP.CRLF || 'Date:' || TO_CHAR(SYSDATE, 'YYYY-MM-dd HH24:MI:SS')
|| UTL_TCP.CRLF || 'Subject:' || p_subject
|| UTL_TCP.CRLF || 'Content-Type: text/plain; charset=GB2312'
|| UTL_TCP.CRLF || 'Content-Transfer-Encoding: 8bit'
|| UTL_TCP.CRLF || UTL_TCP.CRLF
|| p_body;
/* 发送数据 */
UTL_SMTP.OPEN_DATA(MAIL_CONN);
UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN,UTL_RAW.cast_to_raw(convert(v_msg,'ZHS16GBK','ZHS16GBK'))); --转换编码到GBK
UTL_SMTP.CLOSE_DATA(MAIL_CONN);
UTL_SMTP.quit (MAIL_CONN);
EXCEPTION WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;