REM Procedure mail_files
REM --------------------
REM
REM An Oracle PL/SQL procedure to send emails with file attachments from
REM within an Oracle PL/SQL program.
REM
REM Additional documentation for this script can be found at:
REM http://home.clara.net/dwotton/dba/oracle_smtp.htm
REM
REM If you are looking for a shell-script to send emails with file
REM attachments from the Unix environment, please refer to my web-page:
REM http://home.clara.net/dwotton/unix/mail_files.htm


create or replace procedure mail_files ( from_name varchar2,
                                         to_name varchar2,
                                         subject varchar2,
                                         message varchar2,
                                         max_size number default 9999999999,
                                         filename1 varchar2 default null,
                                         filename2 varchar2 default null,
                                         filename3 varchar2 default null,
                                         debug number default 0 ) is

/*
  This procedure uses the UTL_SMTP package to send an email message.
  Up to three file names may be specified as attachments.

  Parameters are:

    1) from_name (varchar2)
    2) to_name   (varchar2)
    3) subject   (varchar2)
    4) message   (varchar2)
    5) max_size  (number)
    5) filename1 (varchar2)
    6) filename2 (varchar2)
    7) filename3 (varchar2)

  eg.

    mail_files( from_name => 'oracle' ,
                to_name   => 'someone@somewhere.com' ,
                subject   => 'A test',
                message   => 'A test message',
                filename1 => '/data/oracle/dave_test1.txt',
                filename2 => '/data/oracle/dave_test2.txt');

  Most of the parameters are self-explanatory. "message" is a varchar2
  parameter, up to 32767 bytes long which contains the text of the message
  to be placed in the main body of the email.

  filename{1,2,3} are the names of the files to be attached to the email.
  The full pathname of each file must be specified. The files must exist
  in one of the directories specified in the init.ora parameter
  UTL_FILE_DIR. All filename parameters are optional: It is not necessary
  to specify unused file parameters (eg. filename3 is missing in the above
  example).

  The max_size parameter enables you to place a constraint on the maximum
  size of message, including all attachments, that the procedure will send.
  If this limit is exceeded, the procedure will truncate the message at
  that point with a '*** truncated ***' message. The default is effectively
  unlimited. However, the text of message body is still limited to 32Kb, as
  it is passed in as a varchar2.

  Obviously, as with any Oracle procedure, the parameter values can (and
  usually will be) PL/SQL variables, rather than hard-coded literals, as
  shown here.


  Written: Dave Wotton, 14/6/01 (Cambridge UK)
           This script comes with no warranty or support. You are free to
           modify it as you wish, but please retain an acknowledgement of
           my original authorship.

  Amended: Dave Wotton, 10/7/01
           Now uses the utl_smtp.write_data() method to send the message,
           eliminating the 32Kb message size constraint imposed by the
           utl_smtp.data() procedure.

  Amended: Dave Wotton, 20/7/01
           Increased the v_line variable, which holds the file attachment
           lines from 400 to 1000 bytes. This is the maximum supported
           by RFC2821, The Simple Mail Transfer Protocol specification.

  Amended: Dave Wotton, 24/7/01
           Now inserts a blank line before each MIME boundary line. Some
           mail-clients require this.

  Amended: Dave Wotton, 4/10/01
           Introduced a 'debug' parameter. Defaults to 0. If set to
           non-zero then errors in opening files for attaching are
           reported using dbms_output.put_line.
           Include code to hand MS Windows style pathnames.
*/



/*
  You may need to modify the following variable if you don't have a local
  SMTP service running (particularly relevant to Windows 2000 servers).
  Refer to http://home.clara.net/dwotton/dba/oracle_smtp.htm for more
  details.
*/

  v_smtp_server      varchar2(10) := 'localhost';
  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

   -- first load the three filenames into an array for easier handling later ...

   file_array(1) := filename1;
   file_array(2) := filename2;
   file_array(3) := filename3;

   -- Open the SMTP connection ...
   -- ------------------------

   conn:= utl_smtp.open_connection( v_smtp_server, v_smtp_server_port );

   -- Initial handshaking ...
   -- -------------------

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

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

   -- Append the files ...
   -- ----------------

   for i in  1..3 loop

       -- Exit if message length already exceeded ...

       exit when mesg_length_exceeded;

       -- If the filename has been supplied ...

       if file_array(i) is not null then

          begin

             -- locate the final '/' or '\' in the pathname ...

             v_slash_pos := instr(file_array(i), '/', -1 );

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

             -- separate the filename from the directory name ...

             v_directory_name := substr(file_array(i), 1, v_slash_pos - 1 );
             v_file_name      := substr(file_array(i), v_slash_pos + 1 );

             -- open the file ...

             v_file_handle := utl_file.fopen(v_directory_name, v_file_name, 'r' );

             -- generate the MIME boundary line ...

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

             -- and append the file contents to the end of the message ...

             loop

                 utl_file.get_line(v_file_handle, v_line);

                 if mesg_len + length(v_line) > max_size then

                    mesg := '*** truncated ***' || 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('Error in opening attachment '||
                                          file_array(i) );
                 end if;

             -- All other exceptions are ignored ....

             when others then
                 null;

          end;

          mesg := crlf;

          utl_smtp.write_data ( conn, mesg );

          -- close the file ...

          utl_file.fclose(v_file_handle);

        end if;

   end loop;

   -- append the final boundary line ...

   mesg := crlf || '--DMW.Boundary.605592468--' || crlf;

   utl_smtp.write_data ( conn, mesg );

   -- and close the SMTP connection  ...

   utl_smtp.close_data( conn );

   utl_smtp.quit( conn );

end;
/

