试下这个:http://www.huihoo.com/e_business/oracle/interact/email.html
解决方案 »
- 求助:关于LINUX SHELL接受存储过程OUT参数
- 数据库连接报错
- 如何在PL/SQL里面写程序的通用接口?
- oracle10g客户端与软件打包的问题?
- oracle 中两个到三表级联查询且要进行分页
- 为什么在oracle官方网站下载不下来oracle jdbc 10g的驱动呢??
- 使用oracle的UTL_FILE写入文件报错
- Oracle11g 还原数据库时出错
- TNS:12560错误?求救!!!!!
- 【紧急求助!】oracle触发器中如何获取某一个表的insert和delete语句操作失败
- 求救!急!急!急!trigger中表名用变量代替怎么不行?
- 用proc编写多线程进行数据库操作(请教高手)???
1.UTL_SMTP($ORACLE_HOME/rdbms/admin/utlsmtp.sql)
2.UTL_TCP($ORACLE_HOME/rdbms/admin/utltcp.sql)
3.JServer
已经安装的情况下,用SQLPLUS连接有相应提示如:
SQL*Plus: Release 9.2.0.1.0 - Production on 星期一 6月 27 13:28:37 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production====================================================================
发送邮件存储过程:Post_html_mail
入参说明:
p_to : 目标地址
p_subject:邮件主题
p_text:邮件纯文本内容
p_html:邮件HTML内容
====================================================================
create or replace procedure 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.1.2';--SMTP服务器地址
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, 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);
--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;
不過好象發郵件還是文本格式,或者說是plain格式
是不是轉入的參數要是html的原始檔啊?
p_text:邮件纯文本内容
p_html:邮件HTML内容
只要将html内容传进p_html即可。
如:
<html><body>你好!</body></html>传进p_html
特別感謝skystar99047!
可惜一次最多隻有100分。不知怎麼加分,抱歉了。