--pl/sql发送邮件多个收件人问题----测试使用pl/sql发送html报表，在网上找了个post_html_mail的procedure，现在测试单个用户可接受，可多用户，但要保证输入的用户正确----附上post_html_mail
create or replace procedure kol_Post_html_mail( p_to in varchar2, p_subject in varchar2, p_text in varchar2 default null, p_html in varchar2 default null) is p_smtp_hostname varchar2(20):='192.168.38.247'; --SMTP server p_smtp_portnum varchar2(2):='25';--port p_from varchar2(100):='sysAdmin@kolinker.com'; --from l_boundary varchar2(255) default 'a1B2C3d4e3f2g1'; l_connection utl_smtp.connection; l_body_html clob := empty_clob; --This LOB will be the email MESSage l_offset number; l_ammount number; l_temp varchar2(32767) default null; l_adresses varchar2(1000) := p_to; l_adress varchar2(50); begin l_connection := utl_smtp.open_connection( p_smtp_hostname, p_smtp_portnum ); utl_smtp.helo( l_connection,p_smtp_hostname); utl_smtp.mail( l_connection, p_from ); -- Send more than one person if (instr(l_adresses, ';') = 0) then l_adress := l_adresses; utl_smtp.rcpt( l_connection, l_adress ); end if; while instr(l_adresses, ';') > 0 loop select substr(l_adresses, 1, instr(l_adresses, ';')-1) into l_adress from dual; select substr(l_adresses, instr(l_adresses, ';')+1) into l_adresses from dual; utl_smtp.rcpt( l_connection, l_adress ); if instr(l_adresses, ';') = 0 then l_adress := l_adresses; utl_smtp.rcpt( l_connection, l_adress ); end if; end loop; --utl_smtp.rcpt( l_connection, p_to ); l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10); l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10); l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10); l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10); l_temp := l_temp || 'Reply-To: ' || p_from || chr(13) || chr(10); l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' || chr(34) || l_boundary || chr(34) || chr(13) || chr(10); --chr(34) is " ---------------------------------------------------- -- Write the headers dbms_lob.createtemporary( l_body_html, false, 10 ); dbms_lob.write(l_body_html,length(l_temp),1,l_temp); ---------------------------------------------------- -- Write the text boundary l_offset := dbms_lob.getlength(l_body_html) + 1; l_temp := '--' || l_boundary || chr(13)||chr(10); l_temp := l_temp || 'content-type: text/plain; Charset=UTF-8' || chr(13) || chr(10) || chr(13) || chr(10); dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp); ---------------------------------------------------- -- Write the plain text portion of the email l_offset := dbms_lob.getlength(l_body_html) + 1; dbms_lob.write(l_body_html,length(p_text),l_offset,p_text); ---------------------------------------------------- -- Write the HTML boundary l_temp := chr(13)||chr(10)||chr(13)||chr(10)||'--' || l_boundary || chr(13) || chr(10); l_temp := l_temp || 'content-type: text/html;' || chr(13) || chr(10) || chr(13) || chr(10); l_offset := dbms_lob.getlength(l_body_html) + 1; dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp); ---------------------------------------------------- -- Write the HTML portion of the message l_offset := dbms_lob.getlength(l_body_html) + 1; dbms_lob.write(l_body_html,length(p_html),l_offset,p_html); ---------------------------------------------------- -- Write the final html boundary l_temp := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13); l_offset := dbms_lob.getlength(l_body_html) + 1; dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp); ---------------------------------------------------- -- Send the email in 1900 byte chunks to UTL_SMTP l_offset := 1; l_ammount := 1900; utl_smtp.open_data(l_connection); while l_offset < dbms_lob.getlength(l_body_html) loop utl_smtp.write_raw_data(l_connection, UTL_RAW.CAST_TO_RAW(dbms_lob.substr(l_body_html,l_ammount,l_offset))); l_offset := l_offset + l_ammount ; l_ammount := least(1900,dbms_lob.getlength(l_body_html) - l_ammount); end loop; utl_smtp.close_data(l_connection); utl_smtp.quit( l_connection ); dbms_lob.freetemporary(l_body_html); end;
联系客服
