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.honeywell.com';
FUNCTION address_email( p_string IN VARCHAR2,
p_recipients IN ARRAY1 ) RETURN VARCHAR2
IS
l_recipients LONG;
BEGIN
FOR i IN 1 .. p_recipients.COUNT
LOOP
IF (p_recipients(i) IS NOT NULL) THEN
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 IF;
END LOOP;
RETURN l_recipients;
END;
PROCEDURE send( p_sender_email IN VARCHAR2,
p_from IN VARCHAR2 DEFAULT NULL,
p_to IN ARRAY1 ,
p_cc IN ARRAY1 ,
p_bcc IN ARRAY1 ,
p_subject IN VARCHAR2 ,
p_body IN CLOB 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' );
v_to1 ARRAY1;
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('MIME-Version: 1.0' ||CHR(13)|| CHR(10)||'Content-Type: text/html' );
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;
i am callin send function from outside..in v_to i am sending id of 40 users