Tuesday, July 1, 2008

Sending mails from PLSQL..UTL_SMTP

Hi All,
Recently we got a requirement for sending mail..on a business logic..we tried to set the mail system on alerts..but still not succesful due to some other reasons..which we are still figuring out..
In the mean time we tried to send mail functionality from PLSQL..This is one package we found out in asktom.oracle.com...use this one directly to compile ..and send mail...it works...

create or replace package XX_DOF_mail_pkg as
type array1 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 );

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) := 'govmail1.gov.com;--Give your smtp mail address..check whether it exists in teh host file or not...
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) ;
l_recipients := l_recipients ', ' p_recipients(i);
end if;
end loop;
return l_recipients;
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
if ( p_text is not null ) then
utl_smtp.write_data( g_mail_conn, p_text g_crlf );
end if;
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;
use this script to test the error....
begin mail_pkg.send
( p_sender_email => 'murthyganjam@gmail.com',
p_from => 'Oracle Database Account <mailto:murthyganjam@gmail.com,
p_to => mail_pkg.array( 'murthyganjam@gmail.com', 'murthyganjam@gmail.com' ),
p_cc => mail_pkg.array( 'murthyganjam@gmail.com' ),
p_bcc => mail_pkg.array( 'murthyganjam@gmail.com' ),
p_subject => 'Testing mailing package',
p_body => 'Hello murthy, this is the mail you need' );
in case you want to handle the exception block for this program use the following exception block in the package...

dbms_output.put_line(' Invalid Operation in SMTP transaction.');
dbms_output.put_line(' Temporary problems with sending email - try again later.');
dbms_output.put_line(' Errors in code for SMTP transaction.');


sap upgrade challenges said...

This is the new concept I learned with the help of this post. I learned how to send mails from PLSQL__UTL_SMTP. At first time I found it difficult. This is a new experience for me. I feel great after learning it. Thanks for this.

Sridevi Koduru said...
This comment has been removed by the author.
Sridevi Koduru said...
This comment has been removed by the author.
Sridevi Koduru said...

Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.