因工作需要研究了一下oracle发送电子邮件,网上多少的版本都有些问题。下面这段存储过程是参考metalink上在的版本改进而来,在oracle 10g 调试过,可以发送中文的电子邮件。这段存储过程包含邮件服务器要身份验证,请修改相应的部分。
CREATE OR REPLACE PROCEDURE send_email
( p_sender IN VARCHAR2,
p_recipient IN VARCHAR2,
P_subject IN VARCHAR2,
p_message IN VARCHAR2)
AS
mailhost VARCHAR2(100) := 'smtp.163.com';
mail_conn utl_smtp.connection;
msg varchar2(4000);
PROCEDURE send_header(conn IN OUT NOCOPY utl_smtp.connection,
name IN VARCHAR2,
value IN VARCHAR2) IS
BEGIN
utl_smtp.write_data(conn, name || ': =?iso-8859-1?Q?' ||
utl_raw.cast_to_varchar2( utl_encode.quoted_printable_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.command (mail_conn, 'AUTH LOGIN');
utl_smtp.command(mail_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('account'))));
utl_smtp.command(mail_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('password'))));
utl_smtp.mail(mail_conn,p_sender); -- sender
utl_smtp.rcpt(mail_conn,p_recipient); -- recipient
utl_smtp.open_data(mail_conn);
send_header(mail_conn,'From', p_sender);
send_header(mail_conn,'To', p_recipient);
send_header(mail_conn,'Subject', p_subject);
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,
'Failed tosend mail due to the following error: ' || sqlerrm);
WHEN OTHERS THEN
raise_application_error(-20001,
'The following error has occured: ' || sqlerrm);
END;
/