Sending e-mail with attached file (zip)

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

Poston Wed, 13 Oct 2004 9:58 am

Personally, I have facing a problem with the routine below, whenever I try to send an email to compressed (.zip) file attached, upon receipt of this e-mail file he comes corrupted, making it impossible for its opening. Doing a comparison of the file sent with the received file I noticed that there is a slight difference in size between them. I imagine that's the problem, but I can't find a solution to it. This problem occurs only with compressed files, any other file I can send the mail without problems.

Code: Select all
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
Location: rs

Poston Wed, 22 Dec 2004 7:58 am

Have the link of TomKyte some examples of sending email using Java, might help: http://asktom.oracle.com/pls/ask/f?p=49 ... 5615160805, also has this example, but only supports TEXT as an attachment:
Code: Select all
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
Location: Novo Hamburgo - RS

Poston Wed, 29 Jun 2005 3:18 pm

see, in this first aí function ... so I go through the whole parameter attaching the file I have to pass the path where is he???

also I was wondering, if I have to put a progression bar to view the status of sending the email, type Outlook mesmo ...

If you have to give an example aí??


Thanks a..
Deathão
Location: Assis

--
Júnior

Poston Tue, 12 Jul 2005 10:09 am

good morning guys ... I tried to use this first function and gave the following error FRM-40735 WHEN BUTTON PRESSED trigger raised unhandled exception ORA-00600 know which error can be this?

grateful attention each of you ...
Deathão
Location: Assis

--
Júnior

Poston Tue, 12 Jul 2005 2:43 pm

Talks Jean I'm not sure, but I think UTL_FILE only supports text! (I saw that its routine use UTL_FILE to send). Maybe that's the problem! I found this routine below using DBMS_LOB. I think this will be able to send the binary attachments:
Code: Select all
  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;
/
I will send the LINK where I found it: http://www.uaex.edu/srea/email_files.htm
Code: Select all
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
Location: 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

Poston Tue, 12 Jul 2005 9:00 pm

: shock: damn each time is more complex these functions!!!!!!
Deathão
Location: Assis

--
Júnior

Poston Mon, 22 Feb 2010 11:15 am

Congratulations for the script, I would like to insert the text of the \"message\" field is not in an attached file, but directly to the e-mail.
And ' can do this? I try the following: does not work.

Code: Select all
   -- 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;
thanks to all antecipadament LuigI
baffardello
Location: Milan - italy

Poston Tue, 23 Feb 2010 5:54 am

Congratulations for the script, I would like to insert the text of the \"message\" field is not in an attached file, but directly to the e-mail.
And ' can do this? I try the following: does not work.

Code: Select all
   -- 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;
thanks to all antecipadament LuigI
baffardello
Location: Milan - italy


  • See also
    Replies
    Views
    Last Post