CREATE OR REPLACE procedure html_email(
p_to in varchar2,
p_from in varchar2,
p_subject in varchar2,
p_text in varchar2 default null,
p_html in varchar2 default null,
p_smtp_hostname in varchar2,
p_smtp_portnum in varchar2)
is
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;
V_DB_NLS_CHARACTERSET VARCHAR2(40); --(防乱码处理)
begin
l_connection := utl_smtp.open_connection( p_smtp_hostname, p_smtp_portnum );
utl_smtp.helo( l_connection, p_smtp_hostname );
-- v_conn := UTL_SMTP.open_connection(v_smtphost, v_smtpport);
--是用 ehlo() 而不是 helo() 函数
--否则会报:ORA-29279: SMTP 永久性错误: 503 5.5.2 Send hello first.
-- UTL_SMTP.ehlo(v_conn, 'misdb.szyuto.com');
-- 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('****@QQ.com'))));
UTL_SMTP.command(l_connection,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('****'))));
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;charset=gb2312; boundary=' ||
chr(34) || l_boundary || chr(34) || chr(13) ||
chr(10);
dbms_output.put_line(l_temp);
----------------------------------------------------
-- 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;
/*
*modify date:2006-05-09
*cause :the html mail only is source code,can't be HTML format.
* :because the mail header format is wrong,less the chr(13)chr(10)
*modifier :zhuzl
*Thank for zengnan;
*
*I should study the mail p--
*/
l_temp := chr(13)||chr(10)||'--' || l_boundary || chr(13)||chr(10);
l_temp := l_temp || 'content-type: text/plain; charset=us-ascii' ||
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);
--(防乱码处理)
SELECT VALUE
INTO V_DB_NLS_CHARACTERSET
FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER = 'NLS_CHARACTERSET';
/*
UTL_SMTP.WRITE_RAW_DATA(l_connection,
UTL_RAW.CAST_TO_RAW(CONVERT(l_body_html,
'ZHS16GBK',
V_DB_NLS_CHARACTERSET)));
*/ --
while l_offset < dbms_lob.getlength(l_body_html) loop
-- utl_smtp.write_data(l_connection,
-- dbms_lob.substr(l_body_html,l_ammount,l_offset));
--邮件输出(防乱码处理)
UTL_SMTP.WRITE_RAW_DATA(l_connection,
UTL_RAW.CAST_TO_RAW(CONVERT(dbms_lob.substr(l_body_html,l_ammount,l_offset),
'ZHS16GBK',
V_DB_NLS_CHARACTERSET)));
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;
/
p_to in varchar2,
p_from in varchar2,
p_subject in varchar2,
p_text in varchar2 default null,
p_html in varchar2 default null,
p_smtp_hostname in varchar2,
p_smtp_portnum in varchar2)
is
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;
V_DB_NLS_CHARACTERSET VARCHAR2(40); --(防乱码处理)
begin
l_connection := utl_smtp.open_connection( p_smtp_hostname, p_smtp_portnum );
utl_smtp.helo( l_connection, p_smtp_hostname );
-- v_conn := UTL_SMTP.open_connection(v_smtphost, v_smtpport);
--是用 ehlo() 而不是 helo() 函数
--否则会报:ORA-29279: SMTP 永久性错误: 503 5.5.2 Send hello first.
-- UTL_SMTP.ehlo(v_conn, 'misdb.szyuto.com');
-- 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('****@QQ.com'))));
UTL_SMTP.command(l_connection,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('****'))));
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;charset=gb2312; boundary=' ||
chr(34) || l_boundary || chr(34) || chr(13) ||
chr(10);
dbms_output.put_line(l_temp);
----------------------------------------------------
-- 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;
/*
*modify date:2006-05-09
*cause :the html mail only is source code,can't be HTML format.
* :because the mail header format is wrong,less the chr(13)chr(10)
*modifier :zhuzl
*Thank for zengnan;
*
*I should study the mail p--
*/
l_temp := chr(13)||chr(10)||'--' || l_boundary || chr(13)||chr(10);
l_temp := l_temp || 'content-type: text/plain; charset=us-ascii' ||
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);
--(防乱码处理)
SELECT VALUE
INTO V_DB_NLS_CHARACTERSET
FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER = 'NLS_CHARACTERSET';
/*
UTL_SMTP.WRITE_RAW_DATA(l_connection,
UTL_RAW.CAST_TO_RAW(CONVERT(l_body_html,
'ZHS16GBK',
V_DB_NLS_CHARACTERSET)));
*/ --
while l_offset < dbms_lob.getlength(l_body_html) loop
-- utl_smtp.write_data(l_connection,
-- dbms_lob.substr(l_body_html,l_ammount,l_offset));
--邮件输出(防乱码处理)
UTL_SMTP.WRITE_RAW_DATA(l_connection,
UTL_RAW.CAST_TO_RAW(CONVERT(dbms_lob.substr(l_body_html,l_ammount,l_offset),
'ZHS16GBK',
V_DB_NLS_CHARACTERSET)));
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;
/