oracle plsql 发邮件,oracle 基于PLSQL实现发送邮件 UTL_SMTL

本博文介绍如何使用 UTL_SMTP来发送邮件

UTL_SMTP是基于SMTP协议来发送邮件

1、需要安装UTL_SMTP包

要利用oracle的系统包实现发送邮件的功能，必须先以sys用户登录执行以下两个脚本：

$ORACLE_HOME/rdbms/admin/utlsmtp.sql

$ORACLE_HOME/rdbms/admin/utltcp.sql

2、UTL_SMTP使用请看下面详细说明

DECLARE

c UTL_SMTP.CONNECTION;

PROCEDUREsend_header(nameINVARCHAR2,headerINVARCHAR2)AS

BEGIN

UTL_SMTP.WRITE_DATA(c,name||': '||header|| UTL_TCP.CRLF);

END;

BEGIN

/*连接邮件服务器*/

c := UTL_SMTP.OPEN_CONNECTION('mail.daphne.com.cn');

/*连接后执行与SMTP服务器的初始握手*/

UTL_SMTP.HELO(c,'mail.daphne.com.cn');

/*该函数/过程执行的通用SMTP命令,执行登录认证*/

UTL_SMTP.command(conn,'AUTH LOGIN');

UTL_SMTP.command(conn,

demo_base64.encode(utl_raw.cast_to_raw('testuser')));

UTL_SMTP.command(conn,

demo_base64.encode(utl_raw.cast_to_raw('********')));

/*如果不用登陆认证，则直接执行下面语句即可*/

/*发送邮箱*/

UTL_SMTP.MAIL(c,'testuser@mail.daphne.com.cn');

/*接收邮箱*/

UTL_SMTP.RCPT(c,'zhenyun.asu@mail.daphne.com.cn');

/*开始发送数据*/

UTL_SMTP.OPEN_DATA(c);

/*设置发送邮箱*/

send_header('From','"Sender"');

/*设置接收邮箱*/

send_header('To','"Recipient"');

/*设置抄送邮箱*/

send_header('Cc','');

/*设置邮件主题*/

send_header('Subject','Hello');

/*设置邮件内容*/

UTL_SMTP.WRITE_DATA(c,UTL_TCP.CRLF || l_msg);

UTL_SMTP.WRITE_RAW_DATA(c,

UTL_RAW.cast_to_raw(convert(UTL_TCP.CRLF ||

'测试邮件',

'ZHS16CGB231280')));

/*发送邮件内容*/

UTL_SMTP.CLOSE_DATA(c);

/*断开当前会话及链接*/

UTL_SMTP.QUIT(c);

EXCEPTION

WHENutl_smtp.transient_errorORutl_smtp.permanent_errorTHEN

BEGIN

UTL_SMTP.QUIT(c);

EXCEPTION

WHENUTL_SMTP.TRANSIENT_ERRORORUTL_SMTP.PERMANENT_ERRORTHEN

NULL;-- When the SMTP server is down orunavailable, we don't have

-- a connection to the server. TheQUIT call will raise an

-- exception that we can ignore.

END;

raise_application_error(-20000,

'Failed to send maildue to the following error: '||

sqlerrm);

END;

实例代码：D:\2_Techinques\6_Personal_summary\D2_Oracle_DB\SZ02_PLSQL技术文档

实例代码2.sql

3、将UTL_SMTL封装更加好用代码

procedurecreate_mail(i_server_hostvarchar2,

i_sending_host varchar2,

o_con outUTL_SMTP.CONNECTION)is

begin

o_con :=UTL_SMTP.OPEN_CONNECTION(i_server_host);

UTL_SMTP.HELO(o_con,i_sending_host);

end;

procedurecreate_mail_auth(i_server_hostvarchar2,

i_sending_hostvarchar2,

i_usernamevarchar2,

i_passwdvarchar2,

o_conoutUTL_SMTP.CONNECTION)is

begin

o_con :=UTL_SMTP.OPEN_CONNECTION(i_server_host);

UTL_SMTP.HELO(o_con,i_sending_host);

UTL_SMTP.COMMAND(o_con,'AUTH LOGIN');

UTL_SMTP.COMMAND(o_con,

DEMO_BASE64.ENCODE(UTL_RAW.CAST_TO_RAW(i_username)));

UTL_SMTP.COMMAND(o_con,

DEMO_BASE64.ENCODE(UTL_RAW.CAST_TO_RAW(i_passwd)));

end;

proceduresend_mail(i_conINOUTNOCOPYUTL_SMTP.CONNECTION,

i_sendervarchar2)is

begin

UTL_SMTP.MAIL(i_con, i_sender);

end;

procedurerecpt_mail(i_conINOUTNOCOPYUTL_SMTP.CONNECTION,

i_recptvarchar2)is

begin

UTL_SMTP.RCPT(i_con, i_recpt);

end;

PROCEDUREwrite_header(i_conINOUTNOCOPYUTL_SMTP.CONNECTION,

nameINVARCHAR2,

headerINVARCHAR2)AS

BEGIN

UTL_SMTP.WRITE_DATA(i_con,name||': '|| header ||UTL_TCP.CRLF);

END;

PROCEDUREwrite_raw_header(i_conINOUTNOCOPYUTL_SMTP.CONNECTION,

nameINVARCHAR2,

header INVARCHAR2)AS

BEGIN

UTL_SMTP.WRITE_RAW_DATA(i_con,

UTL_RAW.cast_to_raw(convert(name||': '||

header ||

UTL_TCP.CRLF,

'ZHS16CGB231280')));

END;

PROCEDUREwrite_raw_content(i_conINOUTNOCOPYUTL_SMTP.CONNECTION,

contentINVARCHAR2)AS

BEGIN

UTL_SMTP.WRITE_RAW_DATA(i_con,

UTL_RAW.cast_to_raw(convert(UTL_TCP.CRLF ||

content,

'ZHS16CGB231280')));

END;

procedureheader_from(i_conINOUTNOCOPYUTL_SMTP.CONNECTION,

i_mailvarchar2)is

begin

write_header(i_con,'From','"Sender"');

end;

procedureheader_to(i_conINOUTNOCOPYUTL_SMTP.CONNECTION,

i_mailvarchar2)is

begin

write_header(i_con,'To','"Recipient"');

end;

procedureheader_cc(i_conINOUTNOCOPYUTL_SMTP.CONNECTION,

i_mailvarchar2)is

begin

write_header(i_con,'Cc','"CC"');

end;

procedureheader_subject(i_conINOUTNOCOPYUTL_SMTP.CONNECTION,

i_subjectvarchar2)is

begin

write_raw_header(i_con,'Subject', i_subject);

end;

procedureheader_content(i_conINOUTNOCOPYUTL_SMTP.CONNECTION,

i_contentvarchar2)is

begin

write_raw_content(i_con,i_content);

end;

procedureopen_data(i_conINOUTNOCOPYUTL_SMTP.CONNECTION)is

begin

UTL_SMTP.OPEN_DATA(i_con);

end;

procedureclose_data(i_conINOUTNOCOPYUTL_SMTP.CONNECTION)is

begin

UTL_SMTP.CLOSE_DATA(i_con);

end;

procedurefree_mail(i_conINOUTNOCOPYUTL_SMTP.CONNECTION)is

begin

UTL_SMTP.QUIT(i_con);

end;

proceduresend_mail_demois

l_con UTL_SMTP.CONNECTION;

begin

/*初始化条件*/

create_mail_auth('mail.daphne.com.cn',

'mail.daphne.com.cn',

'testuser',

'********',

l_con);

send_mail(l_con,'testuser@mail.daphne.com.cn');

recpt_mail(l_con,'zhenyun.asu@mail.daphne.com.cn');

open_data(l_con);

-- header_from(l_con, 'DPSCM.PT@mail.daphne.com.cn');

header_to(l_con,'zhenyun.asu@mail.daphne.com.cn');

header_cc(l_con,'zhenyun.asu@mail.daphne.com.cn');

header_subject(l_con,'zhenyun.asu hellmail');

header_content(l_con,'zhenyun.asu hellmail');

close_data(l_con);

free_mail(l_con);

EXCEPTION

WHENutl_smtp.transient_errorORutl_smtp.permanent_errorTHEN

BEGIN

free_mail(l_con);

EXCEPTION

WHENUTL_SMTP.TRANSIENT_ERRORORUTL_SMTP.PERMANENT_ERRORTHEN

NULL;-- When the SMTP server is down orunavailable, we don't have

-- a connection to the server. TheQUIT call will raise an

-- exception that we can ignore.

END;

raise_application_error(-20000,

'Failed to send maildue to the following error: '||

sqlerrm);

endsend_mail_demo;