Sunday 2 February 2014

Send mail In PL/SQL Code

Send mail using PLSQL code:

Prerequisite are:
smpt configuration needed.

declare
mailhost varchar2(50) :='XXX.XXX.XXX.XXX';
port number := 25;
mail_conn utl_smtp.connection;
sender varchar2(20) := 'dilip';
recepient varchar2(40) := 'XXXXXXX@XXXXX.com';
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
message1 varchar2(1000);
message2 varchar2(1000);
--cursor c1 is select * from tab where rownum<=5;
begin
mail_conn:=utl_smtp.open_connection(mailhost,port);
message1:='Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||
'From:'||Sender|| crlf ||
        'Subject: First test mail' || crlf ||'';
message2:='Hi using utl_smtp for the first time in my life'||crlf; 
for  i in (select * from tab where rownum<=5) loop
message2:=message2||i.tname||crlf; 
        end loop;
utl_smtp.helo(mail_conn,mailhost);
utl_smtp.mail(mail_conn,sender);
utl_smtp.rcpt(mail_conn,recepient);
utl_smtp.data(mail_conn,message1||crlf||message2);
utl_smtp.quit(mail_conn);
end;

No comments:

Post a Comment