J'essaie d'envoyer un e-mail à tous les utilisateurs de DB. J'utilise une boucle pour obtenir le texte du message par utilisateur car j'ai besoin d'appeler le send_email.

Mais j'ai une erreur disant :

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

Je pense que le problème est qu'il essaie d'ouvrir une connexion alors qu'elle est encore ouverte.

Que dois-je faire pour que cela fonctionne?

Voici mon 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('sample@email.com','noreply@email.com','Overdue CARs', getCarbyIssueto_func (cur_rec.ISSUEDTO) ,' ','92.xxx.xxx.xx','xx');

end loop;

end;

Envoi d'email, je pense que c'est là que je dois modifier ou mais je ne sais pas quoi.

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;
0
Kiffer Van 7 févr. 2020 à 08:01

1 réponse

Meilleure réponse

Vous pouvez utiliser BULK COLLECT. Cela récupérera tous les identifiants de messagerie à la fois et les enregistrera dans une variable. Ensuite, vous pouvez les traiter un par un.

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('sample@email.com','noreply@email.com','Overdue CARs', getCarbyIssueto_func (ISSUEDTO(j)) ,' ','92.xxx.xxx.xx','xx');
            commit;
        END LOOP;
return 1;
end;
End;
/
1
Naveen Kumar 7 févr. 2020 à 05:51