Oracle Database Discussions

Hi,

Can someone help me to find out the issue in the given pakg.
I am tring to send email through this package but i am getting some errors:-

create or replace package body mail_pkg
as
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;

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;

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') || ' +8:00 (GMT+8:00)';

begin
SELECT PARAM_VALUE INTO g_mailhost FROM IHR_PARAM_DATA
WHERE PARAM_CODE = 'MAIL_HOST' AND
( ( CATEGORY = 'S' ) ) AND
( ( ( FROM_DATE IS NULL ) AND ( TO_DATE IS NULL ) ) OR
( ( FROM_DATE IS NULL ) AND ( TO_DATE >= TRUNC(SYSDATE) ) ) OR
( ( FROM_DATE <= TRUNC(SYSDATE) ) AND ( TO_DATE IS NULL ) ) OR
( ( FROM_DATE <= TRUNC(SYSDATE) ) AND ( TO_DATE >= TRUNC(SYSDATE) ) ) ) AND
SEQUENCE = ( SELECT MIN(SEQUENCE) FROM IHR_PARAM_DATA
WHERE PARAM_CODE = 'MAIL_HOST' AND
( ( CATEGORY = 'S' ) ) AND
( ( ( FROM_DATE IS NULL ) AND ( TO_DATE IS NULL ) ) OR
( ( FROM_DATE IS NULL ) AND ( TO_DATE >= TRUNC(SYSDATE) ) ) OR
( ( FROM_DATE <= TRUNC(SYSDATE) ) AND ( TO_DATE IS NULL ) ) OR
( ( FROM_DATE <= TRUNC(SYSDATE) ) AND ( TO_DATE >= TRUNC(SYSDATE ) ) ) ) );

g_mail_conn := utl_smtp.open_connection(g_mailhost, 25);
utl_smtp.helo(g_mail_conn, g_mailhost);
utl_smtp.command(g_mail_conn , 'auth login');
utl_smtp.command(g_mail_conn ,
mail_pkg.f_base64_encode_raw(utl_raw.cast_to_raw('mailuser')));
utl_smtp.command(g_mail_conn ,
mail_pkg.f_base64_encode_raw(utl_raw.cast_to_raw('******')));
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;


I am getting the given below error when tring to execute the procedure even though my email and password are correct.

ERROR at line 1:
ORA-29279: SMTP permanent error: 535 authorization failed (#5.7.0)
ORA-06512: at "SYS.UTL_SMTP", line 21
ORA-06512: at "SYS.UTL_SMTP", line 99
ORA-06512: at "SYS.UTL_SMTP", line 159
ORA-06512: at "SYSTEM.SEND_MAIL", line 12
ORA-06512: at line 1

pls help;

Thanks,
Uttam Singh

Edited by: Uttam Singh on Dec 26, 2008 7:56 PM