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

Procédure permettant d'envoyer un courriel automatiquement à partir d'Oracle. (Téléchargement du script)

Des tests préliminaire peuvent être fait en utilisant l'outil telnet

exemple:

       telnet smtp.provider.com 25

       helo provider.com

       mail from:votreAdress@provider.com

       rcpt to:AdressDest@autre.com

Pour les versions d'Oracle  > 10G

BEGIN

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'Fonctmail.xml',

                                    description => 'Mail from Oracle',

                                    principal   => 'OWNER',

                                    is_grant    => true,

                                    privilege   => 'connect');

 

 

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'Fonctmail.xml',

                                    host => 'smtp.provider.com',

                                    lower_port => 25,

                                    upper_port => 25);

END;

/

commit;

Procédure d'envoi de courriel sans pièce attaché et non sécurisé

CREATE OR REPLACE procedure user.EnvoieMail(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')

is

        c utl_smtp.connection;

  v_add_src varchar2(2000);

v_addr varchar2(40);

slen number := 1;

     BEGIN

       c := utl_smtp.open_connection('smtp.provider.com');

       utl_smtp.helo(c, 'smtp.provider.com');

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

----Permet d avoir les accents pour le sujet et le corps du courriel

       utl_smtp.write_raw_data(c,utl_raw.cast_to_raw( 'Subject: '||Objet||utl_tcp.CRLF ));

       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, 'Subject: '||Objet|| utl_tcp.CRLF);

--       utl_smtp.write_data(c, utl_tcp.CRLF || BodyEmail);

       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