Copyright 2010 le coin du dba oracle

Le coin du DBA Oracle

All rights reserved

Oracle est une marque déposée d'Oracle Corporation

Seconde procédure permettant de transmettre un courriel avec un fichier joint. (tout type de fichier). Il faut créer un objet de type directory sur Oracle, et donner les droits Lecture/écriture pour le propriétaire de la procédure.(Téléchargement de cette procédure)

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.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-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;           

      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;

/

Scripts Oracle Envoi de courriel suite