oracle utl_smtp 500 bad syntax,改进oracle utl_mail包的smtp_server设定和密码验证不足
一.通過utl_smtp發(fā)送郵件的范例
DECLARE
c utl_smtp.connection;
BEGIN
c := utl_smtp.open_connection('smtp.exmail.qq.com');
utl_smtp.helo(c, 'smtp.exmail.qq.com');
utl_smtp.auth(c??????? => c,
username => 'selectshen@foxmail.com',
password => 'mail_password',
schemes? => 'LOGIN');
utl_smtp.mail(c, 'selectshen@foxmail.com');
utl_smtp.rcpt(c, '20084622@qq.com');
utl_smtp.open_data(c);
utl_smtp.write_data(c,
'From' || ': ' || '' ||
utl_tcp.CRLF);
utl_smtp.write_data(c,
'To' || ': ' || '<20084622@qq.com>' ||
utl_tcp.CRLF);
utl_smtp.write_raw_data(c, utl_raw.cast_to_raw(convert('Subject' || ': ' || '來自oracle數據庫的郵件' || utl_tcp.CRLF,'ZHS16GBK')));
UTL_SMTP.WRITE_DATA(c,
'Content-Type: '||'text/plain; charset=gb2312'||utl_tcp.CRLF);
utl_smtp.write_raw_data(c, utl_raw.cast_to_raw(convert( utl_tcp.CRLF || '你好,oracle!','ZHS16GBK')));
utl_smtp.close_data(c);
utl_smtp.quit(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
utl_smtp.quit(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
NULL;
END;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' ||
sqlerrm);
END;
二.改進utl_mail包
oracle從10.1.0.2提供一個ult_mail的包,用于發(fā)送郵件,它是對utl_smtp進行了封裝,簡化發(fā)郵件的步驟.
但存在的不足:
1.需要通過alter session或alter system去設定smtp_server
2.無法使用密碼驗證
通過對utl_mail包進行改進,將smtp_server的設定放在發(fā)送郵件的參數中,并在發(fā)送的存儲過程中加入密碼驗證,去增強實用性.
發(fā)郵件的存儲過程使用方法,可參考?? ?How to Use the UTL_MAIL Package (文檔 ID 269375.1).
并且要注意,從11g開始,如果不是sys用戶去發(fā)郵件,要設定ACL,可參考ORA-24247 Using UTL_TCP, UTL_HTTP, UTL_SMTP And UTL_MAIL With 11gR1 Or Later (文檔 ID 1209644.1).
下面是簡單的調用示例:
#改進后的包名個人定義為utl_email
UTL_EMAIL.SEND發(fā)送不含附件的郵件.
例如:
begin
scott.UTL_EMAIL.SEND(SMTP_SERVER?? => 'smtp.exmail.qq.com',
SENDER??????? => 'selectshen@foxmail.com',
AUTH_PASSWORD => 'mail_password',
recipients??? => '20084622@qq.com',
subject?????? => '數據庫測試',
message?????? => '中文',
mime_type???? => 'text/plain; charset=utf8'
);
end;
UTL_EMAIL.SEND_ATTACH_VARCHAR2發(fā)送包含文本附件的郵件.
例如:
begin
scott.UTL_EMAIL.SEND_ATTACH_VARCHAR2(SMTP_SERVER?? => 'smtp.exmail.qq.com',
SENDER??????? => 'selectshen@foxmail.com',
AUTH_PASSWORD => 'mail_password',
recipients??? => '20084622@qq.com',
subject?????? => 'oracle數據庫發(fā)送的測試郵件',
message?????? => '你好,oracle!',
mime_type???? => 'text/plain; charset=utf8',
attachment??? => '這是附件里的內容',
ATT_INLINE??? => FALSE,
att_filename? =>'a.txt'
);
end;
UTL_EMAIL.send_attach_raw發(fā)送包含二進制附件的郵件.
以下是改進后的utl_email包的定義,用戶只需新建以下包即可:#PACKAGECREATE OR REPLACE PACKAGE utl_email AUTHID CURRENT_USER AS
-------------
--? CONSTANTS
--
invalid_argument EXCEPTION;
invalid_priority EXCEPTION;
invalid_argument_errcode CONSTANT PLS_INTEGER := -29261;
PRAGMA EXCEPTION_INIT(invalid_argument, -29261);
PRAGMA EXCEPTION_INIT(INVALID_PRIORITY, -44101);
/*----------------------------------------------------------------
**
** SEND - send an email message
**
** This procedure packages and delivers an email message to the
** SMTP server specified by the following configuration parameters:
**
**?? SMTP_SERVER=my_server.my_company.com
**?? SMTP_DOMAIN=my_company.com
**
** SEND PROCEDURE
** IN
**?? sender?????? - sender address
**?? recipients?? - address(es) of 1 or more recipients, comma delimited
**?? cc?????????? - CC (carbon copy) recipient(s)), 1 or more addresses,
**??????????????????? comma delimited, default=NULL
**?? bcc????????? - BCC (blind carbon copy) recipient(s), 1 or more
**??????????????????? addresses, comma delimited, default=NULL
**?? subject????? - subject string, default=NULL
**?? message????? - message text, default=NULL
**?? mime_type??? - mime type, default='text/plain'
**?? priority???? - message priority, default=3, valid values are [1..5]
**
** SEND_ATTACH_VARCHAR2 PROCEDURE
** IN
**?? sender?????? - sender address
**?? recipients?? - address(es) of 1 or more recipients, comma delimited
**?? cc?????????? - CC (carbon copy) recipient(s)), 1 or more addresses,
**??????????????????? comma delimited, default=NULL
**?? bcc????????? - BCC (blind carbon copy) recipient(s), 1 or more
**??????????????????? addresses, comma delimited, default=NULL
**?? subject????? - subject string, default=NULL
**?? message????? - message text, default=NULL
**?? mime_type??? - mime type, default='text/plain'
**?? priority???? - message priority, default=3, valid values are [1..5]
**?? att_txt_inline - boolean specifying whether the attachment is viewable
**??????????????????? inline with the message body text, default=TRUE
**?? attachment?? - attachment text data
**?? att_mime_type- attachment mime_type, default='text/plain'
**?? att_filename - filename to be offered as a default upon saving the
**??????????????????? attachment to disk
**
** SEND_ATTACH_RAW PROCEDURE
** IN
**?? sender?????? - sender address
**?? recipients?? - address(es) of 1 or more recipients, comma delimited
**?? cc?????????? - CC (carbon copy) recipient(s)), 1 or more addresses,
**??????????????????? comma delimited, default=NULL
**?? bcc????????? - BCC (blind carbon copy) recipient(s), 1 or more
**??????????????????? addresses, comma delimited, default=NULL
**?? subject????? - subject string, default=NULL
**?? message????? - message text, default=NULL
**?? mime_type??? - mime type, default='text/plain'
**?? priority???? - message priority, default=3, valid values are [1..5]
**?? att_raw_inline - boolean specifying whether the attachment is viewable
**??????????????????? inline with the message body text, default=TRUE
**?? attachment?? - attachment RAW data
**?? att_mime_type- attachment mime_type, default='application/octet'
**?? att_filename - filename to be offered as a default upon saving the
**??????????????????? attachment to disk
**
*/
PROCEDURE send(SMTP_SERVER?? IN VARCHAR2 CHARACTER SET ANY_CS,
SMTP_PORT???? IN PLS_INTEGER DEFAULT 25,
SENDER??????? IN VARCHAR2 CHARACTER SET ANY_CS,
AUTH_PASSWORD IN VARCHAR2 CHARACTER SET ANY_CS,
recipients??? IN VARCHAR2 CHARACTER SET ANY_CS,
cc??????????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
bcc?????????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
subject?????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
message?????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
mime_type???? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',
priority????? IN PLS_INTEGER DEFAULT 3,
replyto?????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);
PROCEDURE send_attach_varchar2(SMTP_SERVER?? IN VARCHAR2 CHARACTER SET ANY_CS,
SMTP_PORT???? IN PLS_INTEGER DEFAULT 25,
SENDER??????? IN VARCHAR2 CHARACTER SET ANY_CS,
AUTH_PASSWORD IN VARCHAR2 CHARACTER SET ANY_CS,
recipients??? IN VARCHAR2 CHARACTER SET ANY_CS,
cc??????????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
bcc?????????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
subject?????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
message?????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
mime_type???? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',
priority????? IN PLS_INTEGER DEFAULT 3,
attachment??? IN VARCHAR2 CHARACTER SET ANY_CS,
att_inline??? IN BOOLEAN DEFAULT TRUE,
att_mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',
att_filename? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
replyto?????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);
PROCEDURE send_attach_raw(SMTP_SERVER?? IN VARCHAR2 CHARACTER SET ANY_CS,
SMTP_PORT???? IN PLS_INTEGER DEFAULT 25,
SENDER??????? IN VARCHAR2 CHARACTER SET ANY_CS,
AUTH_PASSWORD IN VARCHAR2 CHARACTER SET ANY_CS,
recipients??? IN VARCHAR2 CHARACTER SET ANY_CS,
cc??????????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
bcc?????????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
subject?????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
message?????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
mime_type???? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',
priority????? IN PLS_INTEGER DEFAULT 3,
attachment??? IN RAW,
att_inline??? IN BOOLEAN DEFAULT TRUE,
att_mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'application/octet',
att_filename? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
replyto?????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);
END;
/
#PACKAGE BODY
CREATE OR REPLACE PACKAGE BODY scott.utl_email IS
LONG_HEADER_FIELD??? CONSTANT INTEGER := 65;
SUBJECT_PIECE_LENGTH CONSTANT INTEGER := 40;
BOUNDARY CONSTANT VARCHAR2(256) := '------------4D8C24=_23F7E4A13B2357B3';
BAD_ARGUMENT EXCEPTION;
PRAGMA EXCEPTION_INIT(BAD_ARGUMENT, -29261);
FUNCTION LOOKUP_UNQUOTED_CHAR(STR? IN VARCHAR2 CHARACTER SET ANY_CS,
CHRS IN VARCHAR2) RETURN PLS_INTEGER AS
C??????????? VARCHAR2(5) CHARACTER SET STR%CHARSET;
I??????????? PLS_INTEGER;
LEN????????? PLS_INTEGER;
INSIDE_QUOTE BOOLEAN;
BEGIN
INSIDE_QUOTE := FALSE;
I??????????? := 1;
LEN????????? := LENGTH(STR);
WHILE (I <= LEN) LOOP
C := SUBSTR(STR, I, 1);
IF (INSIDE_QUOTE) THEN
IF (C = '"') THEN
INSIDE_QUOTE := FALSE;
ELSIF (C = '\') THEN
I := I + 1;
END IF;
GOTO NEXT_CHAR;
END IF;
IF (C = '"') THEN
INSIDE_QUOTE := TRUE;
GOTO NEXT_CHAR;
END IF;
IF (INSTR(CHRS, C) >= 1) THEN
RETURN I;
END IF;
<>
I := I + 1;
END LOOP;
RETURN 0;
END;
FUNCTION GET_ADDRESS(ADDR_LIST IN OUT VARCHAR2) RETURN VARCHAR2 IS
ADDR VARCHAR2(256);
I??? PLS_INTEGER;
BEGIN
ADDR_LIST := LTRIM(ADDR_LIST);
I???????? := LOOKUP_UNQUOTED_CHAR(ADDR_LIST, ',;');
IF (I >= 1) THEN
ADDR????? := SUBSTR(ADDR_LIST, 1, I - 1);
ADDR_LIST := SUBSTR(ADDR_LIST, I + 1);
ELSE
ADDR????? := ADDR_LIST;
ADDR_LIST := '';
END IF;
I := LOOKUP_UNQUOTED_CHAR(ADDR, '
IF (I >= 1) THEN
ADDR := SUBSTR(ADDR, I + 1);
I??? := INSTR(ADDR, '>');
IF (I >= 1) THEN
ADDR := SUBSTR(ADDR, 1, I - 1);
END IF;
END IF;
RETURN ADDR;
END;
FUNCTION ENCODE_VARCHAR2(DATA IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 IS
BEGIN
RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.QUOTED_PRINTABLE_ENCODE(UTL_RAW.CAST_TO_RAW(DATA)));
END;
FUNCTION ENCODE_RAW(DATA IN RAW) RETURN RAW IS
BEGIN
RETURN UTL_ENCODE.BASE64_ENCODE(DATA);
END;
FUNCTION ENCODE_HEADER(DATA IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET DATA%CHARSET IS
BEGIN
RETURN(UTL_ENCODE.MIMEHEADER_ENCODE(DATA));
END;
FUNCTION ENCODE_RECIPIENTS(RCPTS IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET RCPTS%CHARSET IS
START_LOC?????????? PLS_INTEGER := 1;
OCCUR_LOC?????????? PLS_INTEGER := 1;
SINGLE_RCPT???????? VARCHAR2(32767) CHARACTER SET RCPTS%CHARSET;
ENCODED_RCPTS?????? VARCHAR2(32767) CHARACTER SET RCPTS%CHARSET;
REMAINING_RCPTS???? VARCHAR2(32767) CHARACTER SET RCPTS%CHARSET;
ADDRESS_SEP???????? VARCHAR2(1) CHARACTER SET RCPTS%CHARSET;
ENCODED_SINGLE_RCPT VARCHAR2(32767) CHARACTER SET RCPTS%CHARSET;
FUNCTION ENCODE_SINGLE_RCPT(SINGLE_RCPT IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET SINGLE_RCPT%CHARSET IS
SEPARATOR_LOC?????? PLS_INTEGER := 0;
START_LOC?????????? PLS_INTEGER := 0;
SINGLE_RCPT_PIECE1? VARCHAR2(32767) CHARACTER SET SINGLE_RCPT%CHARSET;
SINGLE_RCPT_PIECE2? VARCHAR2(32767) CHARACTER SET SINGLE_RCPT%CHARSET;
ENCODED_SINGLE_RCPT VARCHAR2(32767) CHARACTER SET SINGLE_RCPT%CHARSET;
PIECE1_SPLIT??????? VARCHAR2(32767) CHARACTER SET SINGLE_RCPT%CHARSET;
ENCODED_PIECE?????? VARCHAR2(32767) CHARACTER SET SINGLE_RCPT%CHARSET;
BEGIN
ENCODED_SINGLE_RCPT := SINGLE_RCPT;
SEPARATOR_LOC?????? := LOOKUP_UNQUOTED_CHAR(SINGLE_RCPT, '
IF (SEPARATOR_LOC >= 1) THEN
ENCODED_SINGLE_RCPT := NULL;
SINGLE_RCPT_PIECE1? := SUBSTR(SINGLE_RCPT, 1, SEPARATOR_LOC - 1);
SINGLE_RCPT_PIECE2? := SUBSTR(SINGLE_RCPT, SEPARATOR_LOC);
IF (SINGLE_RCPT_PIECE1 IS NOT NULL) THEN
ENCODED_SINGLE_RCPT := ENCODE_HEADER(SINGLE_RCPT_PIECE1);
IF (LENGTH(ENCODED_SINGLE_RCPT) <= LONG_HEADER_FIELD) THEN
ENCODED_SINGLE_RCPT := ENCODED_SINGLE_RCPT || UTL_TCP.CRLF || ' ';
ELSE
SEPARATOR_LOC?????? := 0;
START_LOC?????????? := 1;
ENCODED_SINGLE_RCPT := NULL;
LOOP
SEPARATOR_LOC := INSTR(SINGLE_RCPT_PIECE1, ' ', START_LOC);
EXIT WHEN(SEPARATOR_LOC = 0);
PIECE1_SPLIT := SUBSTR(SINGLE_RCPT_PIECE1,
START_LOC,
SEPARATOR_LOC - START_LOC + 1);
START_LOC??? := SEPARATOR_LOC + 1;
IF (PIECE1_SPLIT IS NOT NULL) THEN
ENCODED_PIECE := ENCODE_HEADER(PIECE1_SPLIT);
IF (LENGTH(ENCODED_PIECE) > LONG_HEADER_FIELD) THEN
GOTO REPORT_ERROR;
END IF;
ENCODED_SINGLE_RCPT := ENCODED_SINGLE_RCPT || ENCODED_PIECE ||
UTL_TCP.CRLF || ' ';
END IF;
END LOOP;
PIECE1_SPLIT := SUBSTR(SINGLE_RCPT_PIECE1,
START_LOC,
LENGTH(SINGLE_RCPT_PIECE1) - START_LOC + 1);
IF (PIECE1_SPLIT IS NOT NULL) THEN
ENCODED_PIECE := ENCODE_HEADER(PIECE1_SPLIT);
IF (LENGTH(ENCODED_PIECE) > LONG_HEADER_FIELD) THEN
GOTO REPORT_ERROR;
END IF;
ENCODED_SINGLE_RCPT := ENCODED_SINGLE_RCPT || ENCODED_PIECE ||
UTL_TCP.CRLF || ' ';
END IF;
END IF;
END IF;
IF (LENGTH(SINGLE_RCPT_PIECE2) > LONG_HEADER_FIELD) THEN
GOTO REPORT_ERROR;
END IF;
ENCODED_SINGLE_RCPT := ENCODED_SINGLE_RCPT || SINGLE_RCPT_PIECE2;
END IF;
RETURN ENCODED_SINGLE_RCPT;
<>
RAISE BAD_ARGUMENT;
END;
BEGIN
ENCODED_RCPTS?? := NULL;
REMAINING_RCPTS := RCPTS;
LOOP
OCCUR_LOC := LOOKUP_UNQUOTED_CHAR(REMAINING_RCPTS, ';,');
EXIT WHEN(OCCUR_LOC = 0);
SINGLE_RCPT := SUBSTR(REMAINING_RCPTS, 1, OCCUR_LOC - 1);
ADDRESS_SEP := SUBSTR(REMAINING_RCPTS, OCCUR_LOC, 1);
REMAINING_RCPTS := SUBSTR(REMAINING_RCPTS, OCCUR_LOC + 1);
IF (SINGLE_RCPT IS NOT NULL) THEN
ENCODED_SINGLE_RCPT := ENCODE_SINGLE_RCPT(SINGLE_RCPT);
ENCODED_RCPTS?????? := ENCODED_RCPTS || ENCODED_SINGLE_RCPT ||
ADDRESS_SEP || UTL_TCP.CRLF || ' ';
END IF;
END LOOP;
IF (REMAINING_RCPTS IS NOT NULL) THEN
ENCODED_SINGLE_RCPT := ENCODE_SINGLE_RCPT(REMAINING_RCPTS);
ENCODED_RCPTS?????? := ENCODED_RCPTS || ENCODED_SINGLE_RCPT;
END IF;
RETURN ENCODED_RCPTS;
END;
PROCEDURE SEND_I(SMTP_SERVER??? IN VARCHAR2 CHARACTER SET ANY_CS,
SMTP_PORT????? IN PLS_INTEGER,
SENDER???????? IN VARCHAR2 CHARACTER SET ANY_CS,
AUTH_PASSWORD? IN VARCHAR2 CHARACTER SET ANY_CS,
RECIPIENTS???? IN VARCHAR2 CHARACTER SET ANY_CS,
CC???????????? IN VARCHAR2 CHARACTER SET ANY_CS,
BCC??????????? IN VARCHAR2 CHARACTER SET ANY_CS,
SUBJECT??????? IN VARCHAR2 CHARACTER SET ANY_CS,
MESSAGE??????? IN VARCHAR2 CHARACTER SET ANY_CS,
MIME_TYPE????? IN VARCHAR2 CHARACTER SET ANY_CS,
PRIORITY?????? IN PLS_INTEGER,
TXT_ATTACHMENT IN VARCHAR2 CHARACTER SET ANY_CS,
RAW_ATTACHMENT IN RAW,
ATT_MIME_TYPE? IN VARCHAR2 CHARACTER SET ANY_CS,
ATT_INLINE???? IN BOOLEAN,
ATT_FILENAME?? IN VARCHAR2 CHARACTER SET ANY_CS,
REPLYTO??????? IN VARCHAR2 CHARACTER SET ANY_CS) IS
MAIL_CONN???? UTL_SMTP.CONNECTION;
CRLF????????? VARCHAR2(10) := UTL_TCP.CRLF;
ATTACH_FLAG?? PLS_INTEGER := 0;
TEXT_TYPE???? NUMBER := 1;
RAW_TYPE????? NUMBER := 2;
SUBJECT_TEMP? VARCHAR2(32767);
NONE_TYPE???? NUMBER := 0;
SENDER_COPY?? VARCHAR2(32767) := SENDER;
PRIORITY_COPY PLS_INTEGER := PRIORITY;
ALL_RCPTS???? VARCHAR2(32767) := RECIPIENTS;
CONNECTION_OPENED BOOLEAN := FALSE;
BEGIN
IF (TXT_ATTACHMENT IS NOT NULL) THEN
ATTACH_FLAG := TEXT_TYPE;
ELSE
IF (RAW_ATTACHMENT IS NOT NULL) THEN
ATTACH_FLAG := RAW_TYPE;
END IF;
END IF;
MAIL_CONN???????? := UTL_SMTP.OPEN_CONNECTION(SMTP_SERVER, SMTP_PORT);
CONNECTION_OPENED := TRUE;
UTL_SMTP.HELO(MAIL_CONN, SMTP_SERVER);
if (AUTH_PASSWORD is not null) then
utl_smtp.auth(c??????? => MAIL_CONN,
username => SENDER_COPY,
password => AUTH_PASSWORD,
schemes? => 'LOGIN');
end if;
UTL_SMTP.MAIL(MAIL_CONN, SENDER_COPY);
IF (CC IS NOT NULL) THEN
ALL_RCPTS := ALL_RCPTS || ', ' || CC;
END IF;
IF (BCC IS NOT NULL) THEN
ALL_RCPTS := ALL_RCPTS || ', ' || BCC;
END IF;
WHILE (ALL_RCPTS IS NOT NULL) LOOP
UTL_SMTP.RCPT(MAIL_CONN, '');
END LOOP;
UTL_SMTP.OPEN_DATA(MAIL_CONN);
IF (SENDER IS NOT NULL) THEN
UTL_SMTP.WRITE_DATA(MAIL_CONN,
'From: ' || ENCODE_RECIPIENTS(SENDER) || CRLF);
ELSE
RAISE BAD_ARGUMENT;
END IF;
IF (RECIPIENTS IS NOT NULL) THEN
UTL_SMTP.WRITE_DATA(MAIL_CONN,
'To: ' || ENCODE_RECIPIENTS(RECIPIENTS) || CRLF);
ELSE
RAISE BAD_ARGUMENT;
END IF;
IF (CC IS NOT NULL) THEN
UTL_SMTP.WRITE_DATA(MAIL_CONN,
'CC: ' || ENCODE_RECIPIENTS(CC) || CRLF);
END IF;
IF (REPLYTO IS NOT NULL) THEN
UTL_SMTP.WRITE_DATA(MAIL_CONN,
'Reply-To: ' || ENCODE_RECIPIENTS(REPLYTO) || CRLF);
END IF;
UTL_SMTP.WRITE_DATA(MAIL_CONN,
'Orig-Date: ' ||
TO_CHAR(CURRENT_TIMESTAMP,
'Dy Mon YYYY HH24:MI:SS TZHTZM') || CRLF);
SUBJECT_TEMP := SUBJECT;
DECLARE
CURRENTPIECE??? VARCHAR2(32767) := NULL;
ENCODEDPIECE??? VARCHAR2(32767) := NULL;
PIECELENGTH???? NUMBER := SUBJECT_PIECE_LENGTH;
REMAININGLENGTH NUMBER := LENGTH(SUBJECT_TEMP);
FIRSTPIECE????? BOOLEAN := TRUE;
BEGIN
WHILE (REMAININGLENGTH > 0) LOOP
CURRENTPIECE := SUBSTR(SUBJECT_TEMP, 1, PIECELENGTH);
ENCODEDPIECE := ENCODE_HEADER(CURRENTPIECE);
IF (LENGTH(ENCODEDPIECE) <= LONG_HEADER_FIELD) THEN
IF (FIRSTPIECE = FALSE) THEN
UTL_SMTP.WRITE_DATA(MAIL_CONN, ' ' || ENCODEDPIECE || CRLF);
ELSE
UTL_SMTP.WRITE_DATA(MAIL_CONN,
'Subject: ' || ENCODEDPIECE || CRLF);
FIRSTPIECE := FALSE;
END IF;
SUBJECT_TEMP??? := SUBSTR(SUBJECT_TEMP,
PIECELENGTH + 1,
REMAININGLENGTH);
REMAININGLENGTH := LENGTH(SUBJECT_TEMP);
PIECELENGTH := SUBJECT_PIECE_LENGTH;
ELSE
PIECELENGTH := PIECELENGTH / 2;
IF (PIECELENGTH = 0) THEN
RAISE BAD_ARGUMENT;
END IF;
END IF;
END LOOP;
END;
IF (PRIORITY IS NOT NULL) THEN
IF ((PRIORITY > 5) OR (PRIORITY < 1)) THEN
RAISE INVALID_PRIORITY;
END IF;
UTL_SMTP.WRITE_DATA(MAIL_CONN,
'X-Priority: ' || PRIORITY_COPY || CRLF);
END IF;
IF (ATTACH_FLAG > NONE_TYPE) THEN
UTL_SMTP.WRITE_DATA(MAIL_CONN,
'Content-Type: multipart/mixed;' || CRLF);
UTL_SMTP.WRITE_DATA(MAIL_CONN,
' boundary="' || BOUNDARY || '"' || CRLF);
UTL_SMTP.WRITE_DATA(MAIL_CONN, CRLF);
UTL_SMTP.WRITE_DATA(MAIL_CONN,
'This is a multi-part message in MIME format.' || CRLF);
UTL_SMTP.WRITE_DATA(MAIL_CONN, '--' || BOUNDARY || CRLF);
END IF;
UTL_SMTP.WRITE_DATA(MAIL_CONN, 'Content-Type: ' || MIME_TYPE || CRLF);
IF ((MESSAGE IS NULL) OR (INSTR(UPPER(MIME_TYPE), 'CHARSET') = 0) OR
(INSTR(UPPER(MIME_TYPE), 'US-ASCII') != 0)) THEN
UTL_SMTP.WRITE_DATA(MAIL_CONN,
'Content-Transfer-Encoding: 7bit' || CRLF);
UTL_SMTP.WRITE_DATA(MAIL_CONN, CRLF);
UTL_SMTP.WRITE_DATA(MAIL_CONN,
NVL(MESSAGE, ' ') || CRLF || CRLF || CRLF);
ELSE
UTL_SMTP.WRITE_DATA(MAIL_CONN,
'Content-Transfer-Encoding: quoted-printable' || CRLF);
UTL_SMTP.WRITE_DATA(MAIL_CONN, CRLF);
UTL_SMTP.WRITE_DATA(MAIL_CONN,
ENCODE_VARCHAR2(MESSAGE) || CRLF || CRLF || CRLF);
END IF;
IF (ATTACH_FLAG > NONE_TYPE) THEN
UTL_SMTP.WRITE_DATA(MAIL_CONN, '--' || BOUNDARY || CRLF);
UTL_SMTP.WRITE_DATA(MAIL_CONN,
'Content-Type: ' || ATT_MIME_TYPE || ';' || CRLF);
UTL_SMTP.WRITE_DATA(MAIL_CONN,
' name="' || NVL(ATT_FILENAME, ' ') || '"' || CRLF);
IF (ATTACH_FLAG = TEXT_TYPE) THEN
UTL_SMTP.WRITE_DATA(MAIL_CONN,
'Content-Transfer-Encoding: quoted-printable' || CRLF);
ELSE
UTL_SMTP.WRITE_DATA(MAIL_CONN,
'Content-Transfer-Encoding: base64' || CRLF);
END IF;
IF (ATT_INLINE) THEN
UTL_SMTP.WRITE_DATA(MAIL_CONN,
'Content-Disposition: inline;' || CRLF);
UTL_SMTP.WRITE_DATA(MAIL_CONN,
' filename="' || NVL(ATT_FILENAME, ' ') || '"' || CRLF || CRLF || CRLF);
ELSE
UTL_SMTP.WRITE_DATA(MAIL_CONN,
'Content-Disposition: attachment;' || CRLF);
UTL_SMTP.WRITE_DATA(MAIL_CONN,
' filename="' || NVL(ATT_FILENAME, ' ') || '"' || CRLF || CRLF || CRLF);
END IF;
IF (ATTACH_FLAG = TEXT_TYPE) THEN
UTL_SMTP.WRITE_DATA(MAIL_CONN, ENCODE_VARCHAR2(TXT_ATTACHMENT));
ELSE
UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN, ENCODE_RAW(RAW_ATTACHMENT));
END IF;
UTL_SMTP.WRITE_DATA(MAIL_CONN, CRLF);
UTL_SMTP.WRITE_DATA(MAIL_CONN, '--' || BOUNDARY || '--' || CRLF);
END IF;
UTL_SMTP.CLOSE_DATA(MAIL_CONN);
UTL_SMTP.QUIT(MAIL_CONN);
EXCEPTION
WHEN OTHERS THEN
IF (CONNECTION_OPENED) THEN
UTL_SMTP.CLOSE_CONNECTION(MAIL_CONN);
END IF;
RAISE;
END;
PROCEDURE SEND(SMTP_SERVER?? IN VARCHAR2 CHARACTER SET ANY_CS,
SMTP_PORT???? IN PLS_INTEGER DEFAULT 25,
SENDER??????? IN VARCHAR2 CHARACTER SET ANY_CS,
AUTH_PASSWORD IN VARCHAR2 CHARACTER SET ANY_CS,
RECIPIENTS??? IN VARCHAR2 CHARACTER SET ANY_CS,
CC??????????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
BCC?????????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
SUBJECT?????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
MESSAGE?????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
MIME_TYPE???? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',
PRIORITY????? IN PLS_INTEGER DEFAULT 3,
REPLYTO?????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL) IS
BEGIN
SEND_I(SMTP_SERVER,
SMTP_PORT,
SENDER,
AUTH_PASSWORD,
RECIPIENTS,
CC,
BCC,
SUBJECT,
MESSAGE,
MIME_TYPE,
PRIORITY,
NULL,
NULL,
NULL,
NULL,
NULL,
REPLYTO);
END;
PROCEDURE SEND_ATTACH_VARCHAR2(SMTP_SERVER?? IN VARCHAR2 CHARACTER SET ANY_CS,
SMTP_PORT???? IN PLS_INTEGER DEFAULT 25,
SENDER??????? IN VARCHAR2 CHARACTER SET ANY_CS,
AUTH_PASSWORD IN VARCHAR2 CHARACTER SET ANY_CS,
RECIPIENTS??? IN VARCHAR2 CHARACTER SET ANY_CS,
CC??????????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
BCC?????????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
SUBJECT?????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
MESSAGE?????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
MIME_TYPE???? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',
PRIORITY????? IN PLS_INTEGER DEFAULT 3,
ATTACHMENT??? IN VARCHAR2 CHARACTER SET ANY_CS,
ATT_INLINE??? IN BOOLEAN DEFAULT TRUE,
ATT_MIME_TYPE IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',
ATT_FILENAME? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
REPLYTO?????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL) IS
BEGIN
SEND_I(SMTP_SERVER,
SMTP_PORT,
SENDER,
AUTH_PASSWORD,
RECIPIENTS,
CC,
BCC,
SUBJECT,
MESSAGE,
MIME_TYPE,
PRIORITY,
ATTACHMENT,
NULL,
ATT_MIME_TYPE,
ATT_INLINE,
ATT_FILENAME,
REPLYTO);
END;
PROCEDURE SEND_ATTACH_RAW(SMTP_SERVER?? IN VARCHAR2 CHARACTER SET ANY_CS,
SMTP_PORT???? IN PLS_INTEGER DEFAULT 25,
SENDER??????? IN VARCHAR2 CHARACTER SET ANY_CS,
AUTH_PASSWORD IN VARCHAR2 CHARACTER SET ANY_CS,
RECIPIENTS??? IN VARCHAR2 CHARACTER SET ANY_CS,
CC??????????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
BCC?????????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
SUBJECT?????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
MESSAGE?????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
MIME_TYPE???? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',
PRIORITY????? IN PLS_INTEGER DEFAULT 3,
ATTACHMENT??? IN RAW,
ATT_INLINE??? IN BOOLEAN DEFAULT TRUE,
ATT_MIME_TYPE IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'application/octet',
ATT_FILENAME? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
REPLYTO?????? IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL) IS
BEGIN
SEND_I(SMTP_SERVER,
SMTP_PORT,
SENDER,
AUTH_PASSWORD,
RECIPIENTS,
CC,
BCC,
SUBJECT,
MESSAGE,
MIME_TYPE,
PRIORITY,
NULL,
ATTACHMENT,
ATT_MIME_TYPE,
ATT_INLINE,
ATT_FILENAME,
REPLYTO);
END;
END;
總結
以上是生活随笔為你收集整理的oracle utl_smtp 500 bad syntax,改进oracle utl_mail包的smtp_server设定和密码验证不足的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： oracle var/tmp,关于/va
- 下一篇： 查看oracle自定义函数,Oracle