SQL & PL/SQL

Hi All,

I am trying to send a mail through database. So, I tried to use a package mail_pkg given in Mr Tom link.
http://asktom.oracle.com/pls/asktom/f?p=100:12:0::NO::P12_QUESTION_ID,P12_PREV_PAGE:255615160805,11

I actually got success in doing so, the only problem is that when I have to send email to more users.. say 100 users and do I need to select individual mail id's from a table as below.Is there a better approach?

declare
Testid varchar2(2000);
Testid1 varchar2(2000);
Testid2 varchar2(2000);
Testid3 varchar2(2000);
Testid4 varchar2(2000);
Testid5 varchar2(2000);

begin

select email into Testid1 from Email_tables where NAME in ('Email1') and app_name='SendEmail';
select email into Testid2 from Email_tables where NAME in ('Email2') and app_name='SendEmail';
select email into Testid3 from Email_tables where NAME in ('Email3') and app_name='SendEmail';
select email into Testid4 from Email_tables where NAME in ('Email4') and app_name='SendEmail';
select email into Testid5 from Email_tables where NAME in ('Email5') and app_name='SendEmail';

mail_pkg.send( p_sender_email => 'test@abc.com',

p_from => 'Abc CorpMKB Account <test@abc.com>',

p_to => mail_pkg.array(Testid1,Testid2,Testid3,Testid4,Testid5,Testid6),

p_cc => mail_pkg.array('a@abc.com' ),

p_bcc => mail_pkg.array( 'tom@abc.com' ),
p_subject => 'This is a test',

p_body => 'Test Email' );

end;

If I need to send email to 100 users - Is there a better way to pass the emailid variables instead of specifing each one in the: p_to => mail_pkg.array(Testid1,Testid2,Testid3,Testid4,Testid5,Testid6),


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 default NULL,
p_to in array default array(),
p_cc in array default array(),
p_bcc in array default array(),
p_subject in varchar2 default NULL,
p_body in long default NULL );
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) := 'aaaaa';


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;


Thank you,


procedure send( p_sender_email in varchar2,
p_from in varchar2 default NULL,
p_to in array default array(),
p_cc in array default array(),
p_bcc in array default array(),
p_subject in varchar2 default NULL,
p_body in long default NULL )

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


Thank you.