For loop sends email for each record

user12251389

I have below procedure which sends email for every KPI_NAME exist in the TRACK_KPI table

create or replace procedure execute_send_mail_smtp as beginFOR KPI_TRACK_ROW IN (Select KPI_NAME FROM RATOR_MONITORING.TRACK_KPI)LOOP  SEND_MAIL_SMTP(    '[email protected]',           --Sender    '[email protected]',           --Recipient    'TEST- KPI NOT RUNNING',                    --Subject    'Below KPIs are not running:' || KPI_TRACK_ROW.KPI_NAME  --Message  );END LOOP;end execute_send_mail_smtp;

This log is incorrect. I want to send in only one email all the KPI_NAME from TRACK_KPI table. The records should be formatted and for every records there should be new line in email. Also i want to check if there is no recors exist then it sends an email as 'the KPI does not exist'. I really wondered if i can do this in my procedure. I also want to send email to more than one recipient then is it possible to provide more than one recipient?

Saubhik

You need something like

CREATE OR REPLACE PROCEDURE execute_send_mail_smtp AS 

v_kpi varchar2(4000) :=' '; --assuming your kpi_names are not long.

begin

FOR KPI_TRACK_ROW IN (Select KPI_NAME FROM RATOR_MONITORING.TRACK_KPI)

LOOP

  v_kpi :=v_kpi||KPI_TRACK_RUNNING_ROW.KPI_NAME||chr(10);

END LOOP;

SEND_MAIL_SMTP(

    '[email protected]',           --Sender 

    '[email protected]',           --Recipient 

    'TEST- KPI NOT RUNNING',                    --Subject

    'Below KPIs are not running:' || v_kpi  --Message

  );

end execute_send_mail_smtp;

NOT TESTED.

Cookiemonster76

So loop over the table. Inside the loop build up a string of the text you want in the email.

After the loop check if the string is empty, if it is assign the not exists message

After that call the procedure to send the email.

user12251389

I tried but still had the same issue

Saubhik

user12251389 wrote:I tried but still had the same issue

What is the exact problem you are facing ?

John Stegeman

You tried and it didn't work? What does "didn't work" mean? What did you try - show us?

user12251389

I have currently 2 records in TRACK_KPI table. When i execute this procedure i am getting 2 emails one for each record. So for example if i have 100 records now in TRACK_KPI table then i will receive 100 emails for each records which i dont want. I want all this records from TRACK_KPI  table send in one email

John Stegeman

Yes, because you send the email in a loop. Don't do that. Instead, build the email content in the loop (hint: store it in a CLOB or varchar2 variable, depending on the potential size) and send the email outside the loop

user12251389

Here is what i have tried :FOR KPI_TRACK_RUNNING_ROW IN (Select KPI_NAME FROM RATOR_MONITORING.TRACK_KPI)LOOPsql='SEND_MAIL_SMTP(    ''[email protected]'',           --Sender    ''[email protected]'',           --Recipient    ''PREPROD - KPI NOT RUNNING'',                    --Subject    ''Below KPIs are not running:''' || KPI_TRACK_RUNNING_ROW.KPI_NAME  --Message  );execute immediate l_sql;END LOOP;

Saubhik

You need something like

CREATE OR REPLACE PROCEDURE execute_send_mail_smtp AS 

v_kpi varchar2(4000) :=' '; --assuming your kpi_names are not long.

begin

FOR KPI_TRACK_ROW IN (Select KPI_NAME FROM RATOR_MONITORING.TRACK_KPI)

LOOP

  v_kpi :=v_kpi||KPI_TRACK_RUNNING_ROW.KPI_NAME||chr(10);

END LOOP;

SEND_MAIL_SMTP(

    '[email protected]',           --Sender 

    '[email protected]',           --Recipient 

    'TEST- KPI NOT RUNNING',                    --Subject

    'Below KPIs are not running:' || v_kpi  --Message

  );

end execute_send_mail_smtp;

NOT TESTED.

user12251389

How can i build email content outside loop ? I have procedure SEND_MAIL_SMTP also which i cant take it in string ?

John Stegeman

send_mail_smtp takes a parameter. Saubhik gave you an example.