oracle 邮件发送的原理,oracle 发送电子邮件

因工作需要研究了一下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;

/