--把附件分成多份，這樣可以發送超過32K的附件
L_FILEPOS := 1; --重置offset，在發送多個附件時，必須重置
L_FIL := BFILENAME (DT_NAME, L_FILENAME);
L_FILE_LEN := DBMS_LOB.GETLENGTH (L_FIL);
L_MODULO := MOD (L_FILE_LEN, L_AMT);
L_PIECES := TRUNC (L_FILE_LEN / L_AMT);
IF (L_MODULO <> 0)
THEN
L_PIECES := L_PIECES + 1;
END IF;
DBMS_LOB.FILEOPEN (L_FIL, DBMS_LOB.FILE_READONLY);
DBMS_LOB.READ (L_FIL,
L_AMT,
L_FILEPOS,
L_BUF);
L_DATA := NULL;
FOR I IN 1 .. L_PIECES
LOOP
L_FILEPOS := I * L_AMT + 1;
L_FILE_LEN := L_FILE_LEN - L_AMT;
L_DATA := UTL_RAW.CONCAT (L_DATA, L_BUF);
L_CHUNKS := TRUNC (UTL_RAW.LENGTH (L_DATA) / L_MAX_LINE_WIDTH);
IF (I <> L_PIECES)
THEN
L_CHUNKS := L_CHUNKS - 1;
END IF;
UTL_SMTP.WRITE_RAW_DATA (CONN,
UTL_ENCODE.BASE64_ENCODE (L_DATA));
L_DATA := NULL;
IF (L_FILE_LEN < L_AMT AND L_FILE_LEN > 0)
THEN
L_AMT := L_FILE_LEN;
END IF;
DBMS_LOB.READ (L_FIL,
L_AMT,
L_FILEPOS,
L_BUF);
END LOOP;
DBMS_LOB.FILECLOSE (L_FIL);
END_BOUNDARY (CONN);
EXCEPTION
WHEN OTHERS
THEN
DBMS_LOB.FILECLOSE (L_FIL);
END_BOUNDARY (CONN);
RAISE;
END; --結束處理二進制附件
END IF; --結束處理附件內容
DROP_DIRECTORY (DT_NAME);
END; --結束過程ATTACHMENT
---------------------------------------------真正發送郵件的過程--------------------------------------------
PROCEDURE P_EMAIL (P_SENDORADDRESS2 VARCHAR2, --發送地址
P_RECEIVERADDRESS2 VARCHAR2) --接受地址
IS
L_CONN UTL_SMTP.CONNECTION; --定義連接
BEGIN
/*初始化郵件服務器信息，連接郵件服務器*/
L_CONN := UTL_SMTP.OPEN_CONNECTION (P_SERVER, P_PORT);
UTL_SMTP.HELO (L_CONN, P_SERVER);
/* smtp服務器登錄校驗 */
IF P_NEED_SMTP = 1
THEN
UTL_SMTP.COMMAND (L_CONN, 'AUTH LOGIN', '');
UTL_SMTP.COMMAND (
L_CONN,
UTL_RAW.CAST_TO_VARCHAR2 (
UTL_ENCODE.BASE64_ENCODE (UTL_RAW.CAST_TO_RAW (P_USER))
)
);
UTL_SMTP.COMMAND (
L_CONN,
UTL_RAW.CAST_TO_VARCHAR2 (
UTL_ENCODE.BASE64_ENCODE (UTL_RAW.CAST_TO_RAW (P_PASS))
)
);
END IF;
/*設置發送地址和接收地址*/
UTL_SMTP.MAIL (L_CONN, P_SENDORADDRESS2);
UTL_SMTP.RCPT (L_CONN, P_RECEIVERADDRESS2);
/*設置郵件頭*/
UTL_SMTP.OPEN_DATA (L_CONN);
WRITE_DATA (L_CONN, 'Date', TO_CHAR (SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
/*設置發送人*/
WRITE_DATA (L_CONN, 'From', P_SENDOR);
/*設置接收人*/
WRITE_DATA (L_CONN, 'To', P_RECEIVER);
/*設置郵件主題*/
WRITE_DATA (L_CONN, 'Subject', P_SUB);
WRITE_DATA (L_CONN, 'Content-Type', MULTIPART_MIME_TYPE);
UTL_SMTP.WRITE_DATA (L_CONN, UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA (L_CONN, FIRST_BOUNDARY);
WRITE_DATA (L_CONN, 'Content-Type', 'text/plain;charset=gb2312');
--單獨空一行，否則，正文內容不顯示
UTL_SMTP.WRITE_DATA (L_CONN, UTL_TCP.CRLF);
/* 設置郵件正文
把分隔符還原成chr(10)。這主要是為了shell中調用該過程，如果有多行，則先把多行的內容合並成一行，並用 l_splite分隔
然後用 l_crlf替換chr(10)。這一步是必須的，否則將不能發送郵件正文有多行的郵件
*/
WRITE_DATA (
L_CONN,
'',
REPLACE (REPLACE (P_TXT, L_SPLITE, CHR (10)), CHR (10), L_CRLF),
'',
''
);
END_BOUNDARY (L_CONN);
--如果文件名稱不為空，則發送附件
IF (P_FILENAME IS NOT NULL)
THEN
--根據逗號或者分號拆分附件地址
P_SPLITE_STR (P_FILENAME, 2);
--循環發送附件(在同一個郵件中)
FOR K IN 1 .. MY_ACCT_LIST.COUNT
LOOP
ATTACHMENT (
CONN => L_CONN,
FILENAME => MY_ACCT_LIST (K),
TRANSFER_ENC => P_ENCODE,
DT_NAME => L_DIRECTORY_BASE_NAME || TO_CHAR (K)
);
END LOOP;
END IF;
/*關閉數據寫入*/
UTL_SMTP.CLOSE_DATA (L_CONN);
/*關閉連接*/
UTL_SMTP.QUIT (L_CONN);
/*異常處理*/
EXCEPTION
WHEN OTHERS
THEN
NULL;
RAISE;
END;
---------------------------------------------------主過程-----------------------------------------------------
BEGIN
L_SENDORADDRESS := '<' || P_SENDOR || '>';
P_SPLITE_STR (P_RECEIVER); --處理郵件地址
FOR K IN 1 .. MY_ADDRESS_LIST.COUNT
LOOP
P_EMAIL (L_SENDORADDRESS, MY_ADDRESS_LIST (K));
END LOOP;
/*處理郵件地址，根據逗號分割郵件*/
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;
