Oracle 中利用Procedure 发邮件...
CREATE OR REPLACE PROCEDURE tnt_send_mail
( p_sender IN VARCHAR2,
p_receiver IN VARCHAR2,
P_subject IN VARCHAR2,
p_message IN VARCHAR2)
AS
mailhost VARCHAR2(100) := '10.100.10.100'; --邮件服务器IP
mail_conn utl_smtp.connection;
PROCEDURE send_header(conn IN OUT NOCOPY utl_smtp.connection,
name IN VARCHAR2,
value IN VARCHAR2) IS
BEGIN
--我服务器端的字符集是ZHS16GBK,使用GB2312才能支持中文
utl_smtp.write_data(conn,name || ': =?GB2312?B?' ||utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.
cast_to_raw(value))) || '?='|| utl_tcp.crlf);
END;
BEGIN
mail_conn :=utl_smtp.open_connection(mailhost);
utl_smtp.helo(mail_conn,mailhost);
utl_smtp.mail(mail_conn,p_sender); -- sender
utl_smtp.rcpt(mail_conn,p_receiver); -- receiver
utl_smtp.open_data(mail_conn);
send_header(mail_conn,'From', p_sender);
send_header(mail_conn,'To', p_receiver);
send_header(mail_conn,'Subject', p_subject); --邮件主题
UTL_SMTP.write_data(mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || Chr(13)|| chr(10));
utl_smtp.write_data(mail_conn, 'MIME-Version: 1.0' || chr(13) || chr(10));
utl_smtp.write_data(mail_conn, 'Content-type: text/plain;Charset=UTF8;' || chr(13) || chr(10));
utl_smtp.write_data(mail_conn, 'Content-Transfer-Encoding: base64' || chr(13) || chr(10));
utl_smtp.write_data(mail_conn, chr(13) || chr(10));
utl_smtp.write_data(mail_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_message))));
utl_smtp.close_data(mail_conn);
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
utl_smtp.quit(mail_conn);
raise_application_error(-20000, sqlerrm);
WHEN OTHERS THEN
raise_application_error(-20001,
'The following error has occured: ' || sqlerrm);
End tnt_send_mail;
( p_sender IN VARCHAR2,
p_receiver IN VARCHAR2,
P_subject IN VARCHAR2,
p_message IN VARCHAR2)
AS
mailhost VARCHAR2(100) := '10.100.10.100'; --邮件服务器IP
mail_conn utl_smtp.connection;
PROCEDURE send_header(conn IN OUT NOCOPY utl_smtp.connection,
name IN VARCHAR2,
value IN VARCHAR2) IS
BEGIN
--我服务器端的字符集是ZHS16GBK,使用GB2312才能支持中文
utl_smtp.write_data(conn,name || ': =?GB2312?B?' ||utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.
cast_to_raw(value))) || '?='|| utl_tcp.crlf);
END;
BEGIN
mail_conn :=utl_smtp.open_connection(mailhost);
utl_smtp.helo(mail_conn,mailhost);
utl_smtp.mail(mail_conn,p_sender); -- sender
utl_smtp.rcpt(mail_conn,p_receiver); -- receiver
utl_smtp.open_data(mail_conn);
send_header(mail_conn,'From', p_sender);
send_header(mail_conn,'To', p_receiver);
send_header(mail_conn,'Subject', p_subject); --邮件主题
UTL_SMTP.write_data(mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || Chr(13)|| chr(10));
utl_smtp.write_data(mail_conn, 'MIME-Version: 1.0' || chr(13) || chr(10));
utl_smtp.write_data(mail_conn, 'Content-type: text/plain;Charset=UTF8;' || chr(13) || chr(10));
utl_smtp.write_data(mail_conn, 'Content-Transfer-Encoding: base64' || chr(13) || chr(10));
utl_smtp.write_data(mail_conn, chr(13) || chr(10));
utl_smtp.write_data(mail_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_message))));
utl_smtp.close_data(mail_conn);
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
utl_smtp.quit(mail_conn);
raise_application_error(-20000, sqlerrm);
WHEN OTHERS THEN
raise_application_error(-20001,
'The following error has occured: ' || sqlerrm);
End tnt_send_mail;