Pas d'inquiétude, avec PBAdonf, c'est dans la poche ! ^^

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