utl_smtp 发送邮件的实例

创建公用Package:

create or replace package xx_mail_api is

procedure sendmail(p_err_buf out varchar2
,p_ret_code out number
,p_sendor in varchar2
,p_recipients in varchar2
,p_subject in varchar2
,p_message in varchar2
,p_smtp_server in varchar2 default null);
end xx_mail_api;

--Body

create or replace package body xx_mail_api is

procedure sendmail(p_err_buf out varchar2
,p_ret_code out number
,p_sendor in varchar2
,p_recipients in varchar2
,p_subject in varchar2
,p_message in varchar2
,p_smtp_server in varchar2 default null) is
v_mailhost varchar2(100);
v_mail_conn utl_smtp.connection;

v_error_exception exception;
v_stage varchar2(1000);
v_boundary varchar2(255) default '001';

procedure populate_smtp_server is

begin
v_stage := 'populating the smtp server...';

select flv.description
into v_mailhost
from fnd_lookup_values flv
,fnd_lookup_types flt
where flt.lookup_type = flv.lookup_type
and flt.lookup_type = 'XX_MAIL_SMTP_SERVER'
and flv.language = 'ZHS'
and flv.lookup_code = upper(nvl(p_smtp_server, 'CN_SERVER'));

end populate_smtp_server;

procedure split_recipients is
c_delimeter varchar2(1) := ';';
v_index number := 0;
v_recipients varchar2(1000) := nvl(p_recipients, '');
begin
v_stage := 'spliting the recipients information...';

v_index := instr(v_recipients, c_delimeter);
loop
exit when v_index <= 0;

utl_smtp.rcpt(v_mail_conn, rtrim(ltrim(substr(v_recipients, 1, v_index - 1))));

v_recipients := substr(v_recipients, v_index + 1);
v_index := instr(v_recipients, c_delimeter);
end loop;

utl_smtp.rcpt(v_mail_conn, rtrim(ltrim(v_recipients)));

end split_recipients;

begin
populate_smtp_server;

v_stage := 'connecting the mailhost...';
v_mail_conn := utl_smtp.open_connection(v_mailhost, 25);

v_stage := 'hello of the mail server...';
utl_smtp.helo(v_mail_conn, v_mailhost);
utl_smtp.mail(v_mail_conn, p_sendor);

split_recipients;

v_stage := 'populating the other information for the email...';
utl_smtp.open_data(v_mail_conn);
utl_smtp.write_raw_data(v_mail_conn,
utl_raw.cast_to_raw('MIME-Version: 1.0' || chr(13) || chr(10) || 'To: ' || p_recipients ||
chr(13) || chr(10) || 'From: ' || p_sendor || chr(13) || chr(10) ||
'Subject:' || p_subject || chr(13) || chr(10) || 'Reply-To: ' ||
p_sendor || chr(13) || chr(10) ||
'Content-Type: multipart/alternative;' || ' boundary=' || chr(34) ||
v_boundary || chr(34) || chr(13) || chr(10) || '--' || v_boundary ||
chr(13) || chr(10) || 'content-type: text/plain; Charset=utf-8' ||
chr(13) || chr(10) || chr(13) || chr(10) || p_message));

utl_smtp.close_data(v_mail_conn);
utl_smtp.quit(v_mail_conn);

exception
when others then
p_err_buf := 'Error in ' || v_stage || ' Error Code:' || sqlcode || '. Error Msg:' || sqlerrm;
p_ret_code := 2;

end sendmail;
end xx_mail_api;

--调用：

declare
p_err_buf varchar2(3000);
p_ret_code varchar2(3000);
begin
xx_mail_api.sendmail(p_err_buf,p_ret_code,'ERP@Hotmail.com',ERP@gmail.com','Test','测试！',null);
end;