Oracle9发送邮件过程代码,PLSQL代码库,德仔网

代码语言
代码分类
【PLSQL】 Oracle9发送邮件过程
作者:Dezai.CN / 发布于2011/3/29/ 1149
<div>CREATE OR REPLACE PROCEDURE "SEND_MAIL" (
 P_TO IN VARCHAR2, 
P_FROM IN VARCHAR2 DEFAULT <a href="mailto:'ljx@mail.com'">'ljx@mail.com'</a>,
 P_TEXT IN VARCHAR2 DEFAULT '',
 P_HTML IN VARCHAR2 DEFAULT '', 
P_SUBJECT IN VARCHAR2 DEFAULT 'ERP系统异常报告. '||TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss'))
IS
 P_SMTP_HOSTNAME VARCHAR2(20):='lxj-mail.com';
 P_SMTP_PORTNUM VARCHAR2(2):='25';
 P_USER VARCHAR2(30) := '用户名'; -- 登录SMTP服务器的用户名
 P_PASS VARCHAR2(30) := '密码'; -- 登录SMTP服务器的密码
 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;
BEGIN
 L_CONNECTION := UTL_SMTP.OPEN_CONNECTION( P_SMTP_HOSTNAME, P_SMTP_PORTNUM );
 UTL_SMTP.HELO( L_CONNECTION,P_SMTP_HOSTNAME);
 /* SMTP服务器登录校验 */
 UTL_SMTP.COMMAND(L_CONNECTION, 'AUTH LOGIN'); 
 UTL_SMTP.COMMAND(L_CONNECTION, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(P_USER)))); 
 UTL_SMTP.COMMAND(L_CONNECTION, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(P_PASS))));</div>
<div> UTL_SMTP.MAIL( L_CONNECTION, P_FROM );
 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);
</div>
<div> ----------------------------------------------------
 -- 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=GB2312' ||
 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);
</div>
<div> ----------------------------------------------------
 -- 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;
/

</div>
试试其它关键字
　发送邮件　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)