Haryanto

Membuat procedure mail dengan BLOB di oracle

Hampir setiap orang sekarang memiliki email sehingga lebih flexsible setiap informasi dikirimkan by email secara otomatis. Dioracle ada fungsi BLOB untuk digunakan dalam menyimpan data file. Berikut kami berikan beberapa langkah untuk membuat mail otomatis di oracle dengan BLOB baik dengan header dan footer message serta html message. File attactment juga bisa lebih dari 1 file :

Yuk berikut langkah2 pembuatannya :

1. Buat package dengan nama : string_api dengan script berikut :

-- START HEADER PACKAGE --

create or replace PACKAGE string_api AS
-- Public types
TYPE t_split_array IS TABLE OF VARCHAR2(4000);

FUNCTION split_text (p_text       IN CLOB,
                     p_delimeter IN VARCHAR2 DEFAULT ',')
RETURN t_split_array;

PROCEDURE print_clob (p_clob IN CLOB);
PROCEDURE print_clob_old (p_clob IN CLOB);

PROCEDURE print_clob_htp (p_clob IN CLOB);
PROCEDURE print_clob_htp_old (p_clob IN CLOB);

END string_api;

-- START HEADER PACKAGE --

2. buat body package string_api dengan script berikut :

-- START BODY PACKAGE --

create or replace PACKAGE BODY string_api AS
-- --------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
FUNCTION split_text (p_text       IN CLOB,
                     p_delimeter IN VARCHAR2 DEFAULT ',')
RETURN t_split_array IS
-- ----------------------------------------------------------------------------
l_array t_split_array   := t_split_array();
l_text   CLOB := p_text;
l_idx    NUMBER;
BEGIN
l_array.delete;

IF l_text IS NULL THEN
    RAISE_APPLICATION_ERROR(-20000, 'P_TEXT parameter cannot be NULL');
END IF;

WHILE l_text IS NOT NULL LOOP
    l_idx := INSTR(l_text, p_delimeter);
    l_array.extend;
    IF l_idx > 0 THEN
      l_array(l_array.last) := SUBSTR(l_text, 1, l_idx - 1);
      l_text := SUBSTR(l_text, l_idx + 1);
    ELSE
      l_array(l_array.last) := l_text;
      l_text := NULL;
    END IF;
END LOOP;
RETURN l_array;
END split_text;
-- ----------------------------------------------------------------------------

-- ----------------------------------------------------------------------------
PROCEDURE print_clob (p_clob IN CLOB) IS
-- ----------------------------------------------------------------------------
l_offset NUMBER := 1;
l_chunk NUMBER := 255;
BEGIN
LOOP
    EXIT WHEN l_offset > LENGTH(p_clob);
    DBMS_OUTPUT.put_line(SUBSTR(p_clob, l_chunk, l_offset));
    l_offset := l_offset + l_chunk;
END LOOP;
END print_clob;
-- ----------------------------------------------------------------------------

-- ----------------------------------------------------------------------------
PROCEDURE print_clob_old (p_clob IN CLOB) IS
-- ----------------------------------------------------------------------------
l_offset NUMBER := 1;
l_chunk NUMBER := 255;
BEGIN
LOOP
    EXIT WHEN l_offset > DBMS_LOB.getlength(p_clob);
    DBMS_OUTPUT.put_line(DBMS_LOB.substr(p_clob, l_chunk, l_offset));
    l_offset := l_offset + l_chunk;
END LOOP;
END print_clob_old;
-- ----------------------------------------------------------------------------
----------------------------------------------------------------------------
PROCEDURE print_clob_htp (p_clob IN CLOB) IS
-- ----------------------------------------------------------------------------
l_offset NUMBER := 1;
l_chunk NUMBER := 32767;
BEGIN
LOOP
    EXIT WHEN l_offset > LENGTH(p_clob);
    HTP.prn(SUBSTR(p_clob, l_chunk, l_offset));
    l_offset := l_offset + l_chunk;
END LOOP;
END print_clob_htp;
-- ----------------------------------------------------------------------------

-- ----------------------------------------------------------------------------
PROCEDURE print_clob_htp_old (p_clob IN CLOB) IS
-- ----------------------------------------------------------------------------
l_offset NUMBER := 1;
l_chunk NUMBER := 32767;
BEGIN
LOOP
    EXIT WHEN l_offset > DBMS_LOB.getlength(p_clob);
    HTP.prn(DBMS_LOB.substr(p_clob, l_chunk, l_offset));
    l_offset := l_offset + l_chunk;
END LOOP;
END print_clob_htp_old;
-- ----------------------------------------------------------------------------

END string_api;

-- END BODY PACKAGE --

3. buat table untuk menyimpan data file ( di sini nama table PHP_UPLOAD )

CREATE TABLE PHP_UPLOAD
   (ID_FILE NUMBER NOT NULL ENABLE,
    NAMA_FILE VARCHAR2(150 BYTE),
    SIZE_FILE NUMBER,
    TYPE_FILE VARCHAR2(100 BYTE),
    CONTENT_FILE BLOB,
    USERNAME VARCHAR2(30 BYTE),
    KETERANGAN VARCHAR2(50 BYTE),
    TGL_UPLOAD DATE
   )

4. buat procedure untuk mail BLOBnya

-- START PROCEDURE mail blob --

create or replace procedure mail_BLOB_FILE(
xid_file number,
p_from varchar2,
p_to varchar2,
p_subject varchar2,
p_message varchar2,
p_message_footer varchar2,
p_html_msg varchar2) as

l_asset php_upload_sj%rowtype;
l_blob blob := to_blob('1');
l_conn utl_smtp.connection;
l_raw raw(57);
l_len integer := 0;
l_idx integer := 1;
l_buff_size integer := 57;
l_boundary varchar2(32) := sys_guid();
l_attachment_name long;

type array is table of varchar2(255);

l_tab string_api.t_split_array;

PROCEDURE process_recipients(p_mail_conn IN OUT UTL_SMTP.connection,
                               p_list      IN     VARCHAR2)
AS
    l_tab string_api.t_split_array;
BEGIN
    IF TRIM(p_list) IS NOT NULL THEN
      l_tab := string_api.split_text(p_list);
      FOR i IN 1 .. l_tab.COUNT LOOP
        UTL_SMTP.rcpt(p_mail_conn, TRIM(l_tab(i)));
      END LOOP;
    END IF;
END;

--p_to varchar2(400);

begin

-- Connect
l_conn := utl_smtp.open_connection( '10.234.22.10'); -- IP ADDRES WEB MAILNYA
utl_smtp.helo( l_conn, '10.234.22.10' ); -- IP ADDRES WEB MAILNYA
utl_smtp.mail( l_conn, p_from );
--utl_smtp.rcpt( l_conn, p_to );

process_recipients(l_conn, p_to);

utl_smtp.open_data(l_conn);

-- Header
utl_smtp.write_data( l_conn, 'From: ' || p_from || utl_tcp.crlf );
utl_smtp.write_data( l_conn, 'To: ' || p_to || utl_tcp.crlf );
utl_smtp.write_data( l_conn, 'Subject: ' || p_subject ||
                               utl_tcp.crlf );
utl_smtp.write_data( l_conn, 'MIME-Version: 1.0' || utl_tcp.crlf );
utl_smtp.write_data( l_conn, 'Content-Type: multipart/mixed; ' ||
                               utl_tcp.crlf );
utl_smtp.write_data( l_conn, ' boundary= "' || l_boundary || '"' ||
                               utl_tcp.crlf );
utl_smtp.write_data( l_conn, utl_tcp.crlf );

-- Body Header
utl_smtp.write_data( l_conn, '--' || l_boundary || utl_tcp.crlf );
utl_smtp.write_data( l_conn, 'Content-Type: text/plain;' ||
                               utl_tcp.crlf );
utl_smtp.write_data( l_conn, ' charset=US-ASCII' || utl_tcp.crlf );
utl_smtp.write_data( l_conn, utl_tcp.crlf );
utl_smtp.write_data( l_conn, p_message || utl_tcp.crlf );
utl_smtp.write_data( l_conn, utl_tcp.crlf );

IF p_html_msg IS NOT NULL THEN
    UTL_SMTP.write_data(l_conn, '--' || l_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_conn, p_html_msg);
    UTL_SMTP.write_data(l_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;

-- FOTTER
utl_smtp.write_data( l_conn, '--' || l_boundary || utl_tcp.crlf );
utl_smtp.write_data( l_conn, 'Content-Type: text/plain;' ||
                               utl_tcp.crlf );
utl_smtp.write_data( l_conn, ' charset=US-ASCII' || utl_tcp.crlf );
utl_smtp.write_data( l_conn, utl_tcp.crlf );
utl_smtp.write_data( l_conn, chr(13) || p_message_footer || utl_tcp.crlf );
utl_smtp.write_data( l_conn, utl_tcp.crlf );

-- table tempat penyimpanan file

    for c in ( select *
               from PHP_UPLOAD
              where id_file=xid_file) loop      -- bisa di pakai hardcore lebih dari 1 file

    -- Compress if a content and not already zipped.
    --if ( c.type_file != 'application/zip') then

if ( c.size_file > 500000) then
      utl_compress.lz_compress( src => c.content_file,
                                dst => l_blob );
      l_attachment_name := c.nama_file || '.gz';
    else
      l_blob := c.content_file;
      l_attachment_name := c.nama_file;
    end if;

    -- Attachment
    utl_smtp.write_data( l_conn, '--' || l_boundary || utl_tcp.crlf );
    utl_smtp.write_data( l_conn, 'Content-Type: application/octet-stream'
                                 || utl_tcp.crlf );
    utl_smtp.write_data( l_conn, 'Content-Disposition: attachment; ' ||
                                 utl_tcp.crlf );
    utl_smtp.write_data( l_conn, ' filename="' || l_attachment_name || '"'
                                 || utl_tcp.crlf );
    utl_smtp.write_data( l_conn, 'Content-Transfer-Encoding: base64' ||
                                 utl_tcp.crlf );
    utl_smtp.write_data( l_conn, utl_tcp.crlf );

    -- Loop through the blob
    -- chuck it up into 57-byte pieces
    -- and base64 encode it and write it into the mail buffer

--l_conn utl_smtp.connection;
l_raw :=null;
l_len := 0;
l_idx:= 1;
l_buff_size:= 57;
--l_boundary := sys_guid();

    l_len := dbms_lob.getlength(l_blob);
    while l_idx < l_len loop
      dbms_lob.read( l_blob, l_buff_size, l_idx, l_raw );
      utl_smtp.write_raw_data( l_conn, utl_encode.base64_encode(l_raw) );
      utl_smtp.write_data( l_conn, utl_tcp.crlf );
      l_idx := l_idx + l_buff_size;
    end loop;
    utl_smtp.write_data( l_conn, utl_tcp.crlf );

end loop;

-- Close Email
utl_smtp.write_data( l_conn, '--' || l_boundary || '--' ||
                                         utl_tcp.crlf );
utl_smtp.write_data( l_conn, utl_tcp.crlf || '.' || utl_tcp.crlf );
utl_smtp.close_data( l_conn );
utl_smtp.quit( l_conn );

exception
-- smtp errors, close connection and reraise
when utl_smtp.transient_error or
       utl_smtp.permanent_error then
    utl_smtp.quit( l_conn );
    raise;

end mail_BLOB_FILE;

-- END PROCEDURE mail blob --

4. execute procedure mail_BLOB_FILE

begin
mail_BLOB_FILE(
1020,
'satdc_sidoarjo@sdj.sat.co.id', -- from
'haryanto.hartati@sat.co.id', -- to bisa lebih dari 1 email, pisahkan pakai tanda ","
'p_subject', -- subject dalam text
'p_message', -- message header dalam text
'p_message_footer', -- message footer dalam text
'<tr><td>TEST HTML</td></tr>'); -- message body dalam format HTML
end;

---------------------------------- TERIMA KASIH -------------------------------

Haryanto

Senin, 23 April 2018

Membuat procedure mail dengan BLOB di oracle

Selasa, 10 Februari 2015

Instalasi xampp di linux