Wednesday, October 21, 2015

Oracle procedure send email

CREATE OR REPLACE PROCEDURE SP_SENDMAIL
(
mail_server in varchar2,
sender in varchar2,
recipient in varchar2,
subject in varchar2,
message in varchar2)
is
c utl_smtp.connection;
mesg varchar2 (4000);
crlf varchar2(2) := chr(13) || chr(10);
mlength number(4);
mdone number(4) := 0;
mpos number(3) :=1;
linewidth number(2) := 60;

begin

mlength:=length(message);

c := utl_smtp.open_connection(m
ail_server, 25);

utl_smtp.helo(c, mail_server);

utl_smtp.mail(c, sender);

utl_smtp.rcpt(c,recipient);

mesg := 'Date: ' || to_char(sysdate, 'dd Mon yy hh24:mi:ss');
mesg := mesg || crlf;
mesg := mesg || 'From: ' || sender || '<' ||
sender || '>';
mesg := mesg || crlf;
mesg := mesg || 'To: ' || recipient || '<' ||
recipient || '>';
mesg := mesg || crlf;
mesg := mesg || 'Subject: ' || subject;
mesg := mesg || crlf;
mesg := mesg || '' || crlf;

while mdone < mlength and mpos < mlength
loop

if (mlength - mdone) < linewidth then
mesg := mesg || substr(message, mpos, mlength - mdone) || crlf;

mdone := mdone + (mlength - mdone);
mpos := mlength;
else
mesg := mesg || substr(message, mpos, 60) || crlf;
mpos := mpos + 60;
end if;

end loop;


utl_smtp.data(c, mesg);
utl_smtp.quit(c);

exception
when utl_smtp.transient_error or
utl_smtp.permanent_error then
utl_smtp.quit(c);

raise_application_error(-20000, 'Failed to send mail due to following
' || sqlerrm);
end sp_sendmail;

No comments:

Post a Comment