oracle utlsmtp,[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, '');

utl_smtp.rcpt(conn, '');

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

Service ready

221

Service closing transmission channel

250

Requested mail action okay, completed

251

User not local; forwards to

252

OK, pending messages for nodestarted. CannotVRFYuser (for example, info is not local), but takes message for this user and attempts delivery.

253

OK,pending messages for nodestarted

354

Start mail input; end with

355

Octet-offset is the transaction offset

421

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

459

Nodenot 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

does not accept mail.

530

Must issue aSTARTTLScommand 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

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