You may notice that this procedure does it's own internal e-mail logging via utl_file. You can turn it on or off as you wish. Or even change it from a file to a table. Your choice.
Thanks, List. I made the change and Its working now.
Is there any pre-defined e-mail logging feature can be enabled while sending the e-mail or Should this to be customized like inserting the error/mail log in a table by adding the steps in this procedure?
Raja.
-- --Original Message-- -- From: Goulet, Dick [mailto:DGoulet@(protected)] Sent: Monday, December 05, 2005 1:01 PM To: Nagarajan.Subbiah@(protected); oracle-l@(protected) Subject: RE: UTL_SMTP - Multiple Recipients
Here is the procedure that we're using, multiple addressee's is not a problem. Feel free to copy anything you like:
create or replace procedure vicor_mail(addressee in varchar2, subject in varchar2, message in varchar2, sender in varchar2 default null) is c utl_smtp.connection; v_crlf VARCHAR2(2):= CHR(13)||CHR(10); v_msg varchar2(4000); v_add_src varchar2(2000); v_addr varchar2(40); msg_chunck varchar2(2000); mail_server varchar2(30); madr varchar2(100); slen number := 1; dmy number := 0; a number := 0; t number := 0; logfp utl_file.file_type;
begin logfp := utl_file.fopen(orafunc.utl_home, 'vicor_mail.log','a'); utl_file.putf(logfp, '%s %s %s %s\n', to_char(sysdate,'DD-MON-YYYY HH24:MI'),addressee, subject, message); select default_mail_server into mail_server from oracle_server; c := utl_smtp.open_connection(mail_server); utl_smtp.helo(c, mail_server); if(sender is not null) then madr := replace(sender,' ','_'); utl_smtp.mail(c, madr); else utl_smtp.mail(c, 'Import<import@(protected)>'); end if; v_add_src := replace(addressee,' ','_'); if(instr(addressee,',') = 0) then utl_smtp.rcpt(c, v_add_src); else v_add_src := replace(addressee,' ','_')||','; while(instr(v_add_src,',',slen) > 0) loop v_addr := substr(v_add_src, slen, instr(substr(v_add_src, slen),',')-1); slen := slen+instr(substr(v_add_src, slen),','); utl_smtp.rcpt(c, v_addr); end loop; end if; utl_smtp.open_data(c); utl_smtp.write_data(c, 'SUBJECT: '||subject||utl_tcp.CRLF); v_msg := utl_tcp.CRLF||replace(replace(message,' ',' '),' ',chr(10)); utl_file.put_line(logfp, v_msg); utl_file.put_line(logfp, length(v_msg)); if(length(v_msg) > 2000) then while(a < length(v_msg)) loop msg_chunck := substr(v_msg, a, 2000); a := a+length(msg_chunck); utl_smtp.write_data(c,msg_chunck); end loop; else utl_smtp.write_data(c, v_msg); end if; utl_smtp.close_data(c); utl_smtp.quit(c); utl_file.fclose(logfp); end; /
-- --Original Message-- -- From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of Subbiah, Nagarajan Sent: Monday, December 05, 2005 11:37 AM To: 'oracle-l@(protected)' Subject: UTL_SMTP - Multiple Recipients
Hi,
I am using the below package for the send mail functionality from Oracle. When I use pass the multiple e-mail addresses (using comma or semicolon delimeter), I get the following error message. Do I have to parse and split the e-mail variable and call this package multiple times OR is there a way to use the multiple e-mail addresses in the UTL_SMTP?
PROCEDURE send_email( p_mailhost IN VARCHAR2, p_recipient IN VARCHAR2, p_message_body IN VARCHAR2, p_sender IN VARCHAR2, p_subject IN VARCHAR2, p_cc IN VARCHAR2 DEFAULT NULL, p_mailport IN NUMBER DEFAULT 25 ) /* This procedure sends an SMTP mail via the nominated mailhost using UTL_SMTP. Parameters are fairly self explanatory.
Restrictions: Messages are limited to 32k including Header. NO attachments.
Requires: Oracle JVM Oracle 8.1.7 At least one valid recipient.
Version History: V1.0 NL 08/11/01 -- webutils V1.1 NL 27/11/03 -- generalized */ IS l_mail_conn UTL_SMTP.connection; crlf CHAR(2) := CHR(13)||CHR(10); l_message VARCHAR2(32767); BEGIN /* Create message header per RFC */ l_message := 'Date: '||to_char(sysdate,'dd Mon yy hh24:mi:ss') ||crlf; l_message := l_message||'From: '||p_sender||crlf; l_message := l_message||'To: '||p_recipient||crlf; l_message := l_message||'CC: '||p_cc||crlf; l_message := l_message||'Subject: '||p_subject||crlf; /* add body after single cr/lf */ l_message := l_message||crlf||p_message_body; /* done */ l_mail_conn := utl_smtp.open_connection(p_mailhost, p_mailport); utl_smtp.helo(l_mail_conn, p_mailhost); utl_smtp.mail(l_mail_conn, p_sender); utl_smtp.rcpt(l_mail_conn, p_recipient); if p_cc is not null then -- mail ccs as well utl_smtp.rcpt(l_mail_conn, p_cc); end if; utl_smtp.data(l_mail_conn, l_message); utl_smtp.quit(l_mail_conn); EXCEPTION WHEN others THEN raise_application_error( -20000,
'Failed to send mail due to the following error: ' ||crlf|| sqlerrm); END send_email;
end webutils; -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l
