CREATE OR replace PROCEDURE Xxidl_send_blob (p_mailhost  VARCHAR2,
                                             p_port      NUMBER,
                                             p_sender    VARCHAR2,
                                             p_recipient VARCHAR2,
                                             p_cc        VARCHAR2,
                                             p_subject   VARCHAR2,
                                             p_filename  VARCHAR2,
                                             p_body      VARCHAR2,
                                             p_dir       VARCHAR2)
IS
  v_raw         RAW (57);
  v_length      INTEGER := 0;
  v_buffer_size INTEGER := 57;
  v_offset      INTEGER := 1;
  timeout       NUMBER := 180;
  mail_conn     utl_smtp.connection;
  p_blob        BLOB;
  temp_os_file  BFILE;
  ex            NUMBER;
  v_addr        VARCHAR2 (2500);
  l_firsttime   BOOLEAN;
  slen          NUMBER;
  p_boundary    VARCHAR2 (200) := '----=*#abc1234321cba#*=';
  p_attach_mime VARCHAR2 (200) := 'application/octet-stream';
BEGIN
    dbms_lob.Createtemporary (p_blob, TRUE);

    temp_os_file := Bfilename (p_dir, p_filename);

    ex := dbms_lob.Fileexists (temp_os_file);

    IF ex = 1 THEN
      dbms_lob.Fileopen (temp_os_file, dbms_lob.file_readonly);

      dbms_lob.Loadfromfile (p_blob, temp_os_file,
      dbms_lob.Getlength (temp_os_file)
      );

      dbms_lob.Fileclose (temp_os_file);
    END IF;

    dbms_output.Put_line ('File Exists count '
                          || ex);

    mail_conn := utl_smtp.Open_connection (p_mailhost, p_port);

    utl_smtp.Helo (mail_conn, p_mailhost);

    utl_smtp.Mail (mail_conn, p_sender);

    l_firsttime := TRUE;

    slen := 1;

    WHILE ( Instr (p_recipient, ',', slen) > 0 ) LOOP
        v_addr := Substr (p_recipient, slen, Instr (Substr (p_recipient, slen),
                                             ',')
                                             - 1
                  );

        slen := slen + Instr (Substr (p_recipient, slen), ',');

        l_firsttime := FALSE;

        dbms_output.Put_line ('While loop cheking'
                              || v_addr);
    END LOOP;

    IF l_firsttime
       AND Nvl (Length (p_recipient), 0) > 0 THEN
      utl_smtp.Rcpt (mail_conn, p_recipient);
    ELSE
      v_addr := Trim (Substr (p_recipient, slen, Length (p_recipient)));

      utl_smtp.Rcpt (mail_conn, v_addr);
    END IF;

    dbms_output.Put_line (' Address 1'
                          || v_addr
                          || 'p_recipient =>  '
                          || p_recipient);

    utl_smtp.Open_data (mail_conn);

    utl_smtp.Write_data (mail_conn, 'From'
                                    || ':'
                                    || p_sender
                                    || utl_tcp.crlf);

    utl_smtp.Write_data (mail_conn, 'To'
                                    || ':'
                                    || p_recipient
                                    || utl_tcp.crlf);

    utl_smtp.Write_data (mail_conn, 'CC'
                                    || ':'
                                    || p_cc
                                    || utl_tcp.crlf);

    utl_smtp.Write_data (mail_conn, 'Subject'
                                    || ':'
                                    || p_subject
                                    || utl_tcp.crlf);

    utl_smtp.Write_data (mail_conn, 'Mime-version: 1.0'
                                    || utl_tcp.crlf
                                    || 'Content-type: multipart/mixed;'
                                    || utl_tcp.crlf
                                    || 'boundary="-----SECBOUND"'
                                    || utl_tcp.crlf
                                    || '-------SECBOUND'
                                    || utl_tcp.crlf
                                    || 'Content-Type: text/html;'
                                    || utl_tcp.crlf
                                    || 'Content-Transfer_Encoding: 8bit'
                                    || utl_tcp.crlf
                                    || utl_tcp.crlf
                                    || p_body
                                    || utl_tcp.crlf
                                    || -- message body
                                    utl_tcp.crlf
                                    || utl_tcp.crlf
                                    || '-------SECBOUND'
                                    || utl_tcp.crlf
                                    || 'Content-Type: application/excel;'
                                    || utl_tcp.crlf
                                    || ' name="excel.log"'
                                    || utl_tcp.crlf
                                    ||
                                    -- file name that will hold the attached text
                                    'Content-Transfer_Encoding: 8bit'
                                    || utl_tcp.crlf);

    utl_smtp.Write_data (mail_conn,
    'Content-Disposition: attachment; filename="'
    || p_filename
    || '"'
    || utl_tcp.crlf);

    utl_smtp.Write_data (mail_conn, 'Content-Transfer-Encoding: base64'
                                    || utl_tcp.crlf);

    utl_smtp.Write_data (mail_conn, utl_tcp.crlf);

    v_length := dbms_lob.Getlength (p_blob);

    <<while_loop>>
    WHILE v_offset < v_length LOOP
        --      dbms_output.put_line('offset loop ') ;
        dbms_lob.READ (p_blob, v_buffer_size, v_offset, v_raw);

        utl_smtp.Write_raw_data (mail_conn, utl_encode.Base64_encode (v_raw));

        utl_smtp.Write_data (mail_conn, utl_tcp.crlf);

        v_offset := v_offset + v_buffer_size;
    END LOOP while_loop;

    utl_smtp.Write_data (mail_conn, utl_tcp.crlf);

    utl_smtp.Close_data (mail_conn);

    utl_smtp.Quit (mail_conn);
EXCEPTION
  WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
             utl_smtp.Quit (mail_conn);

             RAISE; WHEN OTHERS THEN
             RAISE;

             dbms_output.Put_line ('Error in send_blob '
                                   || SQLERRM);
END xxidl_send_blob; 

BEGIN
   xxglo_send_blob (p_mailhost       => '192.168.106.6',
                    p_port           => 25,
                    p_sender         => 'no_reply@test.com',
                    p_recipient      => 'test1@test.com',
                    p_cc             => 'test1@test.com',
                    p_subject        => 'IDL Records Successfully Processed',
                    p_filename       => 'XXGLO_TEST.xls',
                    p_body           => 'Hi This is test Mail',
                    p_dir            => 'XXGLO_IDL_DIR'
                   );
END;