Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Problem in Sending Email

584260May 13 2009 — edited May 25 2009
HI,

I have created the following package to send email,

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) := 'mymailserver';

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



begin
mail_pkg.send
( p_sender_email => 'mymailid@mycompany.com',
p_from => 'mymailid@mycompany.com'
p_to => mail_pkg.array( 'mymaildid@mycompany.com'),
p_cc => mail_pkg.array( 'mymaildid@mycompany.com'),
p_bcc => mail_pkg.array( 'mymaildid@mycompany.com'),
p_subject => 'PL/SQL Mail',
p_body => 'Test Mail' );
end;


I am using Outlook Express,

When i execute the package i,m getting the following errors,

*
ERROR at line 1:
ORA-29278: SMTP transient error: 425 Connection refused. You are not
authorized to use the Mail protocol through WinProxy.<BR>See your network
administrator.
ORA-06512: at "SYS.UTL_SMTP", line 20
ORA-06512: at "SYS.UTL_SMTP", line 96
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "SUMMARY.MAIL_PKG", line 48
ORA-06512: at line 2


Can anyone help me in this.


Thanks
Radha K
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 22 2009
Added on May 13 2009
2 comments
389 views