Re: send a csv file generated by reports from Forms

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Wed, 06 Jul 2005 21:15:55 +0100
Message-ID: <qmeoc1dclsite1lol56h1eb42f83s49vgi_at_4ax.com>

On Tue, 05 Jul 2005 08:22:10 -0700, DA Morgan <damorgan_at_psoug.org> wrote:

>stef1711 wrote:
>> version of database : 9.2.0.5
>> OS: sun Solaris V9
>
>You can not create attachments to emails in 9.2.0.5 as that capability
>does not exist in UTL_SMTP. With 10g it can be done using UTL_MAIL.

 It'd be a bit more accurate to say that there's no convenient way to do it with UTL_SMTP, whereas UTL_MAIL gives you a higher-level wrapper around mail formatting that makes it easier.

 But since UTL_SMTP gives you full control over the content of the email, you can certainly produce attachments. e.g. adapted from the example given in the manual, plus the appropriate MIME headers:

DECLARE
    c utl_smtp.connection;
    PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS     BEGIN
        utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF);     END;   BEGIN
    c := utl_smtp.open_connection('server.localhost');

    utl_smtp.helo(c, 'server.localhost');
    utl_smtp.mail(c, 'andyh_at_server.localhost');
    utl_smtp.rcpt(c, 'andyh_at_server.localhost');
    utl_smtp.open_data(c);
    send_header('From',    '"andyh" <andyh_at_server.localhost>');
    send_header('To',      '"andyh" <andyh_at_server.localhost>');
    send_header('Subject', 'Hello');

    send_header('Content-Transfer-Encoding', 'binary');     send_header('Content-Type',

        'multipart/mixed; boundary="_----------=_112068030119160"');     send_header('MIME-Version', '1.0');     

    utl_smtp.write_data(

       c, utl_tcp.CRLF || 'This is a multi-part message in MIME format.');

    utl_smtp.write_data(c, utl_tcp.CRLF);
    utl_smtp.write_data(c, utl_tcp.CRLF || '--_----------=_112068030119160');
    utl_smtp.write_data(c, utl_tcp.CRLF || 'Content-Disposition: inline');
    utl_smtp.write_data(c, utl_tcp.CRLF || 
        'Content-Transfer-Encoding: binary');
    utl_smtp.write_data(c, utl_tcp.CRLF || 'Content-Type: text/plain');

    utl_smtp.write_data(c, utl_tcp.CRLF);     utl_smtp.write_data(c, utl_tcp.CRLF ||

        'CSV format data attached as test.csv');

    utl_smtp.write_data(c, utl_tcp.CRLF || '--_----------=_112068030119160');     utl_smtp.write_data(c, utl_tcp.CRLF ||

        'Content-Disposition: inline; filename="test.csv"');     utl_smtp.write_data(c, utl_tcp.CRLF ||

        'Content-Transfer-Encoding: binary');     utl_smtp.write_data(c, utl_tcp.CRLF ||

        'Content-Type: text/csv; name="test.csv"');

    utl_smtp.write_data(c, utl_tcp.CRLF);
    utl_smtp.write_data(c, utl_tcp.CRLF || '1,2,3');
    utl_smtp.write_data(c, utl_tcp.CRLF || '4,5,6');
    utl_smtp.write_data(c, utl_tcp.CRLF || '7,8,9');

    utl_smtp.close_data(c);
    utl_smtp.quit(c);
EXCEPTION
  WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN     BEGIN
      utl_smtp.quit(c);
    EXCEPTION

      WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
        NULL; -- When the SMTP server is down or unavailable, we don't have
              -- a connection to the server. The quit call will raise an
              -- exception that we can ignore.

    END;
    raise_application_error(-20000,
      'Failed to send mail due to the following error: ' || sqlerrm); END;
/

-- 
Andy Hassall / <andy_at_andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

Received on Wed Jul 06 2005 - 22:15:55 CEST