DECLARE
SendorAddress Varchar2(30) := '[email protected]';
ReceiverAddress varchar2(30) := '[email protected]';
EmailServer varchar2(30) := 'smtp.163.com';
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); utl_smtp.data(conn, mesg);
utl_smtp.quit(conn); END;
/
以上例子我执行的结果是
DECLARE
*
ERROR 位于第 1 行:
ORA-29279: SMTP 永久性错误: 500 Error: bad syntax
ORA-06512: 在"SYS.UTL_SMTP", line 17
ORA-06512: 在"SYS.UTL_SMTP", line 98
ORA-06512: 在"SYS.UTL_SMTP", line 221
ORA-06512: 在line 16当然,实际测试是,邮件地址用的都是合法的地方
我初步怀疑是邮件服务器的设置问题
如果我换成吸铁石的服务器,则报:
DECLARE
*
ERROR 位于第 1 行:
ORA-29279: SMTP 永久性错误: 554 #2175035 Authentication failure, refer to
http://www.naesasoft.com/help/umta_tx/smtp.html for help
ORA-06512: 在"SYS.UTL_SMTP", line 17
ORA-06512: 在"SYS.UTL_SMTP", line 98
ORA-06512: 在"SYS.UTL_SMTP", line 221
ORA-06512: 在line 16
SendorAddress Varchar2(30) := '[email protected]';
ReceiverAddress varchar2(30) := '[email protected]';
EmailServer varchar2(30) := 'smtp.163.com';
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); utl_smtp.data(conn, mesg);
utl_smtp.quit(conn); END;
/
以上例子我执行的结果是
DECLARE
*
ERROR 位于第 1 行:
ORA-29279: SMTP 永久性错误: 500 Error: bad syntax
ORA-06512: 在"SYS.UTL_SMTP", line 17
ORA-06512: 在"SYS.UTL_SMTP", line 98
ORA-06512: 在"SYS.UTL_SMTP", line 221
ORA-06512: 在line 16当然,实际测试是,邮件地址用的都是合法的地方
我初步怀疑是邮件服务器的设置问题
如果我换成吸铁石的服务器,则报:
DECLARE
*
ERROR 位于第 1 行:
ORA-29279: SMTP 永久性错误: 554 #2175035 Authentication failure, refer to
http://www.naesasoft.com/help/umta_tx/smtp.html for help
ORA-06512: 在"SYS.UTL_SMTP", line 17
ORA-06512: 在"SYS.UTL_SMTP", line 98
ORA-06512: 在"SYS.UTL_SMTP", line 221
ORA-06512: 在line 16
经过几天的工作,终于能够实现oracle发中文邮件.
create or replace procedure SP_MAIL_SHARE
IS
SendorAddress Varchar2(30) := '[email protected]';
EmailServer varchar2(30) := 'smtp.21cn.com';
Port number := 25;
conn utl_smtp.CONNECTION;
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
mesg VARCHAR2( 4000 char );mesg_body0 varchar2(4000 char)
:= '<html>
<head>
<title> </title>
</head>
<body bgcolor="#FFFFFF" link="#000080">
<table cellspacing="0" cellpadding="0" width="100%">';
font_red varchar2(100 char):='<font size="2" face="Arial" color="#ff0000" >';
font_blue varchar2(100 char):='<font size="2" face="Arial" color="#0000FF" >';
mesg_body1 varchar2(300 char):='谢谢您的支持.<br><br>
</font>
</body>
</html>';
BEGIN
conn:= utl_smtp.open_connection( EmailServer, Port );
utl_smtp.ehlo( conn, EmailServer );
utl_smtp.command(conn, 'AUTH LOGIN');
utl_smtp.command(conn,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('[email protected]'))));
utl_smtp.command(conn,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('password'))));
utl_smtp.mail( conn, SendorAddress);
utl_smtp.rcpt( conn, '[email protected]');
mesg:='' || crlf ||mesg_body0||FONT_RED||mesg_body1;
utl_smtp.open_data(conn);
utl_smtp.write_data(conn,'From:'||SendorAddress||utl_tcp.crlf);
utl_smtp.write_data(conn,'To:[email protected]' || utl_tcp.crlf );
utl_smtp.write_data(conn,'Date:'||to_char (sysdate, 'hh24:mi:ss dd-Mon-yyyy')||utl_tcp.crlf);
utl_smtp.write_data(conn,'Subject:Not approved over three days Alert'||utl_tcp.crlf);
utl_smtp.write_data(conn, 'MIME-Version: 1.0' || chr(13) || chr(10));
utl_smtp.write_data(conn, 'Content-type: text/html;Charset=GB2312;' || chr(13) || chr(10));
utl_smtp.write_data(conn, 'Content-Transfer-Encoding: base64' || chr(13) || chr(10));
utl_smtp.write_data(conn, chr(13) || chr(10));
utl_smtp.write_raw_data(conn, utl_encode.base64_encode(utl_raw.cast_to_raw(mesg)));
utl_smtp.write_data(conn, CRLF);
utl_smtp.close_data(conn);
utl_smtp.quit( conn );
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
END SP_MAIL_SHARE;
还是一样的错ERROR 位于第 1 行:
ORA-29279: SMTP 永久性错误: 500 Error: bad syntax
ORA-06512: 在"SYS.UTL_SMTP", line 17
ORA-06512: 在"SYS.UTL_SMTP", line 98
ORA-06512: 在"SYS.UTL_SMTP", line 221
ORA-06512: 在"SYSTEM.SP_MAIL_SHARE", line 29
ORA-06512: 在line 1刚才不报错是因为异常被捕获后回滚了