create or replace procedure Send_mail(sender IN VARCHAR2,
recipients IN VARCHAR2,
subject IN VARCHAR2,
result OUT integer
) is
v_Addr varchar2(256);
v_Message varchar2(1000);
v_MailHost varchar2(50):='192.1.1.88';
v_MailConnection UTL_SMTP.CONNECTION;
v_Time_Gap number :=0;
v_HeartBeat_count number :=0;
v_err varchar2(100);
begin
result:=0;
v_Addr:=recipients;
v_Message:='From:' || Sender || CHR(10) ||'To:'||v_Addr_list||CHR(10)||'Subject:'||subject||utl_tcp.crlf;
v_MailConnection:=UTL_SMTP.OPEN_CONNECTION(v_MailHost,25);
UTL_SMTP.HELO(v_MailConnection,v_MailHost);
UTL_SMTP.MAIL(v_MailConnection,sender);
UTL_SMTP.RCPT(v_MailConnection,v_Addr);
UTL_SMTP.DATA(v_MailConnection,v_Message);
UTL_SMTP.QUIT(v_MailConnection);
result:=1;
exception
when others then
result:=0;
v_err:=substr(sqlerrm,1,100);
end Send_mail;
recipients IN VARCHAR2,
subject IN VARCHAR2,
result OUT integer
) is
v_Addr varchar2(256);
v_Message varchar2(1000);
v_MailHost varchar2(50):='192.1.1.88';
v_MailConnection UTL_SMTP.CONNECTION;
v_Time_Gap number :=0;
v_HeartBeat_count number :=0;
v_err varchar2(100);
begin
result:=0;
v_Addr:=recipients;
v_Message:='From:' || Sender || CHR(10) ||'To:'||v_Addr_list||CHR(10)||'Subject:'||subject||utl_tcp.crlf;
v_MailConnection:=UTL_SMTP.OPEN_CONNECTION(v_MailHost,25);
UTL_SMTP.HELO(v_MailConnection,v_MailHost);
UTL_SMTP.MAIL(v_MailConnection,sender);
UTL_SMTP.RCPT(v_MailConnection,v_Addr);
UTL_SMTP.DATA(v_MailConnection,v_Message);
UTL_SMTP.QUIT(v_MailConnection);
result:=1;
exception
when others then
result:=0;
v_err:=substr(sqlerrm,1,100);
end Send_mail;
解决方案 »
- Oracle共享内存SGA区域有哪些组件可以被数据库自动调整大小
- sql优化.....关于in
- 未找到数据 怎么解决
- 技巧:如何用一个SQL语句,把某月所有的天数列出来。
- 谁能告诉我在新建的数据库里在哪建我的表啊?
- 请问oracle9i中SALES_HISTORY_ROLE的权限是什么,在8i中相应role是什么 谢谢!
- 请问:在ORACLE9i中的Enterprise Manager Console启动不起来,怎么解决?
- 复杂的select,各位帮忙看看。
- $ORACLE_HOME/bin/sqlldr是什么意思?
- 请问怎么把sequence作为参数传递给一个函数?
- 请教通信通道文件结束错误
- 请问在SQL怎么使用变量!
sender IN VARCHAR2,
recipient IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2,
send IN VARCHAR2
)
IS
mailhost VARCHAR2(100) := '10.*.*.*';
mail_conn utl_smtp.connection; crlf VARCHAR2(2) := CHR (13) || CHR (10);
mesg VARCHAR2(3000);
BEGIN
--htp.p('Contacting SMTP server: '||mailhost||'...');
mail_conn := utl_smtp.open_connection (mailhost, 25);
--htp.p('Successfully connected to SMTP server: '||mailhost);
--mesg := 'Date: ' ||
-- TO_CHAR (SYSDATE,'dd Mon yy hh24:mi:ss') ||
--TO_CHAR (SYSDATE,'yy mm dd hh24:mi:ss') ||
mesg:= 'Date: ' || to_char(sysdate,'YYYY-MON-DD HH24:MI:SS','NLS_DATE_LANGUAGE=AMERICAN') || crlf ||
--crlf ||
'From: <' ||
sender ||
'>' ||
crlf ||
'Subject: ' ||
subject ||
crlf ||
'To: ' ||
recipient ||
crlf ||
'' ||crlf ||
message;
utl_smtp.helo (mail_conn, mailhost);
utl_smtp.mail (mail_conn, sender);
utl_smtp.rcpt (mail_conn, recipient);
utl_smtp.data (mail_conn, mesg);
utl_smtp.quit (mail_conn);
END SENDMAIL1;
CREATE OR REPLACE PROCEDURE SEND_EMAIL( p_sender IN VARCHAR2, --发送者
p_recipient IN VARCHAR2, --接收者
p_title IN VARCHAR2, --主题
p_message IN VARCHAR2, --内容
p_priority IN NUMBER, p_server IN vARCHAR2, --接收邮件的服务器
p_port IN VARCHAR2 --端口号
) AS
l_mail_conn UTL_SMTP.CONNECTION;
BEGIN
l_mail_conn := UTL_SMTP.OPEN_CONNECTION(p_server, p_port); --默认端口号 UTL_SMTP.HELO(l_mail_conn, p_server);
UTL_SMTP.MAIL(l_mail_conn, p_sender);
UTL_SMTP.RCPT(l_mail_conn, p_recipient); UTL_SMTP.OPEN_DATA(l_mail_conn);
--Email头 /*
UTL_SMTP.WRITE_DATA(l_mail_conn,'From:' || p_sender || UTL_TCP.CRLF); --发送者
UTL_SMTP.WRITE_DATA(l_mail_conn,'To:' || p_sender || UTL_TCP.CRLF); --接收者
UTL_SMTP.WRITE_DATA(l_mail_conn,'Cc:' || p_sender || UTL_TCP.CRLF); --抄送者
UTL_SMTP.WRITE_DATA(l_mail_conn,'Subject:' || p_title || UTL_TCP.CRLF); --主题
*/
UTL_SMTP.WRITE_DATA(l_mail_conn,utl_raw.cast_to_raw(convert('From:' || p_recipient || utl_tcp.CRLF,'gb2312')));UTL_SMTP.WRITE_DATA(l_mail_conn,utl_raw.cast_to_raw(convert('To:' || p_sender || utl_tcp.CRLF,'gb2312')));UTL_SMTP.WRITE_DATA(l_mail_conn,utl_raw.cast_to_raw(convert('Cc: cccc' || utl_tcp.CRLF,'gb2312')));UTL_SMTP.WRITE_DATA(l_mail_conn,utl_raw.cast_to_raw(convert('Subject:' || p_title || utl_tcp.CRLF,'gb2312')));UTL_SMTP.WRITE_DATA(l_mail_conn,'X-Priority: ' || p_priority || UTL_TCP.CRLF); --优先级--内容
-- UTL_SMTP.WRITE_DATA(l_mail_conn, p_message); --内容UTL_SMTP.WRITE_DATA(l_mail_conn, utl_raw.cast_to_raw(convert(p_message,'gb2312')));UTL_SMTP.CLOSE_DATA(l_mail_conn);
UTL_SMTP.QUIT(l_mail_conn);
EXCEPTION
WHEN OTHERS THEN
UTL_SMTP.QUIT(l_mail_conn);
END;
参考以下:
http://otn.oracle.com/sample_code/tech/pl_sql/htdocs/Utl_Smtp_Sample.html