测试过多次，终于成功：oracle内发送email

nattyneat

create or replace procedure ProcSendEmail(txt varchar2)
IS
--sender's email address
SendorAddress Varchar2(30) := 'xxx@xxx.com';   
--receiver 's email address
ReceiverAddress varchar2(30) := 'xxx@xxx.com';
--mail server's name or ip address
EmailServer varchar2(30) := '10.10.1.168';
Port number := 25;

conn UTL_SMTP.CONNECTION;
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
contentmsg VARCHAR2( 60 ) := 'hello';
mesg VARCHAR2( 4000 );
mesg_body varchar2(4000);
BEGIN

conn:= utl_smtp.open_connection( EmailServer, Port );
utl_smtp.helo( conn, EmailServer );
utl_smtp.mail( conn, SendorAddress);
utl_smtp.rcpt( conn, ReceiverAddress );

mesg:=
'Content-Type: text/plain; Charset=GB2312' || crlf ||
'Date:' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
'From:' || SendorAddress || crlf ||
'Subject: Sending Mail From Database' || crlf ||
'To: '|| ReceiverAddress || crlf ||
'Content-Type: text/plain; Charset=GB2312' || crlf ||
'' || crlf || txt || crlf ;

utl_smtp.data( conn, mesg );
utl_smtp.quit( conn );

END;

szgz

楼主的平台？

yangqingdelphi

ORA-20001: 421 Service not available
ORA-06512: 在"SYS.UTL_SMTP", line 83
ORA-06512: 在"SYS.UTL_SMTP", line 121
ORA-06512: 在"SYS.PROCSENDEMAIL", line 18
ORA-06512: 在line 7
我的怎么不能运行啊.能不能说说为什么啊

liuxnp

我试过了，可以发送！！！

yangqingdelphi

楼上,我自已的邮箱难道不用登录吗?
我利用sina的smtp.sina.com.cn
出现
ORA-20002: 553 ________________________________________________________________________________     SMTP 登录出错。新浪邮件系统增加了 SMTP 服务认证，从 2001 年 2 月 28 日起，只有新浪会员才能享受此项服务。请访问新浪邮件网站查看有关通知及设置方法，网址是：http://mail.sina.com.cn/smtp.html - authentication required, see http://mail.sina.com.cn/smtp.html for setup details.     ____________________________________________________________________________________________________
ORA-06512: 在"SYS.UTL_SMTP", line 86
ORA-06512: 在"SYS.UTL_SMTP", line 204
ORA-06512: 在"SYS.PROCSENDEMAIL", line 20
ORA-06512: 在line 7

liuxnp

不能发送中文，希望能改进
SQL> exec ProcSendEmail("别人流的泪将是我的教训";
BEGIN ProcSendEmail("别人流的泪将是我的教训"; END;

                    *
ERROR at line 1:
ORA-06550: line 1, column 21:
PLS-00201: identifier '1pHKAw5D@a=+JGNR5D=LQ5' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

jiangzx

必须有Jserver这个部件啊

yangqingdelphi

怪了,我发了n次,有一次成功,但不知是哪一次.郁闷中....

xie3000

请问
EmailServer ----------------- '10.10.1.168';          是指定本单位的MAIL SERVER吗？
Port number ------------------- 25;   是怎么确定的

xie3000

明白拉EmailServer 要求是有效的MAIL服务器否则出现yangqingdelphi
遇到的错误
谢谢
nattyneat！