去年因為 Alert 的問題, 上 Google 找到了 昭佑.天翔 的 Blog, 這位高手有客製了一個名為 "Graphic Alert", 當下真是驚為天人, 在天翔好心的指點之下, 我知道 UTL_SMTP 的存在
最近因為有些 Alert 因為資料量太大的問題 (超過 Alert 限制的 64K) (註一), 造成 Alert 無法正常寄送, 所以才認真的研究 UTL_SMTP 的實作. 剛好, 在 Oracle Metalink 上有一篇文章, 就是教我們怎麼使用 UTL_SMTP (Metalink DocID : 357385.1), 也附上完整範例檔, 基本上, 如果 DB Server 有開啟 SMTP 服務, 修正一下 Package 的設定, 應該就可以直接使用了. 不過, 可能會遇到的問題有 :
1. 中文字變亂碼
2. 內容太長, VARCHAR2 變數仍有長度問題
3. Attachment 的方式太過僵化
註一: Alert 64K 的限制在 Oracle Applications Technology 11i.ATG_PF.H.delta.7 (RUP 7) 將會更新, 不過, 上 RUP7 是一個不小的挑戰就是了
附上範例檔 :
/**********************Start of package spec for the E-mail program*******************/
/* demomail.sql */
CREATE OR REPLACE PACKAGE DEMO_MAIL IS
/* Package variable Declaration. */
SMTP_HOST VARCHAR2 (256) := 'Your SMTP Server Name or IP Address';
SMTP_PORT PLS_INTEGER := 25;
SMTP_DOMAIN VARCHAR2 (256) := 'Put your domain here';
MAILER_ID CONSTANT VARCHAR2 (256) := 'Mailer by Oracle UTL_SMTP';
/* Boundary is an arbitrary string used as seperator marker for different
sections of the of the e-mail. Separates various parts of e-mail and attachments. */
BOUNDARY CONSTANT VARCHAR2 (256) := '__7D81B75CCC90D2974F7A1CBD__';
FIRST_BOUNDARY CONSTANT VARCHAR2 (256) := '--' || BOUNDARY || UTL_TCP.CRLF;
LAST_BOUNDARY CONSTANT VARCHAR2 (256) := '--' || BOUNDARY || '--' || UTL_TCP.CRLF;
MULTIPART_MIME_TYPE CONSTANT VARCHAR2 (256) := 'multipart/mixed; boundary="' || BOUNDARY || '"';
MAX_BASE64_LINE_WIDTH CONSTANT PLS_INTEGER := 76 / 4 * 3; -- do not change this line.
/* Table declaration for storing e-mail attachment file names. */
TYPE ATTACHMENT IS RECORD (FILENAME VARCHAR2 (100));
TYPE TAB_OF_ATTACHMENTS IS TABLE OF ATTACHMENT;
/* Package Function and procedures. */
PROCEDURE MAIL (SENDER IN VARCHAR2
, RECIPIENTS IN VARCHAR2
, CC IN VARCHAR2 DEFAULT NULL
, BCC IN VARCHAR2 DEFAULT NULL
, SUBJECT IN VARCHAR2
, MESSAGE IN VARCHAR2
);
FUNCTION BEGIN_MAIL (SENDER IN VARCHAR2
, RECIPIENTS IN VARCHAR2
, CC IN VARCHAR2 DEFAULT NULL
, BCC IN VARCHAR2 DEFAULT NULL
, SUBJECT IN VARCHAR2
, MIME_TYPE IN VARCHAR2 DEFAULT 'text/plain'
, PRIORITY IN PLS_INTEGER DEFAULT NULL
)
RETURN UTL_SMTP.CONNECTION;
PROCEDURE WRITE_TEXT (CONN IN OUT NOCOPY UTL_SMTP.CONNECTION, MESSAGE IN VARCHAR2);
PROCEDURE WRITE_MB_TEXT (CONN IN OUT NOCOPY UTL_SMTP.CONNECTION, MESSAGE IN VARCHAR2);
PROCEDURE WRITE_RAW (CONN IN OUT NOCOPY UTL_SMTP.CONNECTION, MESSAGE IN RAW);
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
);
PROCEDURE ATTACH_MB_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
);
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
);
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
);
PROCEDURE END_ATTACHMENT (CONN IN OUT NOCOPY UTL_SMTP.CONNECTION, LAST IN BOOLEAN DEFAULT FALSE );
PROCEDURE END_MAIL (CONN IN OUT NOCOPY UTL_SMTP.CONNECTION);
FUNCTION BEGIN_SESSION
RETURN UTL_SMTP.CONNECTION;
PROCEDURE BEGIN_MAIL_IN_SESSION (CONN IN OUT NOCOPY UTL_SMTP.CONNECTION
, SENDER IN VARCHAR2
, RECIPIENTS IN VARCHAR2
, CC IN VARCHAR2 DEFAULT NULL
, BCC IN VARCHAR2 DEFAULT NULL
, SUBJECT IN VARCHAR2
, MIME_TYPE IN VARCHAR2 DEFAULT 'text/plain'
, PRIORITY IN PLS_INTEGER DEFAULT NULL
);
PROCEDURE END_MAIL_IN_SESSION (CONN IN OUT NOCOPY UTL_SMTP.CONNECTION);
PROCEDURE END_SESSION (CONN IN OUT NOCOPY UTL_SMTP.CONNECTION);
PROCEDURE SEND_EMAIL (P_DIRECTORY IN VARCHAR2
, P_SENDER IN VARCHAR2
, P_RECIPIENT IN VARCHAR2
, P_CC IN VARCHAR2
, P_BCC IN VARCHAR2
, P_SUBJECT IN VARCHAR2
, P_BODY IN VARCHAR2
, P_ATTACHMENT1 IN VARCHAR2 DEFAULT NULL
, P_ATTACHMENT2 IN VARCHAR2 DEFAULT NULL
, P_ATTACHMENT3 IN VARCHAR2 DEFAULT NULL
, P_ATTACHMENT4 IN VARCHAR2 DEFAULT NULL
, P_ERROR OUT VARCHAR2
);
END DEMO_MAIL;
/
show errors
/* End of demo_mail_spec.sql */
/*-------------------------------------------------------------------------
----- Package Body: demo_mail
----- File: demo_mail_body.sql
----- Main Routine: send_email(P_SENDER IN VARCHAR2,
---------- P_RECIPIENT IN VARCHAR2,
---------- P_CC IN VARCHAR2,
---------- P_BCC IN VARCHAR2,
---------- P_SUBJECT IN VARCHAR2,
---------- P_BODY IN VARCHAR2,
---------- P_ATTACHMENT1 IN VARCHAR2 default null,
---------- P_ATTACHMENT2 IN VARCHAR2 default null,
---------- P_ATTACHMENT3 IN VARCHAR2 default null,
---------- P_ATTACHMENT4 IN VARCHAR2 default null,
---------- P_ERROR OUT VARCHAR2)
----------
----- Description: This package implements calls to ult_smtp and utl_encode
----- packages. Tje send_mail procedure is the entry point to send an e-mail
----- with upto 4 attachment filenames. The package requires creation of a
----- directory object BFILE_DIR pointing to the absolute patch were the
----- attachment files are stored.
-----
----- SQL: create or replace directory BFILE_DIR as '/home/attachments';
-----
----- Script Filename: setup.sql
-----
----------------------------------------------------------------------------*/
CREATE OR REPLACE PACKAGE BODY DEMO_MAIL IS
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;
END IF;
IF (C = '"') THEN
INSIDE_QUOTE := TRUE;
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;
PROCEDURE WRITE_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;
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
, CC IN VARCHAR2 DEFAULT NULL
, BCC IN VARCHAR2 DEFAULT NULL
, SUBJECT IN VARCHAR2
, MESSAGE IN VARCHAR2
) IS
CONN UTL_SMTP.CONNECTION;
BEGIN
CONN :=
BEGIN_MAIL (SENDER
, RECIPIENTS
, CC
, BCC
, SUBJECT
);
WRITE_TEXT (CONN, MESSAGE);
END_MAIL (CONN);
END;
FUNCTION BEGIN_MAIL (SENDER IN VARCHAR2
, RECIPIENTS IN VARCHAR2
, CC IN VARCHAR2 DEFAULT NULL
, BCC IN VARCHAR2 DEFAULT NULL
, 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
, CC
, BCC
, 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_MB_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
);
UTL_SMTP.WRITE_RAW_DATA (CONN, UTL_RAW.CAST_TO_RAW (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'
);
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, UTL_ENCODE.BASE64_ENCODE (UTL_RAW.SUBSTR (DATA, I, MAX_BASE64_LINE_WIDTH)));
ELSE
UTL_SMTP.WRITE_RAW_DATA (CONN, UTL_ENCODE.BASE64_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
CONN := UTL_SMTP.OPEN_CONNECTION (SMTP_HOST, SMTP_PORT);
UTL_SMTP.HELO (CONN, SMTP_DOMAIN);
RETURN CONN;
END;
PROCEDURE BEGIN_MAIL_IN_SESSION (CONN IN OUT NOCOPY UTL_SMTP.CONNECTION
, SENDER IN VARCHAR2
, RECIPIENTS IN VARCHAR2
, CC IN VARCHAR2 DEFAULT NULL
, BCC IN VARCHAR2 DEFAULT NULL
, SUBJECT IN VARCHAR2
, MIME_TYPE IN VARCHAR2 DEFAULT 'text/plain'
, PRIORITY IN PLS_INTEGER DEFAULT NULL
) IS
MY_RECIPIENTS VARCHAR2 (32767) := RECIPIENTS;
MY_SENDER VARCHAR2 (32767) := SENDER;
MY_CC VARCHAR2 (32767) := CC;
MY_BCC VARCHAR2 (32767) := BCC;
BEGIN
UTL_SMTP.MAIL (CONN, GET_ADDRESS (MY_SENDER));
WHILE (MY_RECIPIENTS IS NOT NULL) LOOP
UTL_SMTP.RCPT (CONN, GET_ADDRESS (MY_RECIPIENTS));
END LOOP;
WHILE (MY_CC IS NOT NULL) LOOP
UTL_SMTP.RCPT (CONN, GET_ADDRESS (MY_CC));
END LOOP;
WHILE (MY_BCC IS NOT NULL) LOOP
UTL_SMTP.RCPT (CONN, GET_ADDRESS (MY_BCC));
END LOOP;
UTL_SMTP.OPEN_DATA (CONN);
WRITE_MIME_HEADER (CONN, 'From', SENDER);
WRITE_MIME_HEADER (CONN, 'To', RECIPIENTS);
WRITE_MIME_HEADER (CONN, 'CC', CC);
WRITE_MIME_HEADER (CONN, 'BCC', BCC);
WRITE_MIME_HEADER (CONN, 'Subject', SUBJECT);
WRITE_MIME_HEADER (CONN, 'Content-Type', MIME_TYPE);
WRITE_MIME_HEADER (CONN, 'X-Mailer', MAILER_ID);
IF (PRIORITY IS NOT NULL) THEN
WRITE_MIME_HEADER (CONN, 'X-Priority', PRIORITY);
END IF;
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;
--------------------------------------------------------------------------------------------------------
------------------------------------------- Main email procedure ---------------------------------------
--------------------------------------------------------------------------------------------------------
PROCEDURE SEND_EMAIL (P_DIRECTORY IN VARCHAR2
, P_SENDER IN VARCHAR2
, P_RECIPIENT IN VARCHAR2
, P_CC IN VARCHAR2
, P_BCC IN VARCHAR2
, P_SUBJECT IN VARCHAR2
, P_BODY IN VARCHAR2
, P_ATTACHMENT1 IN VARCHAR2 DEFAULT NULL
, P_ATTACHMENT2 IN VARCHAR2 DEFAULT NULL
, P_ATTACHMENT3 IN VARCHAR2 DEFAULT NULL
, P_ATTACHMENT4 IN VARCHAR2 DEFAULT NULL
, P_ERROR OUT VARCHAR2
) IS
FIL BFILE;
FILE_LEN PLS_INTEGER;
MAX_LINE_WIDTH PLS_INTEGER := 54;
BUF RAW (2100);
AMT BINARY_INTEGER := 672 * 3; /* ensures proper format; 2016 */
POS PLS_INTEGER := 1; /* pointer for each piece */
FILEPOS PLS_INTEGER := 1; /* pointer for the file */
T_FILE1 VARCHAR2 (100) := P_ATTACHMENT1; /* binary file attachment */
T_FILE2 VARCHAR2 (100) := P_ATTACHMENT2; /* binary file attachment */
T_FILE3 VARCHAR2 (100) := P_ATTACHMENT3; /* binary file attachment */
T_FILE4 VARCHAR2 (100) := P_ATTACHMENT4; /* binary file attachment */
V_FILE_NAME VARCHAR2 (100) := NULL; /* ascii file attachment */
V_FILE_HANDLE UTL_FILE.FILE_TYPE;
V_DIRECTORY_NAME VARCHAR2 (100) := P_DIRECTORY;
V_LINE VARCHAR2 (2000);
V_RLINE RAW (1000);
CONN UTL_SMTP.CONNECTION;
MESG VARCHAR2 (32767);
MESG_LEN NUMBER;
CRLF VARCHAR2 (2) := CHR (13) || CHR (10);
DATA RAW (2100);
CHUNKS PLS_INTEGER;
LEN PLS_INTEGER := 1;
MODULO PLS_INTEGER;
PIECES PLS_INTEGER;
ERR_NUM NUMBER;
ERR_MSG VARCHAR2 (100);
--v_mime_type_bin varchar2(30) := 'application/pdf';
--v_mime_type_bin varchar2(30) := 'application/doc';
--v_mime_type_bin varchar2(30) := 'application/jpg';
/* Use this mime type when multiple attachments of different types are sent. */
V_MIME_TYPE_BIN VARCHAR2 (30) := 'application/octet-stream';
-- working storage
FILES TAB_OF_ATTACHMENTS;
T_FILE_COUNT NUMBER := 0;
BEGIN
P_ERROR := '0';
-- put the attachments into the pl/sql table
IF T_FILE1 IS NOT NULL THEN
FILES := TAB_OF_ATTACHMENTS (NULL);
FILES (1).FILENAME := T_FILE1;
T_FILE_COUNT := 1;
IF T_FILE2 IS NOT NULL THEN
FILES.EXTEND (1);
FILES (2).FILENAME := T_FILE2;
T_FILE_COUNT := 2;
END IF;
IF T_FILE3 IS NOT NULL THEN
FILES.EXTEND (1);
FILES (3).FILENAME := T_FILE3;
T_FILE_COUNT := 3;
END IF;
IF T_FILE4 IS NOT NULL THEN
FILES.EXTEND (1);
FILES (4).FILENAME := T_FILE4;
T_FILE_COUNT := 4;
END IF;
ELSE
T_FILE_COUNT := 0;
END IF;
BEGIN
CONN :=
DEMO_MAIL.BEGIN_MAIL (SENDER => P_SENDER
, RECIPIENTS => P_RECIPIENT
, CC => P_CC
, BCC => P_BCC
, SUBJECT => P_SUBJECT
, MIME_TYPE => DEMO_MAIL.MULTIPART_MIME_TYPE
);
END BEGIN_MAIL;
BEGIN
DEMO_MAIL.ATTACH_TEXT (CONN => CONN, DATA => P_BODY || CRLF || CRLF, MIME_TYPE => 'text/html');
END ATTACH_TEXT;
BEGIN
-- check to see if there are any attachments (otherwise the loop will error!)
IF T_FILE_COUNT > 0 THEN
-- loop through attachments
FOR I IN 1 .. FILES.COUNT LOOP
DEMO_MAIL.BEGIN_ATTACHMENT (CONN => CONN
, MIME_TYPE => V_MIME_TYPE_BIN
, INLINE => TRUE
, FILENAME => FILES (I).FILENAME
, TRANSFER_ENC => 'base64'
);
BEGIN
FILEPOS := 1; /* Insures we are pointing to beginning of file. */
AMT := 672 * 3; /* Insures amount is re-initialize for each file */
FIL := BFILENAME (V_DIRECTORY_NAME, FILES (I).FILENAME);
FILE_LEN := DBMS_LOB.GETLENGTH (FIL);
MODULO := MOD (FILE_LEN, AMT);
PIECES := TRUNC (FILE_LEN / AMT);
IF (MODULO <> 0) THEN
PIECES := PIECES + 1;
END IF;
DBMS_LOB.FILEOPEN (FIL, DBMS_LOB.FILE_READONLY);
DBMS_LOB.READ (FIL
, AMT
, FILEPOS
, BUF
);
DATA := NULL;
FOR I IN 1 .. PIECES LOOP
FILEPOS := I * AMT + 1;
FILE_LEN := FILE_LEN - AMT;
DATA := UTL_RAW.CONCAT (DATA, BUF);
CHUNKS := TRUNC (UTL_RAW.LENGTH (DATA) / MAX_LINE_WIDTH);
IF (I <> PIECES) THEN
CHUNKS := CHUNKS - 1;
END IF;
DEMO_MAIL.WRITE_RAW (CONN => CONN, MESSAGE => UTL_ENCODE.BASE64_ENCODE (DATA));
DATA := NULL;
IF (FILE_LEN < AMT
AND FILE_LEN > 0) THEN
AMT := FILE_LEN;
END IF;
/* Insures we only read again if there is more data. */
/*Commented and changed on 18-Apr-2008*/
--if (file_len > amt) then
--dbms_lob.read(fil, amt, filepos, buf);
--end if;
---Changed code----------
IF (FILE_LEN = 0) THEN
NULL;
ELSE
DBMS_LOB.READ (FIL
, AMT
, FILEPOS
, BUF
);
END IF;
-----------------------------
END LOOP;
END;
DBMS_LOB.FILECLOSE (FIL);
DEMO_MAIL.END_ATTACHMENT (CONN => CONN);
END LOOP;
END IF;
END BEGIN_ATTACHMENT;
DEMO_MAIL.END_MAIL (CONN => CONN);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DEMO_MAIL.END_ATTACHMENT (CONN => CONN);
DBMS_LOB.FILECLOSE (FIL);
P_ERROR := 'Error: No data found.';
WHEN OTHERS THEN
DEMO_MAIL.END_ATTACHMENT (CONN => CONN);
ERR_NUM := SQLCODE;
ERR_MSG := SUBSTR (SQLERRM, 1, 100);
P_ERROR := 'Error: ' || ERR_NUM || ' - ' || ERR_MSG;
DBMS_OUTPUT.PUT_LINE ('Error number is ' || ERR_NUM);
DBMS_OUTPUT.PUT_LINE ('Error message is ' || ERR_MSG);
DBMS_LOB.FILECLOSE (FIL);
END;
END DEMO_MAIL;
/
/******************End of package body for the E-mail program*****************/
/*-- A simple way of sending upto 4 attachments --*/
---simple.sql---
DECLARE
PO_ERR_MSG VARCHAR2 (1000);
BEGIN
DEMO_MAIL.SEND_EMAIL (P_DIRECTORY => 'BFILE_DIR' --replace with your directory object
, P_SENDER => 'sender@somedomain.com'
, P_RECIPIENT => 'receiver@somedomain.com'
, P_CC => 'first.last@somedomain.com'
, P_BCC => NULL
, P_SUBJECT => 'Subject of the e-mail '
, P_BODY => 'Please find the following n attachments.'
, P_ATTACHMENT1 => 'blob.jpg' --replace with your attachment filename or null.
, P_ATTACHMENT2 => 'test.doc' --replace with your attachment filename or null
, P_ATTACHMENT3 => 'text.txt' --replace with your attachment filename or null
, P_ATTACHMENT4 => 'mypdf.pdf' --replace with your attachment filename or null
, P_ERROR => PO_ERR_MSG -- should be 0.
);
DBMS_OUTPUT.PUT_LINE ('THIS IS THE ERROR: ' || PO_ERR_MSG);
END;
/
/*---End of simple.sql------*/
留言列表
Web (1)
{{ article.title }}
