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;

2 Comments to “Sending email using Oracle procedures.”

  1. Stanley Lindowris 30 January 2009 at 3:34 pm #

    i’m from indonesia, i don’t speak english but i like this website.thanks very much. this is website is very good. please, give me tutorial else with PHP ORACLE Example make guestbook,news online,and so on……………thanks you, thanks you

  2. h2Guru 31 January 2009 at 7:18 pm #

    Hi stanley,
    Thanks for your visiting.
    Regarding your enquiries, I think I cant provide the example at this moment.
    I just sharing some of my experience to your guys. If I am writing on this kind of applications, I will post in here.
    Thanks.


Leave a Reply