try use utl_smtp send through gmail by plsql
前陣子公司的信箱從Outlook換成Gmail，在發信時基本上大同小異，但是信件內容有嵌HTML的話需要額外增加一段語法
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" "http://www.w3.org/TR/REC-html40/loose.dtd">
/******************************
Author:Jimbo Chang
******************************/
DECLARE
TYPE G_SPLIT_ARRAY IS TABLE OF VARCHAR2 (32767);
V_SMTP_HOST VARCHAR2 (30) := 'smtp-relay.gmail.com';
V_SMTP_PORT NUMBER := '25';
V_US_CONN UTL_SMTP.CONNECTION;
V_CRLF VARCHAR2 (2) := CHR (13) || CHR (10);
V_MESSAGE VARCHAR2 (3000);
V_HTML_TEXT VARCHAR2 (32767);
V_FROM VARCHAR2 (3000);
V_TO VARCHAR2 (3000);
V_SUBJECT VARCHAR2 (3000);
V_NLS_LANG VARCHAR2 (30) := 'ZHT16BIG5';
V_DATA1 VARCHAR2 (32767);
V_DATA2 VARCHAR2 (32767);
V_DATA3 VARCHAR2 (32767);
V_DATA4 VARCHAR2 (32767);
V_DATA5 VARCHAR2 (32767);
FUNCTION SPLIT_TEXT (P_TEXT IN CLOB, P_DELIMETER IN VARCHAR2 DEFAULT ',')
RETURN G_SPLIT_ARRAY IS
-- ----------------------------------------------------------------------------
V_ARRAY G_SPLIT_ARRAY := G_SPLIT_ARRAY ();
V_TEXT CLOB := P_TEXT;
V_IDX NUMBER;
BEGIN
V_ARRAY.DELETE;
IF V_TEXT IS NULL THEN
RAISE_APPLICATION_ERROR (-20000, 'P_TEXT 不可為空值');
END IF;
WHILE V_TEXT IS NOT NULL LOOP
V_IDX := INSTR (V_TEXT, P_DELIMETER);
V_ARRAY.EXTEND;
IF V_IDX > 0 THEN
V_ARRAY (V_ARRAY.LAST) := SUBSTR (V_TEXT, 1, V_IDX - 1);
V_TEXT := SUBSTR (V_TEXT, V_IDX + 1);
ELSE
V_ARRAY (L_ARRAY.LAST) := V_TEXT;
V_TEXT := NULL;
END IF;
END LOOP;
RETURN V_ARRAY;
END SPLIT_TEXT;
PROCEDURE SET_RCPT (P_US_CONN IN OUT UTL_SMTP.CONNECTION, P_LIST IN VARCHAR2) AS
L_TAB G_SPLIT_ARRAY;
BEGIN
IF TRIM (P_LIST) IS NOT NULL THEN
L_TAB := SPLIT_TEXT (P_LIST);
FOR I IN 1 .. L_TAB.COUNT LOOP
UTL_SMTP.RCPT (P_US_CONN, TRIM (L_TAB (I)));
END LOOP;
END IF;
END SET_RCPT;
BEGIN
V_HTML_TEXT := '<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" "http://www.w3.org/TR/REC-html40/loose.dtd">';
V_HTML_TEXT := V_HTML_TEXT || ' ';
V_HTML_TEXT := V_HTML_TEXT || ' <html> ';
V_HTML_TEXT := V_HTML_TEXT || ' <body> ';
V_HTML_TEXT := V_HTML_TEXT || ' <p>Dear All，</p>';
V_HTML_TEXT := V_HTML_TEXT || ' </body> ';
V_HTML_TEXT := V_HTML_TEXT || ' </html> ';
V_FROM := 'OracleNotification@XXX.com.tw';
V_TO := 'Destination@XXX.com.tw';
V_SUBJECT := UTL_RAW.CAST_TO_RAW (CONVERT ('Subject:' || 'Oracle發信通知測試' || V_CRLF, V_NLS_LANG));
V_DATA1 := 'To: ' || V_TO || V_CRLF;
V_DATA2 := 'From: ' || V_FROM || V_CRLF;
V_DATA3 := 'MIME-Version: 1.0' || V_CRLF;
V_DATA4 := 'Content-Type: text/html; charset=utf-8/big5' || V_CRLF;
V_DATA5 := 'Content-Transfer-Encoding: 8bit' || V_CRLF;
V_MESSAGE := UTL_RAW.CAST_TO_RAW (CONVERT (V_HTML_TEXT, V_NLS_LANG));
V_US_CONN := UTL_SMTP.OPEN_CONNECTION (V_SMTP_HOST);
UTL_SMTP.EHLO (V_US_CONN, V_SMTP_HOST);
UTL_SMTP.MAIL (V_US_CONN, V_FROM);
-- UTL_SMTP.RCPT (P_US_CONN, V_TO);
--multiple receipter
SET_RCPT (V_US_CONN, V_TO);
UTL_SMTP.OPEN_DATA (V_US_CONN);
UTL_SMTP.WRITE_RAW_DATA (V_US_CONN, V_SUBJECT);
UTL_SMTP.WRITE_DATA (V_US_CONN, V_DATA1);
UTL_SMTP.WRITE_DATA (V_US_CONN, V_DATA2);
UTL_SMTP.WRITE_DATA (V_US_CONN, V_DATA3);
UTL_SMTP.WRITE_DATA (V_US_CONN, V_DATA4);
UTL_SMTP.WRITE_DATA (V_US_CONN, V_DATA5);
UTL_SMTP.WRITE_RAW_DATA (V_US_CONN, V_MESSAGE);
UTL_SMTP.CLOSE_DATA (V_US_CONN);
UTL_SMTP.QUIT (V_US_CONN);
END;