Hints and tricks for Oracle and other stuff...

procedure MailMessage( from_name varchar2,
                       to_name varchar2,
                       subject varchar2,
                       message varchar2,
                       smtp_server varchar2,
                       smtp_server_port number default 25,
                       max_size number default 9999999999,
                       filename1 varchar2 default null,
                       filename2 varchar2 default null,
                       filename3 varchar2 default null,
                       debug number default 0 ) is

v_smtp_server      varchar2(50) := smtp_server;
v_smtp_server_port number  := smtp_server_port;

v_directory_name   varchar2(100);
v_file_name        varchar2(100);
v_line             varchar2(1000);
crlf               varchar2(2):= '
';
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
 file_array(1) := filename1;
 file_array(2) := filename2;
 file_array(3) := filename3;
 conn:= utl_smtp.open_connection( v_smtp_server, v_smtp_server_port );
 utl_smtp.helo( conn, v_smtp_server );
 utl_smtp.mail( conn, from_name );
 utl_smtp.rcpt( conn, to_name );
 utl_smtp.open_data ( conn );
 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
  'text besedilo'  crlf
  ''  crlf
  '--DMW.Boundary.605592468'  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 );
for i in  1..3 loop
 exit when mesg_length_exceeded;
  if file_array(i) is not null then
   begin
     v_slash_pos := instr(file_array(i), '/', -1 );
     if v_slash_pos = 0 then
        v_slash_pos := instr(file_array(i), '\', -1 );
     end if;
     v_directory_name := substr(file_array(i), 1, v_slash_pos - 1 );
     v_file_name      := substr(file_array(i), v_slash_pos + 1 );
     v_file_handle := utl_file.fopen(v_directory_name, v_file_name, 'r' );
     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: 8bit'  crlf  crlf ;
      mesg_len := mesg_len + length(mesg);
      utl_smtp.write_data ( conn, mesg );
    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;
     when others then
       null;
     end;
     mesg := crlf;
   utl_smtp.write_data ( conn, mesg );
   utl_file.fclose(v_file_handle);
  end if;
 end loop;
 mesg := crlf  '--DMW.Boundary.605592468--'  crlf;
 utl_smtp.write_data ( conn, mesg );
 utl_smtp.close_data( conn );
 utl_smtp.quit( conn );
end;