[ORACLE]UTL_SMTP发邮件

--ORACLE发邮件
declare
  SendorAddress   VARCHAR2(50) := '3edcvf@163.com';
  ReceiverAddress VARCHAR2(30) := '278676125@qq.com';
  EmailServer     VARCHAR2(30) := 'smtp.163.com';
  Port            NUMBER := 25;

  conn UTL_SMTP.CONNECTION;
  crlf VARCHAR2(2) := CHR(13) || CHR(10);

  mesg      VARCHAR2(4000);
  mesg_body VARCHAR2(4000);

BEGIN
  conn := utl_smtp.open_connection(EmailServer, Port);
  utl_smtp.helo(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('3edcvf'))));
  UTL_SMTP.COMMAND(conn, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('&pwd'))));
  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: Test Mail From OracleDatabase' || crlf ||
          'To: ' || ReceiverAddress || crlf ||
          'Content-Type: text/plain; Charset=GB2312' || crlf || '' || crlf || '你好啊,cry' || crlf;
   utl_smtp.open_data(conn);
  utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(mesg));
  utl_smtp.close_data(conn);
  --utl_smtp.data(conn, mesg);
  utl_smtp.quit(conn);
exception 
  when others then
    utl_smtp.quit(conn);
    raise;
END;

注:支持中文,不支持附件!  需要在SYS权限下执行,否则可能会报下面的421 service not available错误.

遇到的2个错误:

1.ORA-29279: SMTP 永久性错误: 550 Invalid User 

    解决: utl_smtp.mail和utl_smtp.rcpt发送和接收的地址前后需要加上'<'和'>'

2. ORA-29278: SMTP 临时性错误: 421 service not available

    解决:邮件服务器地址不对,这里要填SMTP服务器地址。163的邮箱填 'smtp.163.com'

 

3.SSL的SMTP服务器发送邮件,Oracle好像不支持

注:如做成存储过程,需要使用调用者权限,即加上AUTHID CURRENT_USER

所有异常CODE如下:

211	System status, or system help reply
214	Help message [Information on how to use the receiver or the meaning of a particular non-standard command; this reply is useful only to the human user]
220	<domain> Service ready
221	<domain> Service closing transmission channel
250	Requested mail action okay, completed
251	User not local; forwards to <forward-path>
252	OK, pending messages for node <node> started. Cannot VRFY user (for example, info is not local), but takes message for this user and attempts delivery.
253	OK, <messages> pending messages for node <node> started
354	Start mail input; end with <CRLF.CRLF>
355	Octet-offset is the transaction offset
421	<domain> Service not available, closing transmission channel (This can be a reply to any command if the service knows it must shut down.)
450	Requested mail action not taken: mailbox unavailable [for example, mailbox busy]
451	Requested action terminated: local error in processing
452	Requested action not taken: insufficient system storage
453	You have no mail.
454	TLS not available due to temporary reason. Encryption required for requested authentication mechanism.
458	Unable to queue messages for node <node>
459	Node <node> not allowed: reason
500	Syntax error, command unrecognized (This may include errors such as command line too long.)
501	Syntax error in parameters or arguments
502	Command not implemented
503	Bad sequence of commands
504	Command parameter not implemented
521	<Machine> does not accept mail.
530	Must issue a STARTTLS command first. Encryption required for requested authentication mechanism.
534	Authentication mechanism is too weak.
538	Encryption required for requested authentication mechanism.
550	Requested action not taken: mailbox unavailable [for, mailbox not found, no access]
551	User not local; please try <forward-path>
552	Requested mail action terminated: exceeded storage allocation
553	Requested action not taken: mailbox name not allowed [for example, mailbox syntax incorrect]
554	Transaction failed