Too many open connections on sending Email

I’m trying to send email to every users from DB. I use a loop to get the message text per user as I loop need to call the send_email.

But I got an error saying:

ORA-30678: too many open connections
ORA-06512: at "SYS.UTL_TCP", line 28
ORA-06512: at "SYS.UTL_TCP", line 257
ORA-06512: at "SYS.UTL_SMTP", line 115
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "PSPIWEB_USER.SENDEMAIL_HTML", line 12
ORA-06512: at line 10

I think the problem is that it’s trying to open a connection while it is still open.

What do I need to do to make this work?

Here’s my code:

begin

for cur_rec in (Select Distinct ISSUEDTO from IQA_CAR where to_char( TARGET_DATE,'YYYY-MM-DD') < to_char(sysdate,'YYYY-MM-DD') and CLOSEDDATE is null order by  ISSUEDTO)
loop

--dbms_output.put_line('prj_id: ' ||cur_rec.ISSUEDTO);


 --DBMS_OUTPUT.PUT_LINE('Result: ' || getCarbyIssueto_func (cur_rec.ISSUEDTO) );
 Sendemail_Html('[e-mail]','[e-mail]','Overdue CARs', getCarbyIssueto_func (cur_rec.ISSUEDTO) ,' ','92.xxx.xxx.xx','xx');

end loop;

end;

Sending email, I think this is where I need to modify or but I don’t know what.

PROCEDURE PSPIWEB_USER.Sendemail_Html (p_to        IN VARCHAR2,
                          p_from      IN VARCHAR2,
                          p_subject   IN VARCHAR2,
                          p_text_msg  IN VARCHAR2 DEFAULT NULL,
                          p_html_msg  IN VARCHAR2 DEFAULT NULL,
                          p_smtp_host IN VARCHAR2,
                          p_smtp_port IN NUMBER DEFAULT 25)
 AS
  l_mail_conn   UTL_SMTP.connection;
  l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';
 BEGIN
   l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
   UTL_SMTP.helo(l_mail_conn, p_smtp_host);
   UTL_SMTP.mail(l_mail_conn, p_from);
   UTL_SMTP.rcpt(l_mail_conn, p_to);

   UTL_SMTP.open_data(l_mail_conn);

   UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') || UTL_TCP.crlf);
   UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
   UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
   UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
   UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
   UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
   UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/alternative; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

   IF p_text_msg IS NOT NULL THEN
     UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
     UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

     UTL_SMTP.write_data(l_mail_conn, p_text_msg);
     UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
   END IF;

   IF p_html_msg IS NOT NULL THEN
     UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
     UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

     UTL_SMTP.write_data(l_mail_conn, p_html_msg);
     UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
   END IF;

   UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
   UTL_SMTP.close_data(l_mail_conn);

   UTL_SMTP.quit(l_mail_conn);

 END;

Answer

You can use BULK COLLECT. This will fetch all the email ids at once and save it in variable. Then you can process it one by one.

CREATE OR REPLACE 
function  SEND_MAIL(table_name in varchar2) return integer is PRAGMA AUTONOMOUS_TRANSACTION;
Begin
Declare
TYPE var_tab IS TABLE OF varchar2(200);
ISSUEDTO var_tab;
j integer;
begin
    execute immediate 'Select Distinct ISSUEDTO from '||table_name||' where to_char( TARGET_DATE,'||''''||'YYYY-MM-DD'||''''||') < to_char(sysdate,'||''''||'YYYY-MM-DD'||''''||') and CLOSEDDATE is null order by  ISSUEDTO' bulk collect into ISSUEDTO;
        for j in 1.. ISSUEDTO.count
        loop
            Sendemail_Html('[e-mail]','[e-mail]','Overdue CARs', getCarbyIssueto_func (ISSUEDTO(j)) ,' ','92.xxx.xxx.xx','xx');
            commit;
        END LOOP;
return 1;
end;
End;
/