怎样利用Oracle自动发mail 可以调用封装好了的存储过程发mail,当然原理是通过java发送的,你可以去这里下载例子,http://otn.oracle.com/sample_code/tech/pl_sql/htdocs/Utl_Smtp_Sample.html很不错的,我一直再用,很方便的! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 PROCEDURE sendmail1 ( 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; 我执行的时候出现的错误如下.是什么造成的?是不是系统中的mail服务没有运行呢?*ERROR 位于第1行:ORA-20001: 421 Service not availableORA-06512: at "SYS.UTL_SMTP", line 83ORA-06512: at "SYS.UTL_SMTP", line 356ORA-06512: at "APPS.SEND_MAIL", line 31ORA-06512: at line 1 请教关于用adodb链接远程oracle服务器的方法 oracle8i的数据库恢复,在线等,请各位高手帮忙一下。 linux下如何每天自动备份oracle数据库,高手请进 爱好ORACLE-做ORACLE的朋友们进ORACLE群里来哦! 求SQL 数据库异常断开 客户端连接服务器问题 oms 怎么安装啊 此类数据应该什么更新呢???? 用一条sql语句执行Oracle表更新问题! 关于时间函数Month_Between的用法,急! 在SQL Server中能运行但Oracle中运行不了的Select语句
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;
*
ERROR 位于第1行:
ORA-20001: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 83
ORA-06512: at "SYS.UTL_SMTP", line 356
ORA-06512: at "APPS.SEND_MAIL", line 31
ORA-06512: at line 1