by Tom.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;
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 );
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); ----------------------------------------------------
-- 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=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);
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));
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;
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 );
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); ----------------------------------------------------
-- 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=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);
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));
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;
/
解决方案 »
- 关于system/manager问题
- Oracle中想查询某个表的访问权限被授予给了哪个用户,怎么办?
- 请问大虾们:10G Universal Installer是不是会把客户端和服务器端都默认安装进Windows系统
- 有关于进销存库龄的数据库设计
- oracle 怎么查看 消耗内存最多的一条SQL。。。。。。。。。。。。。。。。。。。。。。。。。。。。
- 关于备份的问题
- sqlplus怎么连不上服务器呀
- 请教oracle9i中用startup命令启动数据库实例的详解
- 一个关于oralce里的rownum的问题。
- 请问高手这是报的什么错啊???!!!!!!!!!!!!!!!!!!!!!
- oracle 8.17 备份问题?
- ORACLE ERP 11i系统安装和技术支持。
但是你给的程序和我的程序一样的,执行到 utl_smtp.mail( l_connection, p_from );出错,我估计是否是其他设置(EMAIL SERVER )有问题,比如SMTP SERVER需要口令验证?希望得到你的再次帮助。谢谢。
[email protected]指的是发送者的邮件地址,如果你的服务器检查发送者的地址,问题就出在这里了.因为这个地址,不符合通常的确良[email protected]这要的结构,.mail不是一个合理的域号后缀.
[email protected]也不是一个合理的接收邮件的地址,很难讲它也能通过.
-----------------------------------
顺便问一下,UTL_SMTP.MAIL没有返回值吗?它返回的结果是什么?出错信息是什么?
SYSTEM.MAIL 是我本机的一个EMAIL服务器,域名有自己设定,应该没有问题,而且我用C#作了一个DEMO 收、发都没有问题。
出错信息是:ORA-20002: 501 input error.
ORA-06512: 在"SYS.UTL_SMTP", line 86
ORA-06512: 在"SYS.UTL_SMTP", line 204
ORA-06512: 在"USERCAL.MAIL", line 6
估计你的Oracle应该是9i以上的版本,暂时无法测试.
由于没有用过UTL_SMTP包,只对邮件协议较为了解,在Oracle8.1.7上,出现某些类找不到,估计还有一些问题.暂时无法立即解答.
receiver in varchar2, subject in varchar2, content in varchar2
)
as
c utl_smtp.connection;
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF);
END;
BEGIN
c := utl_smtp.open_connection(smtp_server);
utl_smtp.helo(c, smtp_server);
utl_smtp.command(c,'auth login','');
utl_smtp.command(c,'YnFjXHJ1bm5pbmcgbHVv','');
utl_smtp.command(c,'emhvbmdndW93dWhhbg==','');
utl_smtp.mail(c, sender);
utl_smtp.rcpt(c, receiver);
utl_smtp.open_data(c);
utl_smtp.write_data(c,'Content-Type:text/html;charset=gb2312'||utl_tcp.CRLF);
send_header('From', Sender);
send_header('To', receiver);
send_header('Subject', subject);
utl_smtp.write_data(c, utl_tcp.CRLF);
utl_smtp.write_raw_data(c,utl_raw.cast_to_raw(content));
utl_smtp.close_data(c);
utl_smtp.quit(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
utl_smtp.quit(c);
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || sqlerrm);
END;
你用上面的procedure 再试一下,看报的错和上次相同吗?
utl_smtp.command(c,'YnFjXHJ1bm5pbmcgbHVv','');
utl_smtp.command(c,'emhvbmdndW93dWhhbg==','');
就是你说的有关验证用户密码的方法吗?
我不清楚参数里的'auth login'是什么意思,是否标识用户验证的命令标识?
还有下面两句里经过base64后的字符串对应什么内容?我试过了这段代码,在utl_smtp.command(c,'auth login',''); 时产生异常
错误信息为:
Failed to send mail due to the following error: ORA-20002: 502 Error: command not implemented请再次赐教,多谢very much!
utl_smtp.command(c,'emhvbmdndW93dWhhbg==','');
是经过base64加密后的用户名和密码,你首先要确定你的mail 服务器是不是要经过验证才能发mail.如果是的就需要上面的代码,里面的参数c 是打开mail服务器的连接!你要保证你带入的参数都是正确的!
所以我想我们所用的邮件服务器应该是不需要验证的,而且我的程序中
UTL_SMTP.HELO 是成功通过的,说明带入的参数C等是正确的。
为什么到utl_smtp.mail(c, sender)出错?是ORACLE需要什么组件么?
谢谢
utl_smtp.command(c,'YnFjXHJ1bm5pbmcgbHVv','');
utl_smtp.command(c,'emhvbmdndW93dWhhbg==','');
拿掉,然后再用我的代码试一下,到这一句'utl_smtp.mail(c, sender)'时是什么错?
oracle用的就是utl_smtp包来实现发mail 的功能,没有其他组件!