When the UTL_MAIL connection opens in Oracle 10

Question

I have a pl / sql procedure that sends letters to several people (20,000+). My question is related to utl_mail package and when is the connection really open on the email server.

Example:

BEGIN ... OPEN CUR_person; FETCH CUR_person INTO REC_person; WHILE CUR_person%FOUND LOOP UTL_MAIL.send(sender => ' [e-mail] ', recipients => ' [e-mail] ', subject => 'Test Mail', message => 'Hello World', mime_type => 'text/html'); FETCH CUR_person INTO REC_person; END LOOP; ... END; / 

I was wondering if the connection would open once per person or open to the first person and stay open until the procedure is completed?

If it is open once per person - is it bad coding? Will it bomb if I run into bad data?

If you need more information, please ask. Thanks!!

Answer 1

UTL_MAIL hides the underlying SMTP API, which is UTL_SMTP . This is preferred for ease of use. If you see UTL_SMTP , you will notice that you need to do utl_smtp.Rcpt for each email address and, unfortunately, it only accepts one email id at a time. Thus, having said this for more than one email address, the basic logic is the same, that is, a loop through the email addresses and calls utl_smtp.Rcpt for each email address. The best solution for this would be to simply use email distribution lists, that is, one email identifier that has a group of other email identifiers. So, with UTL_SMTP it looks something like this:

 declare v_From VARCHAR2(80) := ' [e-mail] '; v_cc VARCHAR2(80) := ' [e-mail] '; v_Recipient VARCHAR2(80) := ' [e-mail] '; v_Subject VARCHAR2(80) := 'test Subject'; v_Mail_Host VARCHAR2(50) := 'hub.fun.com'; v_Mail_Conn utl_smtp.Connection; crlf VARCHAR2(2) := chr(13)||chr(10); begin v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25); utl_smtp.Helo(v_Mail_Conn, v_Mail_Host); utl_smtp.Mail(v_Mail_Conn, v_From); utl_smtp.Rcpt(v_Mail_Conn, v_Recipient); utl_smtp.Rcpt(v_Mail_Conn, v_cc); -- To CC recepient utl_smtp.Data(v_Mail_Conn, 'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf || 'From: ' || v_From || crlf || 'Subject: '|| v_Subject || crlf || 'To: ' || v_Recipient || crlf || 'Cc: ' || v_cc || crlf || 'Content-Type: text/html;' ||crlf || 'Hello this is a test email'); utl_smtp.Quit(v_mail_conn); end; 

I don’t see any bad programming in the way your code looks, but rather its only way to do this is if you have several id id.s.Oracle identifiers need to send an email payload to the SMTP server (as indicated in you) for each email identifier, such as sending messages to a queue. A pretty obvious suggestion would be to create a mailing list pair with a uniform distribution of the heap of email identifiers and use DL identifiers rather than the actual email identifiers.

This is one of my Ask Tom questions that might clear that up a bit.

Another quick way would be to change your code to create a string with an address separated by commas, since UTL_MAIL can accept email addresses separated by a comma, for example -

 BEGIN ... email_list VARCHAR2(10000) := NULL; email_count NUMBER(10) := 0; FOR c_rec in CUR_person LOOP email_list := c_rec.CUR_person || ' , ' || email_list; email_count := email_count +1; if (email_count = 100) --100 email ids at a time then UTL_MAIL.send(sender => ' [e-mail] ', recipients => email_list, subject => 'Test Mail', message => 'Hello World', mime_type => 'text/html'); email_list := NULL; email_count := 0; end if; END LOOP ... END; /