Forum Stats

  • 3,852,796 Users
  • 2,264,137 Discussions
  • 7,905,145 Comments

Discussions

For loop sends email for each record

user12251389
user12251389 Member Posts: 335 Blue Ribbon
edited Feb 22, 2017 7:55AM in SQL & PL/SQL

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?

AndrewSayerJohn Stegeman

Best Answer

  • Saubhik
    Saubhik Member Posts: 5,846 Gold Crown
    edited Feb 22, 2017 7:31AM Answer ✓

    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.

«1

Answers

This discussion has been closed.