Pada contoh posting sebelumnya masih sederhana dimana kita belum bisa mengirim email dengan attachment, html. untuk contoh selanjutnya kita akan memperluas procedure kirim email melalui procedure
berikut dengan sedikit modifikasi pada bagian :
- deklarasi spesifik ke gmail :
v_smtp_domain varchar2 (256) := 'gmail.com';
menambahkan
utl_smtp.ehlo(v_mail_conn, v_smtp_domain);
menon aktikan
-- utl_smtp.helo(v_mail_conn, p_smtp_host);
Maka sekarang kita bisa gunakan untuk kebutuhan gmail dengan stunnel :
CREATE OR REPLACE PROCEDURE custom_send_mail (
p_to IN VARCHAR2,
p_cc IN VARCHAR2 DEFAULT NULL,
p_bcc IN VARCHAR2 DEFAULT NULL,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
p_text_msg IN VARCHAR2 DEFAULT NULL,
p_html_msg IN CLOB DEFAULT NULL,
p_attachments IN table_attachments DEFAULT NULL,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25,
p_username IN VARCHAR2 DEFAULT NULL,
p_password IN VARCHAR2 DEFAULT NULL)
AS
v_mail_conn UTL_SMTP.connection;
v_boundary VARCHAR2(50) := '----=*#abc1234321cba#*=';
v_step PLS_INTEGER := 24573;
v_length PLS_INTEGER;
v_begin PLS_INTEGER := 1;
v_buffer_size INTEGER := 75;
v_raw RAW(32767);
v_smtp_domain varchar2 (256) := 'gmail.com';
BEGIN
/* **** Author: Josep Coves
**** Version: 1.0
**** Date: 08/06/2012
**** josepcoves@relational.es
**** http://www.relational.es
**** Based on Tim's procedure, Thanks Tim!
**** http://www.oracle-base.com/articles/misc/email-from-oracle-plsql.php#html
*/
v_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
utl_smtp.ehlo(v_mail_conn, v_smtp_domain);
utl_smtp.command( v_mail_conn, 'AUTH LOGIN');
--Establish SMTP connection
IF (p_username IS NOT NULL) THEN
utl_smtp.command( v_mail_conn, UTL_RAW.cast_to_varchar2( UTL_ENCODE.base64_encode( UTL_RAW.cast_to_raw( p_username ))) );
utl_smtp.command( v_mail_conn, UTL_RAW.cast_to_varchar2( UTL_ENCODE.base64_encode( UTL_RAW.cast_to_raw( p_password ))) );
END IF;
-- utl_smtp.helo(v_mail_conn, p_smtp_host);
utl_smtp.mail(v_mail_conn, p_from);
utl_smtp.rcpt(v_mail_conn, p_to);
utl_smtp.open_data(v_mail_conn);
utl_smtp.write_data(v_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
utl_smtp.write_data(v_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
utl_smtp.write_data(v_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
IF (p_cc IS NOT NULL) THEN
utl_smtp.write_data(v_mail_conn, 'CC: ' || p_cc || UTL_TCP.crlf);
END IF;
IF (p_bcc IS NOT NULL) THEN
utl_smtp.write_data(v_mail_conn, 'BCC: ' || p_bcc || UTL_TCP.crlf);
END IF;
utl_smtp.write_data(v_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
utl_smtp.write_data(v_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
utl_smtp.write_data(v_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
utl_smtp.write_data(v_mail_conn, 'Content-Type: multipart/mixed; boundary="' || v_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
--Plain Text Body
IF p_text_msg IS NOT NULL THEN
utl_smtp.write_data(v_mail_conn, '--' || v_boundary || UTL_TCP.crlf);
utl_smtp.write_data(v_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
utl_smtp.write_data(v_mail_conn, p_text_msg);
utl_smtp.write_data(v_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
ELSIF p_html_msg IS NOT NULL THEN
-- HTML Body
utl_smtp.write_data(v_mail_conn, '--' || v_boundary || UTL_TCP.crlf);
utl_smtp.write_data(v_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_html_msg) - 1 )/v_step) LOOP
utl_smtp.write_data(v_mail_conn, DBMS_LOB.SUBSTR(p_html_msg, v_step, i * v_step + 1));
END LOOP;
utl_smtp.write_data(v_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;
IF p_attachments IS NOT NULL THEN
FOR i IN 1..p_attachments.COUNT
LOOP
utl_smtp.write_data(v_mail_conn, '--' || v_boundary || UTL_TCP.crlf);
utl_smtp.write_data(v_mail_conn, 'Content-Type: ' ||p_attachments(i).attachment_mime || UTL_TCP.crlf);
utl_smtp.write_data(v_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
utl_smtp.write_data(v_mail_conn, 'Content-Disposition: attachment; filename="' || p_attachments(i).attachment_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
v_length := DBMS_LOB.getlength(p_attachments(i).attachment_blob);
v_begin := 1;
v_raw := NULL;
v_buffer_size := 75;
WHILE v_begin < v_length LOOP
DBMS_LOB.read( p_attachments(i).attachment_blob, v_buffer_size, v_begin, v_raw );
utl_smtp.write_raw_data( v_mail_conn, UTL_ENCODE.base64_encode(v_raw) );
utl_smtp.write_data( v_mail_conn, UTL_TCP.crlf );
v_begin := v_begin + v_buffer_size;
END LOOP ;
utl_smtp.write_data(v_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END LOOP;
END IF;
utl_smtp.write_data(v_mail_conn, '--' || v_boundary || '--' || UTL_TCP.crlf);
utl_smtp.close_data(v_mail_conn);
utl_smtp.quit(v_mail_conn);
END;
/
Selanjutnya kita bisa pakai untuk mengirim, text bisa, html dan attachement. berikut contoh pemakaian untuk mengirim gambar product dari tabel DEMO_PRODUCT_INFO.
DECLARE
v_clob clob := 'Test email';
v_blob blob;
t_a table_attachments := NULL;
v_a mail_attachment ;
BEGIN
SELECT mail_attachment(t.FILENAME,t.MIMETYPE,t.PRODUCT_IMAGE)
BULK COLLECT INTO t_a
FROM DEMO_PRODUCT_INFO t;
custom_send_mail (p_to => '<your_recipient@mail.com>',
p_from => '<yourgmailname@gmail.com>',
p_subject => 'test',
p_html_msg => v_clob,
p_attachments => t_a,
p_smtp_host => 'localhost',
p_smtp_port => 1925,
p_username => 'yourgmailname@gmail.com',
p_password => 'yourgmailpassword');
END;
/
Maka pada email penerima akan muncul:
Order #' || o.order_id || '
