PB à toute heure et à tout moment. (à parcourir avec modération)

Le forum (ô combien francophone) des utilisateurs de Powerbuilder.

Recherche rapide

Annonce

Certaines rubriques, dont des cours, sont uniquement visibles par les membres du forum ^^.
Dans la rubrique Liens & Références, vous avez accès à un sommaire de téléchargement, profitez-en !
Il existe maintenant un nouveau TOPIC "Votre CV en Ligne" accessible uniquement par demande.

Pages: 1

 

#1 21-06-2007 08:39:58

JCZ  
Builder Power
Award: bf
Lieu: 75019 paris
Date d'inscription: 21-05-2007
Messages: 1724
Pépites: 496,453,703,213
Banque: 9,223,372,036,854,776,000

[SOURCE] Exemple mail par oracle

Voici un exemple d’envoi de mail avec et sans fichier attaché sous Oracle. Il faut créer les packages contenus dans les fichiers demo_mail et demo_base64 avant de les tester.

Code: sql

CREATE OR REPLACE PACKAGE demo_base64 IS
   
   -- Base64-encode a piece of binary data.
   --
   -- Note that this encode function does not split the encoded text into
   -- multiple lines with no more than 76 bytes each as required by
   -- the MIME standard.
   --
   FUNCTION encode(r IN RAW) RETURN VARCHAR2;

END;
/

CREATE OR REPLACE PACKAGE BODY demo_base64 IS
   
   TYPE vc2_table IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
   map vc2_table;
   
   -- Initialize the Base64 mapping
   PROCEDURE init_map IS
   BEGIN
      map(0) :='A'; map(1) :='B'; map(2) :='C'; map(3) :='D'; map(4) :='E';
      map(5) :='F'; map(6) :='G'; map(7) :='H'; map(8) :='I'; map(9):='J';
      map(10):='K'; map(11):='L'; map(12):='M'; map(13):='N'; map(14):='O';
      map(15):='P'; map(16):='Q'; map(17):='R'; map(18):='S'; map(19):='T';
      map(20):='U'; map(21):='V'; map(22):='W'; map(23):='X'; map(24):='Y';
      map(25):='Z'; map(26):='a'; map(27):='b'; map(28):='c'; map(29):='d';
      map(30):='e'; map(31):='f'; map(32):='g'; map(33):='h'; map(34):='i';
      map(35):='j'; map(36):='k'; map(37):='l'; map(38):='m'; map(39):='n';
      map(40):='o'; map(41):='p'; map(42):='q'; map(43):='r'; map(44):='s';
      map(45):='t'; map(46):='u'; map(47):='v'; map(48):='w'; map(49):='x';
      map(50):='y'; map(51):='z'; map(52):='0'; map(53):='1'; map(54):='2';
      map(55):='3'; map(56):='4'; map(57):='5'; map(58):='6'; map(59):='7';
      map(60):='8'; map(61):='9'; map(62):='+'; map(63):='/';
   END;
   
   FUNCTION encode(r IN RAW) RETURN VARCHAR2 IS
     i pls_integer;
     x pls_integer;
     y pls_integer;
     v VARCHAR2(32767);
   BEGIN
      
      -- For every 3 bytes, split them into 4 6-bit units and map them to
      -- the Base64 characters
      i := 1;
      WHILE ( i + 2 <= utl_raw.length(r) ) LOOP
   x := to_number(utl_raw.substr(r, i, 1), '0X') * 65536 +
        to_number(utl_raw.substr(r, i + 1, 1), '0X') * 256 +
        to_number(utl_raw.substr(r, i + 2, 1), '0X');
   y := floor(x / 262144); v := v || map(y); x := x - y * 262144;
   y := floor(x / 4096);   v := v || map(y); x := x - y * 4096;
   y := floor(x / 64);   v := v || map(y); x := x - y * 64;
                           v := v || map(x);
   i := i + 3;
      END LOOP;

      -- Process the remaining bytes that has fewer than 3 bytes.
      IF ( utl_raw.length(r) - i = 0) THEN
   x := to_number(utl_raw.substr(r, i, 1), '0X');
   y := floor(x / 4);   v := v || map(y); x := x - y * 4;
   x := x * 16;            v := v || map(x);
         v := v || '==';
      ELSIF ( utl_raw.length(r) - i = 1) THEN
   x := to_number(utl_raw.substr(r, i, 1), '0X') * 256 +
          to_number(utl_raw.substr(r, i + 1, 1), '0X');
   y := floor(x / 1024);   v := v || map(y); x := x - y * 1024;
   y := floor(x / 16);   v := v || map(y); x := x - y * 16;
   x := x * 4;             v := v || map(x);
         v := v || '=';
      END IF;
      
      RETURN v;
   
   END;

BEGIN
   init_map;
END;
/

Code: sql

CREATE OR REPLACE PACKAGE BODY demo_mail IS
-- Return the next email address in the list of email addresses, separated
-- by either a "," or a ";". The format of mailbox may be in one of these:
-- someone@some-domain
-- "Someone at some domain" someone@some-domain
-- Someone at some domain someone@some-domain

 FUNCTION get_address(addr_list IN OUT VARCHAR2) RETURN VARCHAR2 IS
 addr VARCHAR2(256);
 i pls_integer;

 FUNCTION lookup_unquoted_char(str IN VARCHAR2,
 chrs IN VARCHAR2) RETURN pls_integer AS
 c VARCHAR2(5);
 i pls_integer;
 len pls_integer;
 inside_quote BOOLEAN;
 BEGIN
 inside_quote := false;
 i := 1;
 len := length(str);
 WHILE (i <= len) LOOP
  c := substr(str, i, 1);
  IF (inside_quote) THEN
   IF (c = '"') THEN
    inside_quote := false;
   ELSIF (c = '\') THEN
    i := i + 1; -- Skip the quote character
   END IF;

   GOTO next_char;

  END IF;
  IF (c = '"') THEN
   inside_quote := true;
   GOTO next_char;
  END IF;

  IF (instr(chrs, c) >= 1) THEN
   RETURN i;
  END IF;
<<next_char>>
  i := i + 1;

  END LOOP;
 RETURN 0;
 END;

 BEGIN
  addr_list := ltrim(addr_list);
  i := lookup_unquoted_char(addr_list, ',;');
  IF (i >= 1) THEN
   addr := substr(addr_list, 1, i - 1);
   addr_list := substr(addr_list, i + 1);
  ELSE
   addr := addr_list;
   addr_list := '';
  END IF;
 
  i := lookup_unquoted_char(addr, '<');
  IF (i >= 1) THEN
   addr := substr(addr, i + 1);
   i := instr(addr, '>');
  
  IF (i >= 1) THEN
   addr := substr(addr, 1, i - 1);
  END IF;
 END IF;
 RETURN addr;
 END;

 -- Write a MIME header
 PROCEDURE write_mime_header(conn IN OUT NOCOPY utl_smtp.connection,
 name IN VARCHAR2,
 value IN VARCHAR2) IS
 BEGIN
 -- utl_smtp.write_data(conn, name || ': ' || value || utl_tcp.CRLF);
 utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(name || ': ' || value || utl_tcp.CRLF));
 END;

 -- Mark a message-part boundary. Set <last> to TRUE for the last boundary.
 PROCEDURE write_boundary(conn IN OUT NOCOPY utl_smtp.connection,
 last IN BOOLEAN DEFAULT FALSE) AS
 BEGIN
 IF (last) THEN
  utl_smtp.write_data(conn, LAST_BOUNDARY);
 ELSE
  utl_smtp.write_data(conn, FIRST_BOUNDARY);
 END IF;
 END;

------------------------------------------------------------------------
 PROCEDURE mail(sender IN VARCHAR2,
 recipients IN VARCHAR2,
 subject IN VARCHAR2,
 message IN VARCHAR2) IS
 conn utl_smtp.connection;
 BEGIN
  conn := begin_mail(sender, recipients, subject);
  write_text(conn, message);
  end_mail(conn);
 END;
------------------------------------------------------------------------
 FUNCTION begin_mail(sender IN VARCHAR2,
 recipients IN VARCHAR2,
 subject IN VARCHAR2,
 mime_type IN VARCHAR2 DEFAULT 'text/plain',
 priority IN PLS_INTEGER DEFAULT NULL)
 RETURN utl_smtp.connection IS
 conn utl_smtp.connection;
 BEGIN
  conn := begin_session;
  begin_mail_in_session(conn, sender, recipients, subject, mime_type, priority);
  RETURN conn;
 END;
------------------------------------------------------------------------
 PROCEDURE write_text(conn IN OUT NOCOPY utl_smtp.connection, message IN VARCHAR2) IS
 BEGIN
  utl_smtp.write_data(conn, message);
 END;
------------------------------------------------------------------------
 PROCEDURE write_mb_text(conn IN OUT NOCOPY utl_smtp.connection, message IN VARCHAR2) IS
 BEGIN
  utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(message));
 END;
------------------------------------------------------------------------
 PROCEDURE write_raw(conn IN OUT NOCOPY utl_smtp.connection, message IN RAW) IS
 BEGIN
  utl_smtp.write_raw_data(conn, message);
 END;
------------------------------------------------------------------------
 PROCEDURE attach_text(conn IN OUT NOCOPY utl_smtp.connection, data IN VARCHAR2,
 mime_type IN VARCHAR2 DEFAULT 'text/plain',
 inline IN BOOLEAN DEFAULT TRUE,
 filename IN VARCHAR2 DEFAULT NULL,
 last IN BOOLEAN DEFAULT FALSE) IS
 BEGIN
  begin_attachment(conn, mime_type, inline, filename);
  write_text(conn, data);
  end_attachment(conn, last);
 END;
------------------------------------------------------------------------
 PROCEDURE attach_mb_text(conn IN OUT NOCOPY utl_smtp.connection,
 data IN VARCHAR2,
 mime_type IN VARCHAR2 DEFAULT 'text/plain',
 inline IN BOOLEAN DEFAULT TRUE,
 filename IN VARCHAR2 DEFAULT NULL,
 last IN BOOLEAN DEFAULT FALSE) IS
 BEGIN
 begin_attachment(conn, mime_type, inline, filename);
 utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(data));
 -- write_text(conn, data);
 end_attachment(conn, last);
 END;
-----------------------------------------------------------------------
 PROCEDURE attach_base64(conn IN OUT NOCOPY utl_smtp.connection, 
 data IN RAW,
 mime_type IN VARCHAR2 DEFAULT 'application/octet',
 inline IN BOOLEAN DEFAULT TRUE,
 filename IN VARCHAR2 DEFAULT NULL,
 last IN BOOLEAN DEFAULT FALSE) IS
 i PLS_INTEGER;
 len PLS_INTEGER;
 BEGIN
  begin_attachment(conn, mime_type, inline, filename, 'base64');
  -- Split the Base64-encoded attachment into multiple lines
  i := 1;
  len := utl_raw.length(data);
  WHILE (i < len) LOOP
  IF (i + MAX_BASE64_LINE_WIDTH < len) THEN
   utl_smtp.write_raw_data(conn,
   --utl_encode.base64_encode(utl_raw.substr(data, i, MAX_BASE64_LINE_WIDTH)));
   demo_base64.encode(utl_raw.substr(data, i, MAX_BASE64_LINE_WIDTH)));
   
  ELSE
   utl_smtp.write_raw_data(conn,
   --utl_encode.base64_encode(utl_raw.substr(data, i)));
   demo_base64.encode(utl_raw.substr(data, i)));
  END IF;
  utl_smtp.write_data(conn, utl_tcp.CRLF);
  i := i + MAX_BASE64_LINE_WIDTH;
  END LOOP;
 end_attachment(conn, last);
 END;
------------------------------------------------------------------------
 PROCEDURE begin_attachment(conn IN OUT NOCOPY utl_smtp.connection,
 mime_type IN VARCHAR2 DEFAULT 'text/plain',
 inline IN BOOLEAN DEFAULT TRUE,
 filename IN VARCHAR2 DEFAULT NULL,
 transfer_enc IN VARCHAR2 DEFAULT NULL) IS
 BEGIN
  write_boundary(conn);
  write_mime_header(conn, 'Content-Type', mime_type);
  IF (filename IS NOT NULL) THEN
   IF (inline) THEN
    write_mime_header(conn, 'Content-Disposition', 'inline; filename="'||filename||'"');
   ELSE
    write_mime_header(conn, 'Content-Disposition','attachment; filename="'||filename||'"');
   END IF;
  END IF;
  
  IF (transfer_enc IS NOT NULL) THEN
   write_mime_header(conn, 'Content-Transfer-Encoding', transfer_enc);
  END IF;
  utl_smtp.write_data(conn, utl_tcp.CRLF);
 END;
------------------------------------------------------------------------
 PROCEDURE end_attachment(conn IN OUT NOCOPY utl_smtp.connection, last IN BOOLEAN DEFAULT FALSE) IS
 BEGIN
  utl_smtp.write_data(conn, utl_tcp.CRLF);
  IF (last) THEN
   write_boundary(conn, last);
  END IF;
 END;
------------------------------------------------------------------------
 PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection) IS
 BEGIN
  end_mail_in_session(conn);
  end_session(conn);
 END;
------------------------------------------------------------------------
 FUNCTION begin_session RETURN utl_smtp.connection IS 
 conn utl_smtp.connection;
 BEGIN
  -- open SMTP connection
  conn := utl_smtp.open_connection(smtp_host, smtp_port);
  utl_smtp.helo(conn, smtp_domain);
  RETURN conn;
 END;
------------------------------------------------------------------------
 PROCEDURE begin_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection,
 sender IN VARCHAR2,
 recipients IN VARCHAR2,
 subject IN VARCHAR2,
 mime_type IN VARCHAR2 DEFAULT 'text/plain',
 priority IN PLS_INTEGER DEFAULT NULL) IS
 my_recipients VARCHAR2(32767) := recipients;
 my_sender VARCHAR2(32767) := sender;
 BEGIN
  -- Specify sender's address (our server allows bogus address
  -- as long as it is a full email address (xxx@yyy.com).
  utl_smtp.mail(conn, get_address(my_sender));
  -- Specify recipient(s) of the email.
  WHILE (my_recipients IS NOT NULL) LOOP
   utl_smtp.rcpt(conn, get_address(my_recipients));
  END LOOP;
  -- Start body of email
  utl_smtp.open_data(conn);
  -- Set "From" MIME header
  write_mime_header(conn, 'From', sender);
  -- Set "To" MIME header
  write_mime_header(conn, 'To', recipients);
  -- Set "Subject" MIME header
  write_mime_header(conn, 'Subject', subject);
  -- Set "Content-Type" MIME header
  write_mime_header(conn, 'Content-Type', mime_type);
  -- Set "X-Mailer" MIME header
  write_mime_header(conn, 'X-Mailer', MAILER_ID);
  -- Set priority:
  -- High Normal Low
  -- 1 2 3 4 5
  IF (priority IS NOT NULL) THEN
   write_mime_header(conn, 'X-Priority', priority);
  END IF;
  -- Send an empty line to denotes end of MIME headers and
  -- beginning of message body.
  utl_smtp.write_data(conn, utl_tcp.CRLF);
  IF (mime_type LIKE 'multipart/mixed%') THEN
   write_text(conn, 'This is a multi-part message in MIME format.' ||
   utl_tcp.crlf);
  END IF;
 END;
------------------------------------------------------------------------
 PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection) IS
 BEGIN
  utl_smtp.close_data(conn);
 END;
------------------------------------------------------------------------
 PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection) IS
 BEGIN
  utl_smtp.quit(conn);
 END;
END;
/


Code: sql

-- Création d 'un répertoire sur le serveur

CREATE DIRECTORY TMPDIR AS 'C:\temp';
GRANT READ ON DIRECTORY TMPDIR To PUBLIC;
/
-- Placer le fichier test4.pdf dans ce répertoire pour la suite des tests


BEGIN
  demo_mail.mail(
    sender     => 'JCZ1@JCZ.com',
    recipients => 'JCZ2@JCZ.com',
    subject    => 'Test',
    message    => 'Hi! C''est un test');
END;
/

DECLARE
  conn utl_smtp.connection;
BEGIN
  conn := demo_mail.begin_mail(
    sender     => 'JCZ1@JCZ.com',
    recipients => 'JCZ2@JCZ.com',
    subject    => 'HTML E-mail Test',
    mime_type  => 'text/html');

  demo_mail.write_text(
    conn    => conn,
    message => '<h1>C est un test</i>.</h1>');
  
  demo_mail.end_mail( conn => conn );

END;
/


DECLARE
  conn      utl_smtp.connection;
  data      RAW(2100);
  chunks    PLS_INTEGER;
  pos       PLS_INTEGER;
  len       PLS_INTEGER;
  v_file_handle UTL_FILE.FILE_TYPE;
  fil BFILE;
  file_len PLS_INTEGER;
  modulo PLS_INTEGER;
  amt BINARY_INTEGER := 672 * 3;
  lobpieces PLS_INTEGER;
  MAX_LINE_WIDTH PLS_INTEGER := 54;
  buf RAW(2100); 
  filepos PLS_INTEGER := 1;

BEGIN
  conn := demo_mail.begin_mail(
    sender     => 'JCZ1@JCZ.com',
    recipients =>  'JCZ2@JCZ.com',
    subject    => 'Test',
    mime_type  => demo_mail.MULTIPART_MIME_TYPE);

  demo_mail.attach_text(
    conn      => conn,
    data      => '<h1>C est un test</h1>',
    mime_type => 'text/html');
  
  demo_mail.begin_attachment(
    conn         => conn,
    mime_type    => 'application/pdf',
    inline       => TRUE,
    filename     => 'test4.pdf',
    transfer_enc => 'base64');

  fil := BFILENAME('TMPDIR', 'test4.pdf');
  file_len := dbms_lob.getlength(fil);
  modulo := mod(file_len, amt);
  
  
  lobpieces := trunc(file_len / amt);
  if (modulo <> 0) then
   lobpieces := lobpieces + 1;
  end if;
  
  dbms_lob.fileopen(fil, dbms_lob.file_readonly);
 
  dbms_lob.read(fil, amt, filepos, buf);
  data := NULL;

  FOR i IN 1..lobpieces LOOP
  
    filepos := i * amt + 1;
    file_len := file_len - amt;
    data := utl_raw.concat(data, buf);

    --chunks := trunc(utl_raw.length(data) / MAX_LINE_WIDTH);
  
    --IF (i <> lobpieces) THEN
    --  chunks := chunks - 1;
    --END IF;
       
   len := utl_raw.length(data) - pos + 1;
   demo_mail.write_text( conn => conn, message => demo_base64.encode(utl_raw.substr(data, pos, len)) || utl_tcp.CRLF);
   data := NULL;
  
   if (file_len < amt and file_len > 0) then
    amt := file_len;
   end if;
 
   dbms_lob.read(fil, amt, filepos, buf);
  END LOOP;

 dbms_lob.fileclose(fil); 
 demo_mail.end_attachment( conn => conn );
 
 demo_mail.end_mail( conn => conn );

END;
/
Face à l'agression, la puissance de l'intelligence

Hors ligne

 

#2 21-06-2007 08:45:45

pick ouic  
La bourse ou la vie ^^
Award: gearotter
Lieu: Massy-Verrières
Date d'inscription: 29-05-2006
Messages: 4658
Pépites: 942
Banque: 2,147,483,647
Site web

Re: [SOURCE] Exemple mail par oracle

50 dans ta tirelire...

Connaitre son ignorance est une grande part de la connaissance.
http://animegifs.free.fr/anime/mazinger/mazinger.gif

Hors ligne

 

 

Pages: 1

Pied de page des forums

Propulsé par FluxBB 1.2.22