Mengirim HTML email melalui PL/SQL and Gmail (2)

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: