Envio de Email com arquivo (zip) anexado

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Mensagemem Qua, 13 Out 2004 9:58 am

Pessoal,

Estou com enfrentando um problema com a rotina abaixo, sempre que tento enviar um email com arquivo compactado (.zip) anexado, após o recebimento deste arquivo por email ele vem corrompido, impossibilitando a sua abertura. Fazendo uma comparação do arquivo enviado com o arquivo recebido verifiquei que há uma pequena diferença de tamanho entre eles. Imagino que seja este o problema, mas não consigo encontrar solução para ele. Este problema ocorre somente com arquivos compactados, qualquer outro arquivo consigo enviar o email sem problemas.

Código: Selecionar todos
CREATE OR REPLACE PROCEDURE PR_SENDMAIL (
       from_name     varchar2                    :='seu e-mail'
      ,to_name       varchar2                    :='e-mail'
      ,to_cc         varchar2                    :='cc'
      ,to_cco        varchar2                    :='cco'
      ,subject       varchar2                    :='Mensagem Oracle'
      ,message       varchar2                    :='Mensagem do Servidor Oracle'
      ,max_size      number   default 9999999999
      ,filename1  in varchar2                    :='/u41/spool/email/enviados/P183507.txt'
      ,filename2  in varchar2                    :='/u41/spool/email/enviados/P182303.txt'
      ,filename3  in varchar2                    :='/u41/spool/email/enviados/P182552.txt'
      ,debug         number   default 0 )

is

  v_smtp_server                          varchar2(30)                              := 'apisulweb'; -- Configuração para a CWI = 'mali.cwisl';
  v_smtp_server_port                     number                                    :=  25;
  v_directory_name                       varchar2(100);
  v_file_name                            varchar2(100);
  v_line                                 varchar2(1000);
  crlf                                   varchar2(2)                               := chr(13) ||
chr(10);
  mesg                                   varchar2(32767);

  conn                                   UTL_SMTP.CONNECTION;

  type varchar2_table  is table of       varchar2(200) index by binary_integer;
  file_array                             varchar2_table;

  i                                      binary_integer;
  v_file_handle                          utl_file.file_type;
  v_slash_pos                            number;
  mesg_len                               number;
  mesg_too_long                          exception;
  invalid_path                           exception;
  mesg_length_exceeded                   boolean                                   := false;

  begin
   -- Carregando os arquivos dentro do ARRAY
   -- ---------------------------------------
   file_array(1) := filename1;
   file_array(2) := filename2;
   file_array(3) := filename3;

   -- Abrindo Conexão SMTP e HTTP
   -- ----------------------------
   conn  := utl_smtp.open_connection( v_smtp_server, v_smtp_server_port );

   -- Comunicando SMTP
   -- ------------------

   utl_smtp.helo( conn, v_smtp_server );
   utl_smtp.mail( conn, from_name );
   utl_smtp.rcpt( conn, to_name );

   If to_cc is not null Then
      utl_smtp.rcpt( conn, to_cc );
   End If;
   
   If to_cco is not null Then
      utl_smtp.rcpt( conn, nvl(to_cco,0) );
   End If;

   utl_smtp.open_data ( conn );

   -- Criando Cabeça do E-mail
   -- -----------------------------------
   mesg:= 'Date: '    || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )                     || crlf ||
          'From: '    || from_name                                                      || crlf ||
          'Subject: ' || subject                                                        || crlf ||
          'To: '      || to_name                                                        || crlf ||
          'Cc: '      || nvl(to_cc,'')                                                        || crlf ||
          'Cco: '      || nvl(to_cco,'')                                                        || crlf ||
          'Mime-Version: 1.0'                                                           || crlf ||
          'Content-Type: multipart/mixed; boundary="DMW.Boundary.605592468"'            || crlf ||
          ''                                                                            || crlf ||
          'This is a Mime message, which your current mail reader may not'              || crlf ||
          'understand. Parts of the message will appear as text. If the remainder'      || crlf ||
          'appears as random characters in the message body, instead of as'             || crlf ||
          'attachments, then you''ll have to extract these parts and decode them'       || crlf ||
          'manually.'                                                                   || crlf ||
          ''                                                                            || crlf ||
          '--DMW.Boundary.605592468'                                                    || crlf ||
          'Content-Type:              text/plain; name="message.txt"; charset=US-ASCII' || crlf ||
          'Content-Disposition:       inline;     filename="message.txt"'               || crlf ||
          'Content-Transfer-Encoding: 7bit'                                             || crlf ||
          ''                                                                            || crlf ||
          message                                                                       || crlf ;

   mesg_len := length(mesg);

   if mesg_len > max_size then
      mesg_length_exceeded := true;
   end if;

   utl_smtp.write_data ( conn, mesg );

   -- Anexando Arquivos
   -- ------------------
   for i in  1..3 loop

       -- Sair se ultrapassar o tamanho de mensagem
       -- -----------------------------------------
       exit when mesg_length_exceeded;

       if file_array(i) is not null then

          begin

             -- Localiza a '/' ou '\' no caminho
             -- ---------------------------------
             v_slash_pos := instr(file_array(i), '/', -1 );

             if v_slash_pos = 0 then
                v_slash_pos := instr(file_array(i), '\', -1 ); -- Valor Retornado = 3

             end if;

             -- Separa o arquivo do diretório
             -- ------------------------------
             v_directory_name := substr(file_array(i), 1, v_slash_pos - 1);
             v_file_name      := substr(file_array(i), v_slash_pos + 1 );

             -- Abrir Arquivo
             -- --------------
             v_file_handle := utl_file.fopen('DIR_AVERBNET',v_file_name,'r');

             -- Gera a linha MIME boundary
             -- --------------------------
             mesg := crlf || '--DMW.Boundary.605592468' || crlf ||
             'Content-Type:              application/octet-stream; name=    ' || v_file_name  || crlf ||
             'Content-Disposition:       attachment;               filename=' || v_file_name  || crlf ||
             'Content-Transfer-Encoding: 7bit'                                              || crlf ||
crlf ;

             mesg_len := mesg_len + length(mesg);
             utl_smtp.write_data ( conn, mesg );

             -- Anexa o conteúdo do arquivo ao corpo da mensagem
             -- ------------------------------------------------

             loop

                 utl_file.get_line(v_file_handle, v_line);

                 if mesg_len + length(v_line) > max_size then

                    mesg := '*** truncado ***' || crlf;

                    utl_smtp.write_data ( conn, mesg );

                    mesg_length_exceeded := true;

                    raise mesg_too_long;

                 end if;

                 mesg := v_line || crlf;

                 utl_smtp.write_data ( conn, mesg );

                 mesg_len := mesg_len + length(mesg);

             end loop;

          exception

             when utl_file.invalid_path then
                 if debug > 0 then
                    dbms_output.put_line('Erro anexando arquivo ! '|| file_array(i));
                 end if;

             -- Todas EXCEPTIONS ignoradas
             when others then null;

          end;

          mesg := crlf;

          utl_smtp.write_data ( conn, mesg );

          -- Fecha Arquivo
          -- --------------
          utl_file.fclose(v_file_handle);

        end if;

   end loop;

   -- Fechando a Cabeça do E-mail
   -- ----------------------------
   mesg := crlf || '--DMW.Boundary.605592468--' || crlf;
   utl_smtp.write_data ( conn, mesg );

   -- Fechando conexão SMTP
   -- -----------------------
   utl_smtp.close_data( conn );
   utl_smtp.quit( conn );

end;
Jean
Localização: rs

Mensagemem Qua, 22 Dez 2004 7:58 am

Tem o link do TomKyte alguns exemplos de enviar email usando Java, talvez ajude:
http://asktom.oracle.com/pls/ask/f?p=49 ... 5615160805,

Também tem esse exemplo, mas só suporta TEXTO como anexo:
Código: Selecionar todos
PROCEDURE send_attach (
  msg_from    varchar2,                         ----- MAIL BOX SENDING THE EMAIL
  msg_to      varchar2 := '',                           ----- MAIL BOX RECIEVING THE EMAIL
  msg_subject varchar2 := 'Output file TEST1',          ----- EMAIL SUBJECT
  msg_text    varchar2 := 'THIS IS THE TEXT OF THE EMAIL MESSAGE.',
  v_output1   varchar2 := 'THIS IS THE TEXT OF THE ATTACHMENT FILE. THIS TEXT SHOULD BE IN A TEXT FILE ATTACHED TO THE EMAIL.')
IS
  c  utl_tcp.connection;
  rc integer;
  crlf VARCHAR2(2):= CHR(13)||CHR(10);
  mesg VARCHAR2( 32767 );
BEGIN
  c := utl_tcp.open_connection('999.999.999.999', 25);       ----- OPEN SMTP PORT CONNECTION
  rc := utl_tcp.write_line(c, 'HELO localhost');       ----- PERFORMS HANDSHAKING WITH SMTP SERVER
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'HELO 999.999.999.999');       ----- PERFORMS HANDSHAKING WITH SMTP SERVER, INCLUDING EXTRA INFORMATION
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);    ----- MAIL BOX SENDING THE EMAIL
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);        ----- MAIL BOX RECIEVING THE EMAIL
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'DATA');                     ----- EMAIL MESSAGE BODY START
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ));
  rc := utl_tcp.write_line(c, 'From: '||msg_from||' <'||msg_from||'>');
  rc := utl_tcp.write_line(c, 'MIME-Version: 1.0');
  rc := utl_tcp.write_line(c, 'To: '||msg_to||' <'||msg_to||'>');
  rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
  rc := utl_tcp.write_line(c, 'Content-Type: multipart/mixed;');     ----- INDICATES THAT THE BODY CONSISTS OF MORE THAN ONE PART
  rc := utl_tcp.write_line(c, ' boundary="-----SECBOUND"');          ----- SEPERATOR USED TO SEPERATE THE BODY PARTS
  rc := utl_tcp.write_line(c, '');                                   ----- INSERTS A BLANK LINE. PART OF THE MIME FORMAT AND NONE OF THEM SHOULD BE REMOVED.
  rc := utl_tcp.write_line(c, '-------SECBOUND');
  rc := utl_tcp.write_line(c, 'Content-Type: text/plain');           ----- 1ST BODY PART. EMAIL TEXT MESSAGE
  rc := utl_tcp.write_line(c, 'Content-Transfer-Encoding: 7bit');
  rc := utl_tcp.write_line(c, '');
  rc := utl_tcp.write_line(c, msg_text);                             ----- TEXT OF EMAIL MESSAGE
  rc := utl_tcp.write_line(c, '');
  rc := utl_tcp.write_line(c, '-------SECBOUND');
  rc := utl_tcp.write_line(c, 'Content-Type: text/plain;');          ----- 2ND BODY PART.
  rc := utl_tcp.write_line(c, ' name="Test.txt"');
  rc := utl_tcp.write_line(c, 'Content-Transfer_Encoding: 8bit');
  rc := utl_tcp.write_line(c, 'Content-Disposition: attachment;');   ----- INDICATES THAT THIS IS AN ATTACHMENT
  rc := utl_tcp.write_line(c, ' filename="Test.txt"');               ----- SUGGESTED FILE NAME FOR ATTACHMENT
  rc := utl_tcp.write_line(c, '');
  rc := utl_tcp.write_line(c, v_output1);
  rc := utl_tcp.write_line(c, '-------SECBOUND--');
  rc := utl_tcp.write_line(c, '');
  rc := utl_tcp.write_line(c, '.');                    ----- EMAIL MESSAGE BODY END
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'QUIT');                 ----- ENDS EMAIL TRANSACTION
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  utl_tcp.close_connection(c);                         ----- CLOSE SMTP PORT CONNECTION
EXCEPTION
  when others then
       raise_application_error(-20000, SQLERRM);
END send_attach;
tfg
Localização: Novo Hamburgo - RS

Mensagemem Qua, 29 Jun 2005 3:18 pm

viu, nessa primeira função aí...

pra eu passar por parâmetro o lance de anexar o arquivo eu tenho que passar o caminho onde ele está???

tambem gostaria de saber, se tem como eu colocar uma barra de progressão para ver o status do envio do e-mail, tipo Outlook mesmo..

se tiver tem como dar um exemplo aí??


valeu..
Deathão
Localização: Assis

--
Júnior

Mensagemem Ter, 12 Jul 2005 10:09 am

bom dia pessoal...

tentei usar essa primeira função e deu o seguinte erro

FRM-40735 WHEN BUTTON PRESSED trigger raised unhandled exception ORA-00600

sabe qual erro pode ser esse?

grato da atenção de cada um de vocês..
Deathão
Localização: Assis

--
Júnior

Mensagemem Ter, 12 Jul 2005 2:43 pm

Fala Jean
Eu não estou bem certo, mas eu acho que UTL_FILE só suporta TEXTO! (vi que sua rotina usa UTL_FILE pra enviar)... Talvez seja esse o problema! Eu achei essa rotina abaixo que usa DBMS_LOB. Eu acho que essa será capaz de enviar os anexos binários:

Código: Selecionar todos
  begin
  email_files(from_name => 'oracle' ,
      to_names          => 'srea@uaex.edu',
      subject           => 'A test',
      message           => 'A test message',
      html_message      => '<h2>A <u><i>test</i></u> message</h2>',
      filename1         => '/tmp/web_ptrbdca.txt',
      filename2         => '/tmp/password_standards.pdf',
      filetype2         => 'application/pdf',       ---> veja  PDF !!!
      filename3         => '/tmp/wb703.jpg',     --->  JPEG
      filetype3         => 'image/jpeg');
  end;
/


Vou mandar o LINK daonde eu achei isso:
http://www.uaex.edu/srea/email_files.htm

Código: Selecionar todos
rem
rem Script: ces_email_files.sql (for Oracle 9.2 and above)
rem
rem Purpose: Sends e-mail (text and/or html, either as a string or from a file)
rem to one or more recipients (including cc and/or bcc recipients), along with
rem up to 3 file attachments (text and/or binary; default is text/plain), using
rem the UTL_SMTP package to send the e-mail, the DBMS_LOB package to read
rem binary file attachments, and the UTL_ENCODE package to convert the binary
rem attachments to BASE64 for character string (non-binary) transmission.
rem BE AWARE THAT A COMMIT MAY BE DONE BY THIS ROUTINE (see HOWEVER... below).
rem
rem The complete parameter list for the email_files procedure is shown below:
rem    from_name - name and e-mail address to put in the From field
rem    to_names - names and e-mail addresses for the To field (separated by
rem       commas or semicolons)
rem    subject - text string for Subject field
rem    message - text string or text file name for Message, if any
rem    html_message - html string or html file name for Message, if any
rem    cc_names - names and e-mail addresses for the Cc field, if any
rem       (separated by commas or semicolons)
rem    bcc_names - names and e-mail addresses for the Bcc field, if any
rem       (separated by commas or semicolons)
rem    filename1 - first unix file pathname to attach, if any
rem    filetype1 - mime type of first file (defaults to 'text/plain')
rem    filename2 - second unix file pathname to attach, if any
rem    filetype2 - mime type of second file (defaults to 'text/plain')
rem    filename3 - third unix file pathname to attach, if any
rem    filetype3 - mime type of third file (defaults to 'text/plain')
rem
rem Sample names and e-mail addresses are: srea (attaches @<localhost>),
rem srea@uaex.edu, <srea@uaex.edu>, Steve Rea <srea@uaex.edu>, and
rem "Steve Rea" <srea@uaex.edu>
rem
rem A sample call in PL/SQL is shown below, which sends a text and html message,
rem plus a text file and two binary files (note: the slash after "end;" must be
rem the first character on it's line):
rem
rem    begin
rem       email_files(from_name    => 'oracle' ,
rem                   to_names     => 'srea@uaex.edu',
rem                   subject      => 'A test',
rem                   message      => 'A test message',
rem                   html_message => '<h2>A <u><i>test</i></u> message</h2>',
rem                   filename1    => '/tmp/web_ptrbdca.txt',
rem                   filename2    => '/tmp/password_standards.pdf',
rem                   filetype2    => 'application/pdf',
rem                   filename3    => '/tmp/wb703.jpg',
rem                   filetype3    => 'image/jpeg');
rem    end;
rem    /
rem
rem If the message or html_message string has a file name in it (starting with
rem a forward slash), the text or html file is copied into the e-mail as the
rem message or html message; otherwise, the message or html_message is copied
rem into the e-mail as-is.
rem
rem Attachment file types (mime types) that I'vê tested include:
rem    text/plain, text/html, image/jpeg, image/gif, application/pdf,
rem    application/msword
rem A list of mime types can be seen at:
rem    http://www.webmaster-toolkit.com/mime-types.shtml
rem If the mime type does not begin with "text", it is assumed to be a binary
rem file that will be encoded as base64 before transmission.
rem
rem This was derived from several sources, including:
rem    Dave Wotton (Cambridge UK) - mail_files.sql at
rem       http://home.clara.net/dwotton/dba/oracle_smtp.htm
rem    Oracle Technology Network - maildemo.sql (demo_mail) at
rem       http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/Utl_Smtp_Sample.html
rem    Akadia's "Read a file word by word using DBMS_LOB" Tip at
rem       http://www.akadia.com/services/read_file_with_dbms_lob.html
rem    Tom Kyte of Oracle's AskTom site (http://AskTom.oracle.com) -
rem       answer to "File Exists which is not a BFILE" and others.
rem
rem NOTE: The user running this must have "create any directory" and "drop any
rem directory" privileges ("create directory" was introduced in Oracle 9iR2),
rem which must be granted from a system or dba account, such as:
rem    grant create any directory to scott;
rem    grant drop any directory to scott;
rem  then:
rem    connect / as sysdba
rem    grant select on dba_directories to scott;
rem or, for everyone to have directory privileges:
rem    grant create any directory to public;
rem    grant drop any directory to public;
rem  then:
rem    connect / as sysdba
rem    grant select on dba_directories to public;
rem Any file in any directory accessible to the user can be attached (not just
rem the directories listed for the utl_file_dir parameter in the init.ora file).
rem HOWEVER, if you are using this to send e-mail's with attached files (or
rem reading the message text or message html from a file) from a trigger or
rem from some other SQL that you can't or don't want to have a commit done,
rem you will first need to create an Oracle directory entry for the directory
rem containing the attached files and grant read access to it to public, such
rem as:
rem    create directory CESDIR_COMMON as '/home/common';
rem    grant read on directory CESDIR_COMMON to public;
rem
rem You may also want to create a public synonym for this procedure, from the
rem procedure's owner:
rem    create or replace public synonym email_files for email_files;
rem    grant execute on email_files to public;
rem
rem FYI: I tried using the utl_file package to read binary files:
rem    utl_file.get_raw(v_file_handle,data,57);
rem but got "ORA-29284: file read error" on .pdf files, so, I switched to use
rem the dbms_lob package to read binary files:
rem    dbms_lob.read(v_bfile_handle,read_bytes,v_pos,data);
rem
rem Author: Stephen Rea <srea@uaex.edu>
rem    University of Arkansas Cooperative Extension Service
rem Initial release: 12/21/04
rem
rem Updates:
rem 1/31/05 - Fixed for triggers and other SQL that can't have a commit by
rem    checking for and using already-defined Oracle directories ("create
rem    directory" is a DDL statement that does a commit).
rem

create or replace procedure email_files(from_name varchar2,
                      to_names varchar2,
                      subject varchar2,
                      message varchar2 default null,
                      html_message varchar2 default null,
                      cc_names varchar2 default null,
                      bcc_names varchar2 default null,
                      filename1 varchar2 default null,
                      filetype1 varchar2 default 'text/plain',
                      filename2 varchar2 default null,
                      filetype2 varchar2 default 'text/plain',
                      filename3 varchar2 default null,
                      filetype3 varchar2 default 'text/plain')
is

   -- Change the SMTP host name and port number below to your own values,
   -- if not localhost on port 25:

   smtp_host          varchar2(256) := 'localhost';
   smtp_port          number := 25;

   -- Change the boundary string, if needed, which demarcates boundaries of
   -- parts in a multi-part email, and should not appear inside the body of
   -- any part of the e-mail:

   boundary           constant varchar2(256) := 'CES.Boundary.DACA587499938898';

   recipients         varchar2(32767);
   directory_path     varchar2(256);
   file_name          varchar2(256);
   crlf               varchar2(2):= chr(13) || chr(10);
   mesg               varchar2(32767);
   conn               UTL_SMTP.CONNECTION;
   type varchar2_table is table of varchar2(256) index by binary_integer;
   file_array         varchar2_table;
   type_array         varchar2_table;
   i                  binary_integer;

   -- Function to return the next email address in the list of email addresses,
   -- separated by either a "," or a ";".  From Oracle's demo_mail.  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 IS
         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;

   -- Procedure to split a file pathname into its directory path and file name
   -- components.

   PROCEDURE split_path_name(file_path IN VARCHAR2, directory_path OUT VARCHAR2,
      file_name OUT VARCHAR2) IS

      pos number;

   begin

      -- Separate the filename from the directory name

      pos := instr(file_path,'/',-1);
      if pos = 0 then
         pos := instr(file_path,'\',-1);
      end if;
      if pos = 0 then
         directory_path := null;
      else
         directory_path := substr(file_path,1,pos - 1);
      end if;
      file_name := substr(file_path,pos + 1);

   end;

   -- Procedure to append a file's contents to the e-mail

   PROCEDURE append_file(directory_path IN VARCHAR2, file_name IN VARCHAR2,
      file_type IN VARCHAR2, conn IN OUT UTL_SMTP.CONNECTION) IS

      generated_name  varchar2(30) := 'CESDIR' || to_char(sysdate,'HH24MISS');
      directory_name  varchar2(30);
      file_handle     utl_file.file_type;
      bfile_handle    bfile;
      bfile_len       number;
      pos             number;
      read_bytes      number;
      line            varchar2(1000);
      data            raw(200);
      my_code         number;
      my_errm         varchar2(32767);

   begin

      begin

         -- Grant access to the directory, unless already defined, and open
         -- the file (as a bfile for a binary file, otherwise as a text file).
   
         begin
            line := directory_path;
            select dd.directory_name into directory_name from dba_directories dd
               where dd.directory_path = line and rownum = 1;
         exception
            when no_data_found then
               directory_name := generated_name;
         end;
         if directory_name = generated_name then
            execute immediate 'create or replace directory ' || directory_name ||
               ' as ''' || directory_path || '''';
            execute immediate 'grant read on directory ' || directory_name ||
               ' to public';
         end if;
         if substr(file_type,1,4) != 'text' then
            bfile_handle := bfilename(directory_name,file_name);
            bfile_len := dbms_lob.getlength(bfile_handle);
            pos := 1;
            dbms_lob.open(bfile_handle,dbms_lob.lob_readonly);
         else
            file_handle := utl_file.fopen(directory_name,file_name,'r');
         end if;
   
         -- Append the file contents to the end of the message
   
         loop
   
            -- If it is a binary file, process it 57 bytes at a time,
            -- reading them in with a LOB read, encoding them in BASE64,
            -- and writing out the encoded binary string as raw data
   
            if substr(file_type,1,4) != 'text' then
               if pos + 57 - 1 > bfile_len then
                  read_bytes := bfile_len - pos + 1;
               else
                  read_bytes := 57;
               end if;
               dbms_lob.read(bfile_handle,read_bytes,pos,data);
               utl_smtp.write_raw_data(conn,utl_encode.base64_encode(data));
               pos := pos + 57;
               if pos > bfile_len then
                  exit;
               end if;
   
            -- If it is a text file, get the next line of text, append a
            -- carriage return / line feed to it, and write it out
   
            else
               utl_file.get_line(file_handle,line);
               utl_smtp.write_data(conn,line || crlf);
            end if;
     
         end loop;
   
      -- Output any errors, except at end when no more data is found
   
      exception
         when no_data_found then
            null;
         when others then
            my_code := SQLCODE;
            my_errm := SQLERRM;
            dbms_output.put_line('Error code ' || my_code || ': ' ||
               my_errm);
      end;

      -- Close the file (binary or text)

      if substr(file_type,1,4) != 'text' then
         dbms_lob.close(bfile_handle);
      else
         utl_file.fclose(file_handle);
      end if;
      if directory_name = generated_name then
         execute immediate 'drop directory ' || directory_name;
      end if;

   end;

begin

   -- Load the three filenames and file (mime) types into an array for
   -- easier handling later

   file_array(1) := filename1;
   file_array(2) := filename2;
   file_array(3) := filename3;
   type_array(1) := filetype1;
   type_array(2) := filetype2;
   type_array(3) := filetype3;

   -- Open the SMTP connection and set the From and To e-mail addresses

   conn := utl_smtp.open_connection(smtp_host,smtp_port);
   utl_smtp.helo(conn,smtp_host);
   recipients := from_name;
   utl_smtp.mail(conn,get_address(recipients));
   recipients := to_names;
   while recipients is not null loop
      utl_smtp.rcpt(conn,get_address(recipients));
   end loop;
   recipients := cc_names;
   while recipients is not null loop
      utl_smtp.rcpt(conn,get_address(recipients));
   end loop;
   recipients := bcc_names;
   while recipients is not null loop
      utl_smtp.rcpt(conn,get_address(recipients));
   end loop;
   utl_smtp.open_data(conn);

   -- Build the start of the mail message

   mesg := 'Date: ' || TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss') || crlf ||
      'From: ' || from_name || crlf ||
      'Subject: ' || subject || crlf ||
      'To: ' || to_names || crlf;
   if cc_names is not null then
      mesg := mesg || 'Cc: ' || cc_names || crlf;
   end if;
   if bcc_names is not null then
      mesg := mesg || 'Bcc: ' || bcc_names || crlf;
   end if;
   mesg := mesg || 'Mime-Version: 1.0' || crlf ||
      'Content-Type: multipart/mixed; boundary="' || boundary || '"' ||
      crlf || crlf ||
      'This is a Mime message, which your current mail reader may not' || crlf ||
      'understand. Parts of the message will appear as text. If the remainder' || crlf ||
      'appears as random characters in the message body, instead of as' || crlf ||
      'attachments, then you''ll have to extract these parts and decode them' || crlf ||
      'manually.' || crlf || crlf;
   utl_smtp.write_data(conn,mesg);

   -- Write the text message or message file, if any

   if message is not null then
      mesg := '--' || boundary || crlf ||
         'Content-Type: text/plain; name="message.txt"; charset=US-ASCII' ||
          crlf ||
         'Content-Disposition: inline; filename="message.txt"' || crlf ||
         'Content-Transfer-Encoding: 7bit' || crlf || crlf;
      utl_smtp.write_data(conn,mesg);
      if substr(message,1,1) = '/' then
         split_path_name(message,directory_path,file_name);
         append_file(directory_path,file_name,'text',conn);
         utl_smtp.write_data(conn,crlf);
      else
         utl_smtp.write_data(conn,message || crlf);
      end if;
   end if;

   -- Write the HTML message or message file, if any

   if html_message is not null then
      mesg := '--' || boundary || crlf ||
         'Content-Type: text/html; name="message.html"; charset=US-ASCII' ||
         crlf ||
         'Content-Disposition: inline; filename="message.html"' || crlf ||
         'Content-Transfer-Encoding: 7bit' || crlf || crlf;
      utl_smtp.write_data(conn,mesg);
      if substr(html_message,1,1) = '/' then
         split_path_name(html_message,directory_path,file_name);
         append_file(directory_path,file_name,'text',conn);
         utl_smtp.write_data(conn,crlf);
      else
         utl_smtp.write_data(conn,html_message || crlf);
      end if;
   end if;

   -- Append the files

   for i in 1..3 loop

      -- If the filename has been supplied ...

      if file_array(i) is not null then

         split_path_name(file_array(i),directory_path,file_name);

         -- Generate the MIME boundary line according to the file (mime) type
         -- specified.

         mesg := crlf || '--' || boundary || crlf;
         if substr(type_array(i),1,4) != 'text' then
            mesg := mesg || 'Content-Type: ' || type_array(i) ||
               '; name="' || file_name || '"' || crlf ||
               'Content-Disposition: attachment; filename="' ||
               file_name || '"' || crlf ||
               'Content-Transfer-Encoding: base64' || crlf || crlf ;
         else
            mesg := mesg || 'Content-Type: application/octet-stream; name="' ||
               file_name || '"' || crlf ||
               'Content-Disposition: attachment; filename="' ||
               file_name || '"' || crlf ||
               'Content-Transfer-Encoding: 7bit' || crlf || crlf ;
         end if;
         utl_smtp.write_data(conn,mesg);

         -- Append the file contents to the end of the message

         append_file(directory_path,file_name,type_array(i),conn);
         utl_smtp.write_data(conn,crlf);

      end if;
   end loop;

   -- Append the final boundary line

   mesg := crlf || '--' || boundary || '--' || crlf;
   utl_smtp.write_data(conn,mesg);

   -- Close the SMTP connection

   utl_smtp.close_data(conn);
   utl_smtp.quit(conn);

end;
/
dr_gori
Localização: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Mensagemem Ter, 12 Jul 2005 9:00 pm

:shock: caramba cada vez fica mais complexas essas funções!!!!!!
Deathão
Localização: Assis

--
Júnior

Mensagemem Seg, 22 Fev 2010 11:15 am

Parabéns pelo script,
Eu gostaria de inserir o texto do campo "message" Não é em um arquivo anexado,
mas diretamente para o e-mail.
E 'pode fazer isso? Eu tente o seguinte:

não funciona.

Código: Selecionar todos
   -- Write the text message or message file, if any

   if message is not null then
      mesg := '--' || boundary || crlf ||
         'Content-Type: text/plain; name="message.txt"; charset=US-ASCII' ||
          crlf ||
         'Content-Disposition: inline; filename="message.txt"' || crlf ||
         'Content-Transfer-Encoding: 7bit' || crlf || crlf;
      utl_smtp.write_data(conn,mesg);
      if substr(message,1,1) = '/' then
         split_path_name(message,directory_path,file_name);
         append_file(directory_path,file_name,'text',conn);
         utl_smtp.write_data(conn,crlf);
      else
--
-- luigi +++
         -- utl_smtp.write_data(conn,message );
         -- utl_smtp.write_data(conn,message || crlf);
         -- utl_smtp.data(conn,message || crlf);
   utl_smtp.data(conn, messagge );
   
-- luigi ---         
--         
      end if;


Obrigado a todos antecipadament
LuigI
baffardello
Localização: Milan - italy

Mensagemem Ter, 23 Fev 2010 5:54 am

Parabéns pelo script,
Eu gostaria de inserir o texto do campo "message" Não é em um arquivo anexado,
mas diretamente para o e-mail.
E 'pode fazer isso? Eu tente o seguinte:

não funciona.

Código: Selecionar todos
   -- Write the text message or message file, if any

   if message is not null then
      mesg := '--' || boundary || crlf ||
         'Content-Type: text/plain; name="message.txt"; charset=US-ASCII' ||
          crlf ||
         'Content-Disposition: inline; filename="message.txt"' || crlf ||
         'Content-Transfer-Encoding: 7bit' || crlf || crlf;
      utl_smtp.write_data(conn,mesg);
      if substr(message,1,1) = '/' then
         split_path_name(message,directory_path,file_name);
         append_file(directory_path,file_name,'text',conn);
         utl_smtp.write_data(conn,crlf);
      else
--
-- luigi +++
         -- utl_smtp.write_data(conn,message );
         -- utl_smtp.write_data(conn,message || crlf);
         -- utl_smtp.data(conn,message || crlf);
   utl_smtp.data(conn, messagge );
   
-- luigi ---         
--         
      end if;


Obrigado a todos antecipadament
LuigI
baffardello
Localização: Milan - italy


  • Veja também
    Respostas
    ExibiÇões
    Última mensagem