改進oracle utl_mail包的smtp_server設定和密碼驗證不足
一.透過utl_smtp傳送郵件的範例
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的包,用於傳送郵件,它是對utl_smtp進行了封裝,簡化發郵件的步驟.
但存在的不足:
1.需要透過alter session或alter system去設定smtp_server
2.無法使用密碼驗證
透過對utl_mail包進行改進,將smtp_server的設定放在傳送郵件的引數中,並在傳送的儲存過程中加入密碼驗證,去增強實用性.
發郵件的儲存過程使用方法,可參考 How to Use the UTL_MAIL Package (文件 ID 269375.1).
並且要注意,從11g開始,如果不是sys使用者去發郵件,要設定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傳送不含附件的郵件.
例如:
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傳送包含文字附件的郵件.
例如:
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資料庫傳送的測試郵件',
message => '你好,oracle!',
mime_type => 'text/plain; charset=utf8',
attachment => '這是附件裡的內容',
ATT_INLINE => FALSE,
att_filename =>'a.txt'
);
end;
UTL_EMAIL.send_attach_raw傳送包含二進位制附件的郵件.
以下是改進後的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, '<' || GET_ADDRESS(ALL_RCPTS) || '>');
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;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28539951/viewspace-2081412/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle本地驗證和密碼檔案Oracle密碼
- Oracle 密碼驗證方式Oracle密碼
- ORACLE 密碼驗證函式Oracle密碼函式
- Oracle 8i 密碼驗證Oracle密碼
- profile進行密碼設定密碼
- oracle 開啟複雜密碼驗證Oracle密碼
- ORACLE密碼錯誤驗證延遲Oracle密碼
- oracle驗證設定的event是否生效：Oracle
- redis設定認證密碼操作Redis密碼
- 實時驗證碼技術可改進生物識別身份驗證
- Oracle資料庫密碼延遲驗證Oracle資料庫密碼
- ACCESS 密碼驗證/文字驗證中的小坑密碼
- Oracle UTL_MAIL郵件包程式使用實踐OracleAI
- Oracle 密碼永不過期設定Oracle密碼
- ORACLE listener監聽設定密碼Oracle密碼
- ORACLE 11g的密碼錯誤延時驗證Oracle密碼
- Redis 密碼設定和檢視Redis密碼
- oracle 11g 密碼延遲驗證問題Oracle密碼
- 設定 Oracle 監聽器密碼(LISTENER)Oracle密碼
- Oracle Listener設定密碼示例說明Oracle密碼
- 模型評估與改進：交叉驗證模型
- oracle 9i改system的密碼Oracle密碼
- Linux下wireshark抓包驗證telnet明文傳輸密碼Linux密碼
- 行為驗證碼安全策略設定
- ORACLE更改密碼時不能使用最近5次密碼的設定方法Oracle密碼
- Oracle11g新增密碼錯誤延遲驗證Oracle密碼
- 1.6.5.1. 準備使用密碼檔案進行身份驗證密碼
- QT 驗證改進後Bresenham演算法QT演算法
- 無密碼驗證：客戶端密碼客戶端
- 自定義密碼驗證函式密碼函式
- orapwd建立密碼及驗證方法密碼
- 使用Docker部署帶密碼驗證的RedisDocker密碼Redis
- redis設定密碼Redis密碼
- 圖形驗證碼圖片樣式設定
- JavaScript驗證碼生成和驗證效果JavaScript
- 安裝Oracle RAC時,不能驗證ASMSNMP密碼問題的解決OracleASM密碼
- js驗證使用者名稱和密碼為空程式碼JS密碼
- EditText設定密碼隱藏和顯示密碼