ORACLE 8I 中使用 pl/sql(utl_smtp) 傳送郵件的一些體會

2005年過去了，本想寫點本年總結的，但一想到朋友就揪心得痛，想提筆,一想往事就不知道幹些什麼了,想來想去還是沒有過自己的這一關,好久沒有寫技術方面的文章了就寫篇技術方面的作為2005年的紀念。

這幾天要把ORACLE的郵件傳送功能加到系統流程中去，寫點自己的摸索出的經驗。

ORACLE,PL/SQL,UTL_SMTP,UTL_TCP,JSERVER,SMTP,TCP/IP,MAIL SERVER

[@more@]
需求：資料庫使用ORACLE8i，因我們系統的流程管理功能很差，客戶要求以郵件形式通知。
分析：基於平臺的二次開發自己想擴充套件功能實在太困難了，因此我們決定使用資料庫的郵件功能實現。
設計構思：
1、在流程的關鍵點生成通知任務，該任務以表記錄的方式存在於郵件傳送任務表中。
2、設定JOB，定時執行儲存過程，該過程檢索新的郵件傳送任務和以前傳送失敗的郵件任務並將其傳送，傳送過程中記錄成功和失敗的郵件輸入log表中，並對郵件傳送任務表做相關操作（標記和刪除），以保證下次JOB執行時


最終實現：
客戶要求實時傳送，並對傳送失敗的郵件是否再傳送不敏感，所以在涉及關鍵任務的檢視觸發器中呼叫一個簡單的郵件傳送過程。

感想：
1、前期的需求調研不是很到位，加上中途接手，有一團亂麻的感覺。
2、覺得自己提出的方案比最終實行的更具實用性和可靠性，為了某種原因而採用不完善的方案總覺得心裡有點疙瘩
3人總是可以偷懶的,網上有大量的程式碼可以利用,用了連感謝他們都不知道用什麼方式表達出來


基礎:
傳送郵件的整個過程如下:
ORACLE->PL/SQL->UTL_SMTP->UTL_TCP->JSERVER->SMTP->TCP/IP->MAIL SERVER

根據以上的過程,可能涉及到方面有:
oracle資料庫問題
PL/SQL問題(UTL_SMTP,UTL_TCP)
JSERVER問題
網路問題
MAIL SERVER問題

以下從這幾個方面說明一下我遇到的問題

>>>>oracle資料庫問題
>>許可權問題
以前很少用郵件功能，開始以為只有SYS使用者才能發郵件，後來發現實際中role設定為connect，resource的使用者就可以，可能是DBA在我們開發時對一些包的許可權管理比較鬆懈吧，不管了，反正我們的許可權夠了。

>>>>PL/SQL問題(UTL_SMTP,UTL_TCP)

>>UTL_SMTP,UTL_TCP
還沒有碰到什麼問題，在網上看到有些人在這方面也遇到問題，不太清楚他們是這些包沒有裝還是在JAVA中遇到了問題。

>>Base64-encoding問題
直接從網上下的檔案，9i資料庫中已自帶了。

>>中文亂碼問題
發件人,接收人,主題,內容等的處理
-- Set "From" MIME header
write_raw_mime_header(conn, 'From', sender);
-- Set "To" MIME header
write_raw_mime_header(conn, 'To', recipients);
-- Set "Cc" MIME header
write_raw_mime_header(conn, 'Cc', cclist);
-- Set "Subject" MIME header
--write_mime_header(conn, 'Subject', subject);
write_raw_mime_header(conn, 'Subject', subject);
-- Set message
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(message));

>>附件問題

>>>>JSERVER問題
預設情況下JSERVER並沒有安裝,本來為圖方便直接使用更改資料庫將JSERVER新增進去,結果java共享池設定過小,在那個128m記憶體的機器上足足花了幾個小時,想想慘呀!

>>>>網路問題
在測試伺服器上折騰了半天,發現該伺服器使用的防病毒軟體居然將外出連線25埠給遮蔽了,那個惱火呀,後來想想也值得,至少遇到這樣的問題以後還可以想想防火牆方面的問題
解決方法是
telnet smtp.xxx.xxx 25
看看是否可以連線上,如果不能檢查網路及ORACLE伺服器的相關設定(如防火牆)。

>>>>MAIL SERVER問題
>>認證問題
username varchar2(256) := 'xxxxx';
password varchar2(256) := '*****';

--utl_smtp.command(conn, 'AUTH LOGIN');
--utl_smtp.command(conn, xx_encode.encode(utl_raw.cast_to_raw(username)));
--utl_smtp.command(conn, xx_encode.encode(utl_raw.cast_to_raw(password)));

>>命令支援問題
UTL_SMTP支援對MAIL SERVER 傳送指定的命令,但如果MAIL SERVER如果不支援該命令,則出現異常,使用前一定要測試好.



部分實現程式碼如下:
------------
DROP VIEW V_SYS_MAIL;
DROP VIEW V_SYSTEM_MAIL;
DROP TABLE sys_mail_log;
DROP TABLE sys_mail_task;
--DROP TABLE sys_email;

drop procedure xx_send_mail;
DROP PACKAGE xx_encode;
DROP PACKAGE xx_MAIL;
-------------

REM
REM bs64demo8i.sql - Demo PL/SQL program that encodes binary data using the
REM Base64-encoding scheme per the MIME standard (RFC 2045).
REM
REM @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
REM Note: The performance of this Base-64 encoding package is slow due to
REM excessive string parsing and byte manipulation in PL/SQL. A native
REM implemenation of this function has been provided in Oracle 9i.
REM @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

CREATE OR REPLACE PACKAGE xx_encode IS

-- Base64-encode a piece of binary data.
--
-- Note that this encode function does not split the encoded text into
-- multiple lines with no more than 76 bytes each as required by
-- the MIME standard.
--
FUNCTION encode(r IN RAW) RETURN VARCHAR2;

END;
/

CREATE OR REPLACE PACKAGE BODY xx_encode IS

TYPE vc2_table IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
map vc2_table;

-- Initialize the Base64 mapping
PROCEDURE init_map IS
BEGIN
map(0) :='A'; map(1) :='B'; map(2) :='C'; map(3) :='D'; map(4) :='E';
map(5) :='F'; map(6) :='G'; map(7) :='H'; map(8) :='I'; map(9):='J';
map(10):='K'; map(11):='L'; map(12):='M'; map(13):='N'; map(14):='O';
map(15):='P'; map(16):='Q'; map(17):='R'; map(18):='S'; map(19):='T';
map(20):='U'; map(21):='V'; map(22):='W'; map(23):='X'; map(24):='Y';
map(25):='Z'; map(26):='a'; map(27):='b'; map(28):='c'; map(29):='d';
map(30):='e'; map(31):='f'; map(32):='g'; map(33):='h'; map(34):='i';
map(35):='j'; map(36):='k'; map(37):='l'; map(38):='m'; map(39):='n';
map(40):='o'; map(41):='p'; map(42):='q'; map(43):='r'; map(44):='s';
map(45):='t'; map(46):='u'; map(47):='v'; map(48):='w'; map(49):='x';
map(50):='y'; map(51):='z'; map(52):='0'; map(53):='1'; map(54):='2';
map(55):='3'; map(56):='4'; map(57):='5'; map(58):='6'; map(59):='7';
map(60):='8'; map(61):='9'; map(62):='+'; map(63):='/';
END;

FUNCTION encode(r IN RAW) RETURN VARCHAR2 IS
i pls_integer;
x pls_integer;
y pls_integer;
v VARCHAR2(32767);
BEGIN

-- For every 3 bytes, split them into 4 6-bit units and map them to
-- the Base64 characters
i := 1;
WHILE ( i + 2 <= utl_raw.length(r) ) LOOP
x := to_number(utl_raw.substr(r, i, 1), '0X') * 65536 +
to_number(utl_raw.substr(r, i + 1, 1), '0X') * 256 +
to_number(utl_raw.substr(r, i + 2, 1), '0X');
y := floor(x / 262144); v := v || map(y); x := x - y * 262144;
y := floor(x / 4096); v := v || map(y); x := x - y * 4096;
y := floor(x / 64); v := v || map(y); x := x - y * 64;
v := v || map(x);
i := i + 3;
END LOOP;

-- Process the remaining bytes that has fewer than 3 bytes.
IF ( utl_raw.length(r) - i = 0) THEN
x := to_number(utl_raw.substr(r, i, 1), '0X');
y := floor(x / 4); v := v || map(y); x := x - y * 4;
x := x * 16; v := v || map(x);
v := v || '==';
ELSIF ( utl_raw.length(r) - i = 1) THEN
x := to_number(utl_raw.substr(r, i, 1), '0X') * 256 +
to_number(utl_raw.substr(r, i + 1, 1), '0X');
y := floor(x / 1024); v := v || map(y); x := x - y * 1024;
y := floor(x / 16); v := v || map(y); x := x - y * 16;
x := x * 4; v := v || map(x);
v := v || '=';
END IF;

RETURN v;

END;

BEGIN
init_map;
END;
/

CREATE OR REPLACE PACKAGE xx_mail IS
----------------------- Customizable Section -----------------------
-- Customize the SMTP host, port and your domain name below.
smtp_host VARCHAR2(256) := 'xxx.xxx.com';
smtp_port PLS_INTEGER := 25;
smtp_domain VARCHAR2(256) := 'xxx.com';
username varchar2(256) := 'xxxxxx';
password varchar2(256) := '******';
-- Customize the signature that will appear in the email's MIME header.
-- Useful for versioning.
MAILER_ID CONSTANT VARCHAR2(256) := 'SYSTEM MAIL';
--------------------- End Customizable Section ---------------------
-- A unique string that demarcates boundaries of parts in a multi-part email
-- The string should not appear inside the body of any part of the email.
-- Customize this if needed or generate this randomly dynamically.
BOUNDARY CONSTANT VARCHAR2(256) := '3RT2SDDF5875CCC90D2974F756WER';
FIRST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || utl_tcp.CRLF;
LAST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || '--' ||
utl_tcp.CRLF;
-- A MIME type that denotes multi-part email (MIME) Messages.
MULTIPART_MIME_TYPE CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="'||
BOUNDARY || '"';
MAX_BASE64_LINE_WIDTH CONSTANT PLS_INTEGER := 76 / 4 * 3;
-- A simple email API for sending email in plain text in a single call.
-- The format of an email address is one of these:
-- someone@some-domain
-- "Someone at some domain"
-- Someone at some domain
-- The recipients is a list of email addresses separated by
-- either a "," or a ";"


PROCEDURE mail(sender IN VARCHAR2,
recipients IN VARCHAR2,
cclist IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2);
-- Extended email API to send email in HTML or plain text with no size limit.
-- First, begin the email by begin_mail(). Then, call write_text() repeatedly
-- to send email in ASCII piece-by-piece. Or, call write_mb_text() to send
-- email in non-ASCII or multi-byte character set. End the email with
-- end_mail().
FUNCTION begin_mail(sender IN VARCHAR2,
recipients IN VARCHAR2,
cclist IN VARCHAR2,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL)
RETURN utl_smtp.connection;
-- Write email body in ASCII
PROCEDURE write_text(conn IN OUT NOCOPY utl_smtp.connection,
message IN VARCHAR2);
-- Write email body in non-ASCII (including multi-byte). The email body
-- will be sent in the database character set.
PROCEDURE write_mb_text(conn IN OUT NOCOPY utl_smtp.connection,
message IN VARCHAR2);
-- Write email body in binary
PROCEDURE write_raw(conn IN OUT NOCOPY utl_smtp.connection,
message IN RAW);
-- APIs to send email with attachments. Attachments are sent by sending
-- emails in "multipart/mixed" MIME format. Specify that MIME format when
-- beginning an email with begin_mail().
-- Send a single text attachment.
PROCEDURE attach_text(conn IN OUT NOCOPY utl_smtp.connection,
data IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
last IN BOOLEAN DEFAULT FALSE);
-- Send a binary attachment. The attachment will be encoded in Base-64
-- encoding format.
PROCEDURE attach_base64(conn IN OUT NOCOPY utl_smtp.connection,
data IN RAW,
mime_type IN VARCHAR2 DEFAULT 'application/octet',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
last IN BOOLEAN DEFAULT FALSE);
-- Send an attachment with no size limit. First, begin the attachment
-- with begin_attachment(). Then, call write_text repeatedly to send
-- the attachment piece-by-piece. If the attachment is text-based but
-- in non-ASCII or multi-byte character set, use write_mb_text() instead.
-- To send binary attachment, the binary content should first be
-- encoded in Base-64 encoding format using the demo package for 8i,
-- or the native one in 9i. End the attachment with end_attachment.
PROCEDURE begin_attachment(conn IN OUT NOCOPY utl_smtp.connection,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
transfer_enc IN VARCHAR2 DEFAULT NULL);
-- End the attachment.
PROCEDURE end_attachment(conn IN OUT NOCOPY utl_smtp.connection,
last IN BOOLEAN DEFAULT FALSE);
-- End the email.
PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection);
-- Extended email API to send multiple emails in a session for better
-- performance. First, begin an email session with begin_session.
-- Then, begin each email with a session by calling begin_mail_in_session
-- instead of begin_mail. End the email with end_mail_in_session instead
-- of end_mail. End the email session by end_session.
FUNCTION begin_session RETURN utl_smtp.connection;
-- Begin an email in a session.
PROCEDURE begin_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection,
sender IN VARCHAR2,
recipients IN VARCHAR2,
cclist IN VARCHAR2,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL);
-- End an email in a session.
PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection);
-- End an email session.
PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection);
END;
/
CREATE OR REPLACE PACKAGE BODY xx_mail IS
-- Return the next email address in the list of email addresses, separated
-- by either a "," or a ";". The format of mailbox may be in one of these:
-- someone@some-domain
-- "Someone at some domain"
-- Someone at some domain
FUNCTION get_address(addr_list IN OUT VARCHAR2) RETURN VARCHAR2 IS
addr VARCHAR2(256);
i pls_integer;
FUNCTION lookup_unquoted_char(str IN VARCHAR2,
chrs IN VARCHAR2) RETURN pls_integer AS
c VARCHAR2(5);
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; -- Skip the quote character
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;
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;
-- Write a MIME header
PROCEDURE write_mime_header(conn IN OUT NOCOPY utl_smtp.connection,
name IN VARCHAR2,
value IN VARCHAR2) IS
BEGIN
utl_smtp.write_data(conn, name || ': ' || value || utl_tcp.CRLF);
END;
-- Write a RAW MIME header
PROCEDURE write_raw_mime_header(conn IN OUT NOCOPY utl_smtp.connection,
name IN VARCHAR2,
value IN VARCHAR2) IS
BEGIN
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(name || ': ' || value || utl_tcp.CRLF));
END;
-- Mark a message-part boundary. Set to TRUE for the last boundary.
PROCEDURE write_boundary(conn IN OUT NOCOPY utl_smtp.connection,
last IN BOOLEAN DEFAULT FALSE) AS
BEGIN
IF (last) THEN
utl_smtp.write_data(conn, LAST_BOUNDARY);
ELSE
utl_smtp.write_data(conn, FIRST_BOUNDARY);
END IF;
END;
------------------------------------------------------------------------
PROCEDURE mail(sender IN VARCHAR2,
recipients IN VARCHAR2,
cclist IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2) IS
conn utl_smtp.connection;
BEGIN
conn := begin_mail(sender, recipients,cclist, subject);
--write_text(conn, message);
write_mb_text(conn, message);
end_mail(conn);
END;
------------------------------------------------------------------------
FUNCTION begin_mail(sender IN VARCHAR2,
recipients IN VARCHAR2,
cclist IN VARCHAR2,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL)
RETURN utl_smtp.connection IS
conn utl_smtp.connection;
BEGIN
conn := begin_session;
begin_mail_in_session(conn, sender, recipients, cclist, subject, mime_type,
priority);
RETURN conn;
END;
------------------------------------------------------------------------
PROCEDURE write_text(conn IN OUT NOCOPY utl_smtp.connection,
message IN VARCHAR2) IS
BEGIN
utl_smtp.write_data(conn, message);
END;
------------------------------------------------------------------------
PROCEDURE write_mb_text(conn IN OUT NOCOPY utl_smtp.connection,
message IN VARCHAR2) IS
BEGIN
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(message));
END;
------------------------------------------------------------------------
PROCEDURE write_raw(conn IN OUT NOCOPY utl_smtp.connection,
message IN RAW) IS
BEGIN
utl_smtp.write_raw_data(conn, message);
END;
------------------------------------------------------------------------
PROCEDURE attach_text(conn IN OUT NOCOPY utl_smtp.connection,
data IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
last IN BOOLEAN DEFAULT FALSE) IS
BEGIN
begin_attachment(conn, mime_type, inline, filename);
write_text(conn, data);
end_attachment(conn, last);
END;
------------------------------------------------------------------------
PROCEDURE attach_base64(conn IN OUT NOCOPY utl_smtp.connection,
data IN RAW,
mime_type IN VARCHAR2 DEFAULT 'application/octet',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
last IN BOOLEAN DEFAULT FALSE) IS
i PLS_INTEGER;
len PLS_INTEGER;
BEGIN
begin_attachment(conn, mime_type, inline, filename, 'base64');
-- Split the Base64-encoded attachment into multiple lines
i := 1;
len := utl_raw.length(data);
WHILE (i < len) LOOP
IF (i + MAX_BASE64_LINE_WIDTH < len) THEN
utl_smtp.write_raw_data(conn,
xx_encode.encode(utl_raw.substr(data, i,
MAX_BASE64_LINE_WIDTH)));
ELSE
utl_smtp.write_raw_data(conn,
xx_encode.encode(utl_raw.substr(data, i)));
END IF;
utl_smtp.write_data(conn, utl_tcp.CRLF);
i := i + MAX_BASE64_LINE_WIDTH;
END LOOP;
end_attachment(conn, last);
END;
------------------------------------------------------------------------
PROCEDURE begin_attachment(conn IN OUT NOCOPY utl_smtp.connection,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
transfer_enc IN VARCHAR2 DEFAULT NULL) IS
BEGIN
write_boundary(conn);
write_mime_header(conn, 'Content-Type', mime_type);
IF (filename IS NOT NULL) THEN
IF (inline) THEN
write_mime_header(conn, 'Content-Disposition',
'inline; filename="'||filename||'"');
ELSE
write_mime_header(conn, 'Content-Disposition',
'attachment; filename="'||filename||'"');
END IF;
END IF;
IF (transfer_enc IS NOT NULL) THEN
write_mime_header(conn, 'Content-Transfer-Encoding', transfer_enc);
END IF;
utl_smtp.write_data(conn, utl_tcp.CRLF);
END;
------------------------------------------------------------------------
PROCEDURE end_attachment(conn IN OUT NOCOPY utl_smtp.connection,
last IN BOOLEAN DEFAULT FALSE) IS
BEGIN
utl_smtp.write_data(conn, utl_tcp.CRLF);
IF (last) THEN
write_boundary(conn, last);
END IF;
END;
------------------------------------------------------------------------
PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection) IS
BEGIN
end_mail_in_session(conn);
end_session(conn);
END;
------------------------------------------------------------------------
FUNCTION begin_session RETURN utl_smtp.connection IS
conn utl_smtp.connection;
BEGIN
-- open SMTP connection
conn := utl_smtp.open_connection(smtp_host, smtp_port);
utl_smtp.helo(conn, smtp_domain);
--utl_smtp.command(conn, 'AUTH LOGIN');
--utl_smtp.command(conn, xx_encode.encode(utl_raw.cast_to_raw(username)));
--utl_smtp.command(conn, xx_encode.encode(utl_raw.cast_to_raw(password)));
RETURN conn;
END;
------------------------------------------------------------------------
PROCEDURE begin_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection,
sender IN VARCHAR2,
recipients IN VARCHAR2,
cclist IN VARCHAR2,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain; charset="GBK"',
priority IN PLS_INTEGER DEFAULT NULL) IS
my_recipients VARCHAR2(32767) := recipients;
my_cclist VARCHAR2(32767) := cclist;
my_sender VARCHAR2(32767) := sender;
BEGIN
-- Specify sender's address (our server allows bogus address
-- as long as it is a full email address (xxx@yyy.com).
utl_smtp.mail(conn, get_address(my_sender));
-- Specify recipient(s) of the email.
WHILE (my_recipients IS NOT NULL) LOOP
utl_smtp.rcpt(conn, get_address(my_recipients));
END LOOP;
-- Specify cc of the email.
WHILE (my_cclist IS NOT NULL) LOOP
utl_smtp.rcpt(conn, get_address(my_cclist));
END LOOP;

-- Start body of email
utl_smtp.open_data(conn);
-- Set "From" MIME header
write_raw_mime_header(conn, 'From', sender);
-- Set "To" MIME header
write_raw_mime_header(conn, 'To', recipients);
-- Set "Cc" MIME header
write_raw_mime_header(conn, 'Cc', cclist);
-- Set "Subject" MIME header
--write_mime_header(conn, 'Subject', subject);
write_raw_mime_header(conn, 'Subject', subject);
-- Set "Content-Type" MIME header
write_mime_header(conn, 'Content-Type', mime_type);
-- Set "X-Mailer" MIME header
write_mime_header(conn, 'X-Mailer', MAILER_ID);
-- Set priority:
-- High Normal Low
-- 1 2 3 4 5
IF (priority IS NOT NULL) THEN
write_mime_header(conn, 'X-Priority', priority);
END IF;
-- Send an empty line to denotes end of MIME headers and
-- beginning of message body.
utl_smtp.write_data(conn, utl_tcp.CRLF);
IF (mime_type LIKE 'multipart/mixed%') THEN
write_text(conn, 'This is a multi-part message in MIME format.' ||
utl_tcp.crlf);
END IF;
END;
------------------------------------------------------------------------
PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection) IS
BEGIN
utl_smtp.close_data(conn);
END;
------------------------------------------------------------------------
PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection) IS
BEGIN
utl_smtp.quit(conn);
END;
END;
/

CREATE OR REPLACE PROCEDURE xx_SEND_MAIL
(SENDER IN VARCHAR2,
RECIPIENTS IN VARCHAR2,
CCLIST IN VARCHAR2,
SUBJECT IN VARCHAR2,
MESSAGE IN VARCHAR2) IS

SSENDER VARCHAR2(512);
SRECIPIENTS VARCHAR2(512);
SCCLIST VARCHAR2(512);
SSUBJECT VARCHAR2(512);
SMESSAGE VARCHAR2(512);

BEGIN --
IF SENDER IS NULL THEN
RETURN;
ELSE
SSENDER :=SENDER;
END IF;

IF RECIPIENTS IS NULL THEN
RETURN;
ELSE
SRECIPIENTS :=RECIPIENTS;
END IF;

SCCLIST :=CCLIST;

IF SUBJECT IS NULL THEN
SSUBJECT:='無主題';
ELSE
SSUBJECT :=SUBJECT;
END IF;

IF MESSAGE IS NULL THEN
SMESSAGE:='無內容';
ELSE
SMESSAGE :=MESSAGE;
END IF;

xx_MAIL.MAIL(SSENDER,SRECIPIENTS,SCCLIST,SSUBJECT,SMESSAGE);

INSERT INTO SYS_MAIL_LOG (SENDER,RECIPIENTS,CCLIST,SUBJECT,MESSAGE,SEND_TIME,RESULT)
VALUES
(SSENDER,SRECIPIENTS,SCCLIST,SSUBJECT,SMESSAGE,SYSDATE,'成功');

EXCEPTION
WHEN OTHERS THEN
INSERT INTO SYS_MAIL_LOG (SENDER,RECIPIENTS,CCLIST,SUBJECT,MESSAGE,SEND_TIME,RESULT)
VALUES
(SSENDER,SRECIPIENTS,SCCLIST,SSUBJECT,SMESSAGE,SYSDATE,'失敗');
END;
/


CREATE OR REPLACE TRIGGER TIG_SYS_MAIL
INSTEAD OF INSERT ON V_SYS_MAIL
FOR EACH ROW
DECLARE
SENDER VARCHAR2(50);
RECIPIENTS VARCHAR2(512);
CCLIST VARCHAR2(512);
SUBJECT VARCHAR2(128);
MESSAGE VARCHAR2(512);
SMAIL_CC VARCHAR2(512);
SMAIL_CC_TO VARCHAR2(512);
NCOUNT NUMBER;
nLENGTH NUMBER;
nBEGIN_IDX NUMBER;
nEND_IDX NUMBER;
nADDRCNT NUMBER;
BEGIN

INSERT INTO SYS_MAIL_TASK
(FROM_DEPT, FROM_SENDER, TO_DEPT, SUBJECT, TO_RECI, CCLIST, MESSAGE, RECORDED_TIME, ATTACH, DEPARTMENT, RECORDED_BY, COMMENTS)
VALUES
(:NEW.FROM_DEPT, :NEW.FROM_SENDER, :NEW.TO_DEPT, :NEW.SUBJECT, :NEW.TO_RECI, :NEW.CCLIST, :NEW.MESSAGE, SYSDATE, :NEW.ATTACH, :NEW.DEPARTMENT, :NEW.RECORDED_BY, :NEW.COMMENTS);

--查詢發件人E_MAIL
SELECT COUNT(E_MAIL) INTO NCOUNT FROM V_SYSTEM_MAIL WHERE USERNAME = :NEW.FROM_SENDER;
IF NCOUNT = 1 THEN
SELECT :NEW.FROM_SENDER||'' INTO SENDER FROM V_SYSTEM_MAIL WHERE USERNAME = :NEW.FROM_SENDER;
ELSE
SENDER := 'NOBODY@MIDEA.COM.CN';
END IF;

--查詢收件人E_MAIL
RECIPIENTS := '';
IF :NEW.TO_RECI IS NOT NULL THEN
nLENGTH := LENGTH(:NEW.TO_RECI);
nBEGIN_IDX := 0;
nEND_IDX := 0 ;
nADDRCNT := 0;
LOOP
nBEGIN_IDX :=nBEGIN_IDX + 1;
nEND_IDX := INSTR(:NEW.TO_RECI, ';' , nBEGIN_IDX);
IF nEND_IDX > 0 THEN
sMAIL_CC := SUBSTR(:NEW.TO_RECI, nBEGIN_IDX , nEND_IDX - nBEGIN_IDX );
ELSE
sMAIL_CC := SUBSTR(:NEW.TO_RECI, nBEGIN_IDX , nLENGTH - nBEGIN_IDX + 1);
END IF;

SELECT COUNT(E_MAIL) INTO NCOUNT FROM V_SYSTEM_MAIL WHERE USERNAME = sMAIL_CC;
IF NCOUNT = 1 THEN
IF nADDRCNT>0 THEN
RECIPIENTS := RECIPIENTS || ';';
END IF;
SELECT E_MAIL INTO SMAIL_CC_TO FROM V_SYSTEM_MAIL WHERE USERNAME = sMAIL_CC;
IF RECIPIENTS IS NOT NULL THEN
RECIPIENTS := RECIPIENTS || sMAIL_CC || '';
ELSE
RECIPIENTS := sMAIL_CC || '';
END IF;
END IF;
nADDRCNT := nADDRCNT + 1;
nBEGIN_IDX := nEND_IDX;

EXIT WHEN nEND_IDX = 0;
END LOOP;
END IF;



--查詢抄送人E_MAIL
CCLIST := '';
IF :NEW.CCLIST IS NOT NULL THEN
nLENGTH := LENGTH(:NEW.CCLIST);
nBEGIN_IDX := 0;
nEND_IDX := 0 ;
nADDRCNT := 0;
LOOP
nBEGIN_IDX :=nBEGIN_IDX + 1;
nEND_IDX := INSTR(:NEW.CCLIST, ';' , nBEGIN_IDX);
IF nEND_IDX > 0 THEN
sMAIL_CC := SUBSTR(:NEW.CCLIST, nBEGIN_IDX , nEND_IDX - nBEGIN_IDX );
ELSE
sMAIL_CC := SUBSTR(:NEW.CCLIST, nBEGIN_IDX , nLENGTH - nBEGIN_IDX + 1);
END IF;

SELECT COUNT(E_MAIL) INTO NCOUNT FROM V_SYSTEM_MAIL WHERE USERNAME = sMAIL_CC;
IF NCOUNT = 1 THEN
IF nADDRCNT>0 THEN
CCLIST := CCLIST || ';';
END IF;
SELECT E_MAIL INTO SMAIL_CC_TO FROM V_SYSTEM_MAIL WHERE USERNAME = sMAIL_CC;
IF CCLIST IS NOT NULL THEN
CCLIST := CCLIST || sMAIL_CC || '';
ELSE
CCLIST := sMAIL_CC || '';
END IF;
END IF;
nADDRCNT := nADDRCNT + 1;
nBEGIN_IDX := nEND_IDX;

EXIT WHEN nEND_IDX = 0;

END LOOP;
END IF;


--其他變數
SUBJECT := :NEW.SUBJECT;
MESSAGE := :NEW.MESSAGE;

xx_SEND_MAIL(SENDER,RECIPIENTS,CCLIST,SUBJECT,MESSAGE);
END;
/