以下过程是用oracle发送邮件,如果是从163信箱发到其他信箱(如新浪信箱)都是正确的,但如果从新浪信箱发到163信箱就不正确,请问如何才能做到任意两个信箱互相都能发送?
CREATE OR REPLACE PROCEDURE SendMail(
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):='smtp.sina.com.cn';
p_smtp_portnum varchar2(2):='25';
p_from varchar2(20):='[email protected]';
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,'25'); utl_smtp.helo( l_connection,p_smtp_hostname);
utl_smtp.command(l_connection,'AUTH LOGIN');
utl_smtp.command(l_connection, 'ZGFudmk5MjY=');
utl_smtp.command(l_connection, 'OTAwOTEx'); 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);
----------------------------------------------------
-- 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);
----------------------------------------------------
-- 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;
/
CREATE OR REPLACE PROCEDURE SendMail(
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):='smtp.sina.com.cn';
p_smtp_portnum varchar2(2):='25';
p_from varchar2(20):='[email protected]';
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,'25'); utl_smtp.helo( l_connection,p_smtp_hostname);
utl_smtp.command(l_connection,'AUTH LOGIN');
utl_smtp.command(l_connection, 'ZGFudmk5MjY=');
utl_smtp.command(l_connection, 'OTAwOTEx'); 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);
----------------------------------------------------
-- 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);
----------------------------------------------------
-- 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;
/
解决方案 »
- oracle 10g 连接 Sql 2005 错误提示NCR-000002 官方解释 调用函数错误
- 求Microsoft JET Database Engine 错误 '80004005'解决方案
- 这个rollup要怎么写?
- oracle如何实现一列多条结果合并
- 那有可以用于ADO的oracle驱动可以下载?
- 帮帮我,在线急等!oracle无法安装
- 菜鸟请教个各位大神!!急!!
- 怎样删除所有A表中有而B表中没有的记录?
- 如何使用CodeSmith生成存储过程?紧急求助
- 还是关于dblink!请大家共同看看,讨论讨论!谢!
- 问题1:请问把SESSION KILL 掉之后,而CPU的利用并没有被释放,怎么做能把CPU给释放掉? 问题2;这里有个SQL文很慢谁能帮我找找原因?很急,在线
- 如何备份?
若你需要人以两个邮箱之间都能发邮件,需要将变量
p_smtp_hostname varchar2(20):='smtp.sina.com.cn';
p_smtp_portnum varchar2(2):='25';
p_from varchar2(20):='[email protected]';
作为发件邮箱的参数,即把过程头部变成:
CREATE OR REPLACE PROCEDURE SendMail(
p_smtp_hostname in varchar2, p_smtp_portnum in varchar2,
p_from in varchar2,
p_to in varchar2,
p_subject in varchar2,
p_text in varchar2 default null,
p_html in varchar2 default null)
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;
BEGIN
......
END