Sending email using Oracle procedures.

This is a post to share that how to sending out email by using oracle procedures. Some of the time. As a developer, I would like this receive some notification email from my procedures, so that I could know that the procedures is running & how many records had been update/inserted.

Below is the same procedures coding.

CREATE OR REPLACE
PROCEDURE TEST
AS
l_mailhost VARCHAR2(64) := ‘‘;
l_from VARCHAR2(64) := ‘XXXXX’;
l_to VARCHAR2(64) := ‘XXXXX’;
l_mail_conn UTL_SMTP.connection;

BEGIN
l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
UTL_SMTP.helo(l_mail_conn, l_mailhost);

– For Authenication
UTL_SMTP.command(l_mail_conn,’AUTH LOGIN’);
UTL_SMTP.command(l_mail_conn, UTL_RAW.CAST_TO_VARCHAR2(
UTL_ENCODE.BASE64_ENCODE(
UTL_RAW.CAST_TO_RAW(‘USERNAME’)
)
));
UTL_SMTP.command(l_mail_conn, UTL_RAW.CAST_TO_VARCHAR2(
UTL_ENCODE.BASE64_ENCODE(
UTL_RAW.CAST_TO_RAW(‘PASSWORD‘)
)
));
– For Authenication

UTL_SMTP.mail(l_mail_conn, l_from);
UTL_SMTP.rcpt(l_mail_conn, l_to);

UTL_SMTP.open_data(l_mail_conn);

UTL_SMTP.write_data(l_mail_conn, ‘Date: ‘ || TO_CHAR(SYSDATE, ‘DD-MON-YYYY HH24:MI:SS’) || utl_tcp.CRLF);
UTL_SMTP.write_data(l_mail_conn, ‘From: ‘ || l_from || utl_tcp.CRLF);
UTL_SMTP.write_data(l_mail_conn, ‘Subject: ‘ || ‘test’ || utl_tcp.CRLF);
UTL_SMTP.write_data(l_mail_conn, ‘To: ‘ || l_to || utl_tcp.CRLF);
UTL_SMTP.write_data(l_mail_conn, ” || utl_tcp.CRLF);

UTL_SMTP.write_data(l_mail_conn, ‘test’);

UTL_SMTP.close_data(l_mail_conn);

UTL_SMTP.quit(l_mail_conn);
END TEST;