Wednesday, January 30, 2008

List prog Send Email from oracle database

PROCEDURE SEND
( I_from IN VARCHAR2,

I_to IN VARCHAR2,
I_cc IN VARCHAR2,
I_subject IN VARCHAR2,
I_message IN VARCHAR2,
I_mail_id IN NUMBER DEFAULT NULL ) IS
L_conn UTL_SMTP.CONNECTION;
L_sender VARCHAR2(255) := I_from;
L_recipient VARCHAR2(255) := I_to;
L_cc VARCHAR2(255) := I_cc;
BEGIN
L_conn := UTL_SMTP.OPEN_CONNECTION(LP_smtp_host, LP_smtp_port);
UTL_SMTP.HELO(L_conn, LP_smtp_domain);
UTL_SMTP.MAIL(L_conn, GET_ADDRESS(L_sender)); -- Sender
WHILE L_recipient IS NOT NULL LOOP
UTL_SMTP.RCPT(L_conn, GET_ADDRESS(L_recipient)); -- Recipient
END LOOP;
WHILE L_cc IS NOT NULL LOOP
UTL_SMTP.RCPT(L_conn, GET_ADDRESS(L_cc)); -- Recipient
END LOOP;
UTL_SMTP.OPEN_DATA(L_conn);
UTL_SMTP.WRITE_DATA(L_conn, 'From: '||I_from||UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(L_conn, 'To: '||I_to||UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(L_conn, 'cc: '||I_cc||UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(L_conn, 'Subject: '||I_subject||UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(L_conn, UTL_TCP.CRLF||I_message);
UTL_SMTP.CLOSE_DATA(L_conn);
UTL_SMTP.QUIT(L_conn);

EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
BEGIN
UTL_SMTP.QUIT(L_conn);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
NULL;
END;

END SEND;