For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
The oracle server with X5-2 S/N: 1539NML001has top fan indicator showing amber. Please advice,
Thnaks
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.
I tried but still had the same issue
user12251389 wrote:I tried but still had the same issue
user12251389 wrote:
What is the exact problem you are facing ?
You tried and it didn't work? What does "didn't work" mean? What did you try - show us?
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
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
Here is what i have tried :FOR KPI_TRACK_RUNNING_ROW IN (Select KPI_NAME FROM RATOR_MONITORING.TRACK_KPI)LOOPsql='SEND_MAIL_SMTP( ''support@leo'', --Sender ''r.d@leo.com'', --Recipient ''PREPROD - KPI NOT RUNNING'', --Subject ''Below KPIs are not running:''' || KPI_TRACK_RUNNING_ROW.KPI_NAME --Message );execute immediate l_sql;END LOOP;
Here is what i have tried :
FOR KPI_TRACK_RUNNING_ROW IN (Select KPI_NAME FROM RATOR_MONITORING.TRACK_KPI)
LOOP
sql='SEND_MAIL_SMTP(
''support@leo'', --Sender
''r.d@leo.com'', --Recipient
''PREPROD - KPI NOT RUNNING'', --Subject
''Below KPIs are not running:''' || KPI_TRACK_RUNNING_ROW.KPI_NAME --Message
);
execute immediate l_sql;
END LOOP;
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)
v_kpi :=v_kpi||KPI_TRACK_RUNNING_ROW.KPI_NAME||chr(10);
SEND_MAIL_SMTP(
'support@leo.com', --Sender
'r.d@leo.com', --Recipient
'TEST- KPI NOT RUNNING', --Subject
'Below KPIs are not running:' || v_kpi --Message
end execute_send_mail_smtp;
NOT TESTED.
How can i build email content outside loop ? I have procedure SEND_MAIL_SMTP also which i cant take it in string ?
send_mail_smtp takes a parameter. Saubhik gave you an example.
I tried your logic it sends email without any data and when i replace
v_kpi := v_kpi||chr(10);
as
v_kpi :=KPI_TRACK_ROW.KPI_NAME||chr(10);
in your logic then it sends an email with only one record but there are two records exist in my table
Think about it...
Why did you replace Saubhik's code, which looked correct with yours, which is obviously not?
I have edited that portion, but this is not a code writing service and as a professional you should take the idea and write, debug yourself.
Now it works thanks but just a small question can i use IF condition in this logic ? Because if there is no records exist in the TRACK_KPI table then its not sending any emai. So if there is nor record exist then i just want to send email as 'NO KPI Tracked'
and just noticed... why the heck are you using execute immediate to call a stored procedure from PL/SQL? There's no need for that
You can use any PL/SQL construct you like, including "IF"
Yes it was mistake i copy paste and didn't realize
I have tried with IF logic and it works now. Just a small question can we send email to more than one Recipient ? When i tried to enter 2 Recipient then it gaves me error as
ora-29279 smtp permanent error 550 user unknown
Talk to whomever wrote send_mail_smtp, or look at the code yourself and figure it out. Of course SMTP can send mail to more than one recipient, if you do it correctly.