oracle send mail
create or replace PROCEDURE p_send_mail
(
sender IN VARCHAR2,
recipient IN VARCHAR2,
ccrecipient IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2 DEFAULT NULL
) IS
crlf VARCHAR2(2):= UTL_TCP.CRLF;
connection utl_smtp.connection;
mailhost VARCHAR2(30):= 'Smtp-out-03.idc.com';
header VARCHAR2(1000);
BEGIN
-- Start the connection.
connection := utl_smtp.open_connection(mailhost,25);
header:= 'Date: '||TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss')||crlf||
'From: '||sender||''||crlf||
'Subject: '||subject ||''||crlf||
'To: '||recipient||crlf||
'CC: '||ccrecipient;
-- Handshake with the SMTP server
utl_smtp.helo(connection, mailhost);
utl_smtp.mail(connection, sender);
utl_smtp.rcpt(connection, recipient);
utl_smtp.rcpt(connection, ccrecipient);
utl_smtp.open_data(connection);
-- Write the header
utl_smtp.write_data(connection, header);
utl_smtp.write_data(connection, crlf ||message);
utl_smtp.close_data(connection);
utl_smtp.quit(connection);
EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Invalid Operation in SMTP transaction.');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary problems with sending email - try again
later.');
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Errors in code for SMTP transaction.');
END;