How to send HTML based e-mail notification using PL/SQL

Hi,

The following code illustrates an example to send html based e-mail notification through PL/SQL.

The Declare Block:

declare
begin
sendmail.send ('eafin20@norelay.com',
'eafin20@norelay.com',
'Subject: Testing',
'Hello '
|| ',
|| Please find attached the '
|| ' Code. '
|| 'This file has been uploaded to '
|| 'BlogSpot.Com'
);
end;

The Package :

Note : You would have to specify the SMTP server as per the instance in :
utl_smtp.open_connection('10.210.42.82',25)
utl_smtp.helo(c, '10.210.42.82')

create or replace PACKAGE sendmail IS
procedure common (p_sender varchar2,
p_recipient varchar2,
p_subject varchar2,
c out utl_smtp.connection);

procedure send (p_sender varchar2,
p_recipient varchar2,
p_subject varchar2,
p_body varchar2 default null);

end sendmail;


create or replace PACKAGE BODY sendmail IS
procedure common (p_sender varchar2,
p_recipient varchar2,
p_subject varchar2,
c out utl_smtp.connection)
is v_recipient varchar2(1000);
begin
c := utl_smtp.open_connection('10.210.42.82',25);--make connection to smtp
utl_smtp.helo(c, '10.210.42.82'); --identify the domain of the sender
utl_smtp.mail(c, p_sender); --start a mail, specify the sender
utl_smtp.rcpt(c, p_recipient); --identify recipient
utl_smtp.open_data(c); --start the mail body
utl_smtp.write_data(c, 'From: ' || p_sender || utl_tcp.crlf);
utl_smtp.write_data(c, 'To: ' || p_recipient || utl_tcp.crlf);
utl_smtp.write_data(c, 'Subject: ' || p_subject || utl_tcp.crlf);
exception when utl_smtp.transient_error or utl_smtp.permanent_error then
utl_smtp.quit(c);
raise;
when others then
raise;
end common;
procedure send (p_sender varchar2,
p_recipient varchar2,
p_subject varchar2,
p_body varchar2 default null)
is c utl_smtp.connection;
begin
common(p_sender, p_recipient, p_subject, c);
utl_smtp.write_data(c, 'Content-Type: text/html' || utl_tcp.crlf);
utl_smtp.write_data(c, utl_tcp.crlf || p_body);
utl_smtp.close_data(c);
utl_smtp.quit(c);
exception when utl_smtp.transient_error or utl_smtp.permanent_error then
utl_smtp.quit(c);
raise;
when others then
raise;
end send;
end sendmail;

You can use the HTML tags in the p_body parameter as per your format.

Thanks,
Gaurav