Home » RDBMS Server » Server Administration » send excel file as attachment thru a plsql email
send excel file as attachment thru a plsql email [message #204620] Tue, 21 November 2006 07:02 Go to next message
frank.svs
Messages: 162
Registered: February 2006
Senior Member
Hi pals,

I need to attach an excel file and send an mail to my recepients.
I succedded in sending mails to multiple users cc,bcc,subject and normail text using PL/SQL.

But my requirement now is that i need to send a excel file via this email.So far i managed to get the following info

create or replace package mail_pkg
    as
            type array is table of varchar2(255);
            procedure send( p_sender_email in varchar2,
                            p_from         in varchar2,
                            p_to           in array default array(),
                            p_cc           in array default array(),
                            p_bcc          in array default array(),
                           p_subject      in varchar2,
                           p_body         in long );
	function address_email( p_string in varchar2, p_recipients in array ) return varchar2;
   end;
/

create or replace package body mail_pkg
as
g_crlf        char(2) default chr(13)||chr(10);
g_mail_conn   utl_smtp.connection;
g_mailhost    varchar2(255) := '<smtp server >;
procedure send( p_sender_email in varchar2,
                   p_from         in varchar2,
                   p_to           in array default array(),
                   p_cc           in array default array(),
                   p_bcc          in array default array(),
                   p_subject      in varchar2,
                   p_body         in long)
   is
       l_to_list   long;
       l_cc_list   long;
       l_bcc_list  long;
       l_date      varchar2(255) default to_char( SYSDATE, 'dd Mon yy hh24:mi:ss' );
       procedure writeData( p_text in varchar2 )
       as
       begin
           if ( p_text is not null )
           then
               utl_smtp.write_data( g_mail_conn, p_text || g_crlf );
           end if;
       end;
begin
       g_mail_conn := utl_smtp.open_connection(g_mailhost, 25);
       utl_smtp.helo(g_mail_conn, g_mailhost);
       utl_smtp.mail(g_mail_conn, p_sender_email);
       l_to_list  := address_email( 'To: ', p_to );
       l_cc_list  := address_email( 'Cc: ', p_cc );
       l_bcc_list := address_email( 'Bcc: ', p_bcc );
       utl_smtp.open_data(g_mail_conn );
       writeData( 'Date: ' || l_date );
       writeData( 'From: ' || nvl( p_from, p_sender_email ) );
       writeData( 'Subject: ' || nvl( p_subject, '(no subject)' ) );
        writeData( l_to_list );
       writeData( l_cc_list );
        utl_smtp.write_data( g_mail_conn, '' || g_crlf );
       utl_smtp.write_data(g_mail_conn, p_body );
       utl_smtp.close_data(g_mail_conn );
       utl_smtp.quit(g_mail_conn);
   end;

function address_email( p_string in varchar2, p_recipients in array ) return varchar2
   is
       l_recipients long;
   begin
      for i in 1 .. p_recipients.count
      loop
         utl_smtp.rcpt(g_mail_conn, p_recipients(i) );
         if ( l_recipients is null )
         then
             l_recipients := p_string || p_recipients(i) ;
         else
             l_recipients := l_recipients || ', ' || p_recipients(i);
         end if;
      end loop;
      return l_recipients;
   end;
  end;
/


If any one knows plz help me.

Thanks in advance,
Franky
Re: send excel file as attachment thru a plsql email [message #205980 is a reply to message #204620] Tue, 28 November 2006 04:51 Go to previous message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Look at the examples posted here.
Previous Topic: Regarding Background processes
Next Topic: Create User
Goto Forum:
  


Current Time: Wed Dec 07 06:35:54 CST 2016

Total time taken to generate the page: 0.08124 seconds