[ORACLE]UTL_SMTP发邮件

--ORACLE发邮件declare  SendorAddress   VARCHAR2(50) := '[e-mail]';  ReceiverAddress VARCHAR2(30) := '[e-mail]';  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