--- semua ini tinggal copas doang koq, tapi harus tersedia koneksi ke mail server
--- semoga bermanfaat
CREATE OR REPLACE PACKAGE BODY MAIL_ATTACHMENT AS
/******************************************************************************
NAME: MAIL_ATTACHMENT
PURPOSE:
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 1/21/2010 1. Created this package.
******************************************************************************/
g_crlf CHAR (2) DEFAULT CHR (13) || CHR (10);
g_mail_conn UTL_SMTP.connection;
g_mailhost VARCHAR2 (255) := '10.10.10.10';
FUNCTION address_email (p_string IN VARCHAR2, p_recipients IN ARRAY)
RETURN VARCHAR2
IS
l_recipients LONG;
BEGIN
FOR i IN 1 .. p_recipients.COUNT
LOOP
UTL_SMTP.rcpt (g_mail_conn, p_recipients (i));
IF (l_recipients IS NULL)
THEN
l_recipients := p_string || p_recipients (i);
ELSE
l_recipients := l_recipients || ', ' || p_recipients (i);
END IF;
END LOOP;
RETURN l_recipients;
END;
PROCEDURE SEND_MAIL_ATTACHMENT (
p_sender_email IN VARCHAR2,
p_from IN VARCHAR2,
p_to IN ARRAY DEFAULT ARRAY (),
p_cc IN ARRAY DEFAULT ARRAY (),
p_bcc IN ARRAY DEFAULT ARRAY (),
p_subject IN VARCHAR2,
p_filename IN VARCHAR2,
p_body IN LONG,
p_content IN LONG
)
IS
l_to_list LONG;
l_cc_list LONG;
l_bcc_list LONG;
l_date VARCHAR2 (255) DEFAULT TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss');
BEGIN
g_mail_conn := UTL_SMTP.open_connection (g_mailhost, 25);
UTL_SMTP.helo (g_mail_conn, g_mailhost);
UTL_SMTP.mail (g_mail_conn, p_sender_email);
l_to_list := address_email ('To: ', p_to);
l_cc_list := address_email ('Cc: ', p_cc);
l_bcc_list := address_email ('Bcc: ', p_bcc);
UTL_SMTP.data (g_mail_conn,
'Date: ' || l_date || g_crlf ||
'From: ' || NVL (p_from, p_sender_email) || g_crlf ||
'Subject: '|| NVL (p_subject, '(no subject)') || g_crlf ||
'To: ' || l_to_list || g_crlf ||
'Cc: ' || l_cc_list || g_crlf ||
'Bcc: ' || l_bcc_list || g_crlf ||
'MIME-Version: 1.0' || g_crlf || -- Use MIME mail standard
'Content-Type: multipart/mixed;' || g_crlf ||
' boundary="-----SECBOUND"' || g_crlf ||g_crlf ||
'-------SECBOUND' || g_crlf ||
'Content-Type: text/plain;' || g_crlf ||
'Content-Transfer_Encoding: 7bit' || g_crlf || g_crlf ||
p_body || g_crlf || g_crlf ||
'-------SECBOUND' || g_crlf ||
'Content-Type: text/plain;' || g_crlf ||
' name="excel.txt"' || g_crlf ||
'Content-Transfer_Encoding: 8bit' || g_crlf ||
'Content-Disposition: attachment;'|| g_crlf ||
' filename="'||p_filename||'.txt"' || g_crlf || g_crlf ||
p_content || g_crlf || g_crlf || -- Content of attachment
'-------SECBOUND--' -- End MIME mail
);
UTL_SMTP.quit (g_mail_conn);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;
PROCEDURE SEND_MAIL_ (
p_sender_email IN VARCHAR2,
p_from IN VARCHAR2,
p_to IN ARRAY DEFAULT ARRAY (),
p_cc IN ARRAY DEFAULT ARRAY (),
p_bcc IN ARRAY DEFAULT ARRAY (),
p_subject IN VARCHAR2,
p_body IN LONG
)
IS
l_to_list LONG;
l_cc_list LONG;
l_bcc_list LONG;
l_date VARCHAR2 (255) DEFAULT TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss');
PROCEDURE writedata (p_text IN VARCHAR2)
AS
BEGIN
IF (p_text IS NOT NULL)
THEN
UTL_SMTP.write_data (g_mail_conn, p_text || g_crlf);
END IF;
END;
BEGIN
g_mail_conn := UTL_SMTP.open_connection (g_mailhost, 25);
UTL_SMTP.helo (g_mail_conn, g_mailhost);
UTL_SMTP.mail (g_mail_conn, p_sender_email);
l_to_list := address_email ('To: ', p_to);
l_cc_list := address_email ('Cc: ', p_cc);
l_bcc_list := address_email ('Bcc: ', p_bcc);
UTL_SMTP.open_data (g_mail_conn);
writedata ('Date: ' || l_date);
writedata ('From: ' || NVL (p_from, p_sender_email));
writedata ('Subject: ' || NVL (p_subject, '(no subject)'));
writedata (l_to_list);
writedata (l_cc_list);
UTL_SMTP.write_data (g_mail_conn, '' || g_crlf);
UTL_SMTP.write_data (g_mail_conn, p_body);
UTL_SMTP.close_data (g_mail_conn);
UTL_SMTP.quit (g_mail_conn);
END;
PROCEDURE SEND_MAIL_HTML (
p_sender_email IN VARCHAR2,
p_from IN VARCHAR2,
p_to IN ARRAY DEFAULT ARRAY (),
p_cc IN ARRAY DEFAULT ARRAY (),
p_bcc IN ARRAY DEFAULT ARRAY (),
p_subject IN VARCHAR2,
p_body IN LONG DEFAULT NULL
)
IS
l_boundary VARCHAR2 (255) DEFAULT 'a1b2c3d4e3f2g1';
l_body_html CLOB := EMPTY_CLOB; --This LOB will be the email message
l_offset NUMBER;
l_ammount NUMBER;
l_temp VARCHAR2 (32767) DEFAULT NULL;
l_to_list LONG;
l_cc_list LONG;
l_bcc_list LONG;
l_date VARCHAR2 (255) DEFAULT TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss');
BEGIN
g_mail_conn := UTL_SMTP.open_connection (g_mailhost, 25);
UTL_SMTP.helo (g_mail_conn, g_mailhost);
UTL_SMTP.mail (g_mail_conn, p_sender_email);
l_to_list := address_email ('To: ', p_to);
l_cc_list := address_email ('Cc: ', p_cc);
l_bcc_list := address_email ('Bcc: ', p_bcc);
l_temp := l_temp || 'MIME-Version: 1.0' || g_crlf;
l_temp := l_temp || 'Date:' ||l_date|| g_crlf;
l_temp := l_temp || 'From: ' || NVL (p_from, p_sender_email) || g_crlf;
l_temp := l_temp || 'Subject: ' || p_subject || g_crlf;
l_temp := l_temp || 'To: ' || l_to_list || g_crlf;
l_temp := l_temp || 'Cc: ' || l_cc_list || g_crlf;
l_temp := l_temp || 'Bcc: ' || l_bcc_list || g_crlf;
l_temp :=
l_temp
|| 'Content-Type: multipart/alternative; boundary='
|| CHR (34)
|| l_boundary
|| CHR (34)
|| g_crlf;
-- Write the headers
DBMS_LOB.createtemporary (l_body_html, FALSE, 10);
DBMS_LOB.WRITE (l_body_html, LENGTH (l_temp), 1, l_temp);
-- Write the text boundary
l_offset := DBMS_LOB.getlength (l_body_html) + 1;
l_temp := '--' || l_boundary || g_crlf;
l_temp :=
l_temp || 'content-type: text/plain; charset=us-ascii' || g_crlf
|| g_crlf;
DBMS_LOB.WRITE (l_body_html, LENGTH (l_temp), l_offset, l_temp);
-- Write the plain text portion of the email
l_offset := DBMS_LOB.getlength (l_body_html) + 1;
DBMS_LOB.WRITE (l_body_html, LENGTH (p_body), l_offset, p_body);
-- Write the HTML boundary
l_temp := g_crlf || g_crlf || '--' || l_boundary || g_crlf;
l_temp := l_temp || 'content-type: text/html;' || g_crlf || g_crlf;
l_offset := DBMS_LOB.getlength (l_body_html) + 1;
DBMS_LOB.WRITE (l_body_html, LENGTH (l_temp), l_offset, l_temp);
-- Write the HTML portion of the message
l_offset := DBMS_LOB.getlength (l_body_html) + 1;
DBMS_LOB.WRITE (l_body_html, LENGTH (p_body), l_offset, p_body);
-- Write the final html boundary
l_temp := g_crlf || '--' || l_boundary || '--' || CHR (13);
l_offset := DBMS_LOB.getlength (l_body_html) + 1;
DBMS_LOB.WRITE (l_body_html, LENGTH (l_temp), l_offset, l_temp);
-- Send the email in 1900 byte chunks to UTL_SMTP
l_offset := 1;
l_ammount := 1900;
UTL_SMTP.open_data (g_mail_conn);
WHILE l_offset < DBMS_LOB.getlength (l_body_html)
LOOP
UTL_SMTP.write_data (g_mail_conn,
DBMS_LOB.SUBSTR (l_body_html, l_ammount, l_offset)
);
l_offset := l_offset + l_ammount;
l_ammount := LEAST (1900, DBMS_LOB.getlength (l_body_html) - l_ammount);
END LOOP;
UTL_SMTP.close_data (g_mail_conn);
UTL_SMTP.quit (g_mail_conn);
DBMS_LOB.freetemporary (l_body_html);
END;
END MAIL_ATTACHMENT;
/
--- Cara Menggunakannya
--- Sebagai contoh ajah yang menggunakan attachment, disini juga disertai tanpa attachment, dan body HTML
--- untuk multi file attach belum bisa, bis lum dapet contekannyah
exec MAIL_ATTACHMENT.SEND_MAIL_ATTACHMENT('Pengirim', --SENDER
'pengirim@mail.com', --FROM
MAIL_ATTACHMENT.ARRAY ('aku@mail.com', 'saya@mail.com'), --TO
MAIL_ATTACHMENT.ARRAY ('dia@mail.com'), --CC
MAIL_ATTACHMENT.ARRAY (NULL), --BCC
'Tes From Vasm', --SUBJECT
'Filename' , --Nama File Attachment
'Body Email', --Body Email
'Isi dari file yang akan di attach' --Isi dari file yang akan di attach
);
--- selamat mencoba ---
