CREATE OR REPLACE PROCEDURE ITEMS."ENVOIEMAILATTACH"    (NameDest IN VARCHAR2 DEFAULT '<toto.maison@provider.com>,<titi.maison@provider.com>',
 Objet IN VARCHAR2 DEFAULT 'Objet du message',
 BodyEmail IN VARCHAR2 DEFAULT 'Corp du message',Attachement IN BOOLEAN DEFAULT TRUE, pdirpath IN varchar2 DEFAULT 'DOC_DIR', pfilename IN varchar2 DEFAULT 'DOC.sql')
is
  c utl_smtp.connection;
  v_add_src varchar2(2000);
  v_addr varchar2(40);
  slen number := 1;
  SLP PLS_INTEGER := 300;
  v_reply UTL_SMTP.REPLY;
  b_connected BOOLEAN := FALSE;
  v_Bfile bfile;
  l_boundary    VARCHAR2(50) := '77LECOINDUDBA77';
  lensrc_off PLS_INTEGER := 1;
  lendst_off PLS_INTEGER := 1;
  lenBuffSize integer := 57;
  lIdx integer := 1;
  lraw raw(57):= NULL;
  LenBlob integer := 0;
  BEGIN
    BEGIN
    if Attachement = TRUE THEN
      v_Bfile := BFILENAME(pDirPath, pFileName);

    end if;
      EXCEPTION
       WHEN no_data_found THEN
         RETURN;
    end;

    FOR i IN 1 .. 3
    LOOP
      BEGIN
      c := utl_smtp.open_connection('smtp.provider.com');
      v_reply := utl_smtp.helo(c, 'smtp.provider.com');
      IF 250 = v_reply.code THEN
        b_connected := TRUE;
        EXIT;
      END IF;
      EXCEPTION
        WHEN OTHERS THEN
        DBMS_LOCK.SLEEP (SLP);
      END;
    END LOOP;

    IF b_connected = FALSE THEN
      RETURN;
    END IF;

    utl_smtp.mail(c, '<no-reply@peutimporte.com>');

    v_add_src := replace(NameDest,' ','_');
    if(instr(NameDest,',') = 0) then
      utl_smtp.rcpt(c, v_add_src);
    else
     v_add_src := replace(NameDest,' ','_')||',';
     while(instr(v_add_src,',',slen) > 0)
     loop
        v_addr := substr(v_add_src, slen, instr(substr(v_add_src,slen),',')-1);
        slen := slen+instr(substr(v_add_src, slen),',');
        utl_smtp.rcpt(c, v_addr);
     end loop;
    end if;

---    utl_smtp.rcpt(c, NameDest);
    utl_smtp.open_data(c);
    utl_smtp.write_data(c, 'From: <no-reply@peutimporte.com>'|| utl_tcp.CRLF);
    utl_smtp.write_data(c, 'To:'||NameDest|| utl_tcp.CRLF);
    utl_smtp.write_raw_data(c,utl_raw.cast_to_raw( 'Subject: '||Objet||utl_tcp.CRLF ));
    
    if Attachement = TRUE THEN
      utl_smtp.write_data(c,'Mime-Version: 1.0'||utl_tcp.CRLF);
      utl_smtp.write_data(c,'Content-Type: multipart/mixed;boundary="'||l_Boundary||'"'|| utl_tcp.CRLF|| ''|| utl_tcp.CRLF);
      utl_smtp.write_data(c,'--'||l_Boundary|| utl_tcp.CRLF);
    end if;

    utl_smtp.write_data(c, 'Content-Type: text/plain; charset=iso-8859-1'||utl_tcp.CRLF);
    utl_smtp.write_data(c, 'Content-Transfer-Encoding: quoted-printable'||utl_tcp.CRLF);
    utl_smtp.write_raw_data(c,utl_raw.cast_to_raw( utl_tcp.CRLF || BodyEmail));
    utl_smtp.write_data(c, utl_tcp.CRLF||utl_tcp.CRLF);

    if Attachement = TRUE THEN
      utl_smtp.write_data(c,'--'||l_Boundary|| utl_tcp.CRLF);
      utl_smtp.write_data(c,'Content-Type: application/octet-stream; name="'|| pFileName|| '"'|| utl_tcp.CRLF);
---      utl_smtp.write_data(c,'Content-Type: application/PDF; name="'|| pFileName|| '"'|| utl_tcp.CRLF);
      utl_smtp.write_data(c,'Content-Disposition: attachment; filename="'|| pFileName|| '"'|| utl_tcp.CRLF);
      utl_smtp.write_data(c,'Content-Transfer-Encoding: base64'|| utl_tcp.CRLF|| utl_tcp.CRLF);
      BEGIN
      dbms_lob.fileopen(v_Bfile, dbms_lob.file_readonly);
      LenBlob := dbms_lob.GETLENGTH(v_Bfile);
      while lIdx < LenBlob loop
        dbms_lob.read(v_Bfile, lenBuffSize, lIdx, lraw);
        utl_smtp.write_raw_data(c,utl_encode.base64_encode(lraw));
        lraw := NULL;
        lIdx := lIdx + lenBuffSize;
      end loop;       
 --     utl_smtp.write_data(c, utl_tcp.CRLF);
      

      EXCEPTION
         WHEN no_data_found THEN
             utl_smtp.quit(c);
             return;
      END;
      dbms_lob.FILECLOSE(v_Bfile);
      utl_smtp.write_data(c, utl_tcp.CRLF||utl_tcp.CRLF|| '--'||l_Boundary||'--'|| utl_tcp.CRLF);
    end if;

      utl_smtp.close_data(c);
      utl_smtp.quit(c);
      EXCEPTION
       WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
         BEGIN
           utl_smtp.quit(c);
         EXCEPTION
           WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
             NULL; -- When the SMTP server is down or unavailable, we don't
                   -- have a connection to the server. The quit call will
                   -- raise an exception that we can ignore.
         END;
         raise_application_error(-20000,
           'Failed to send mail due to the following error: ' || sqlerrm);
     END;
/