Sending result of Procedure as the body of an email-CodePudding

I have a procedure which returns a set of rows. Similarly, I am using a procedure to send an email whose parameters is as follows : SEND_MAIL ( mail_to, mail_from, mail_subject, mail_body, host)

I have been trying to pass a variable which consists of the result from the DBMS_OUTPUT.PUT_LINE as mail subject in the SEND_MAIL procedure but I am only obtaining the last record of the result which indicates the variables only has the latest inserted value.

I want to receive the entire result in the mail body.

create OR REPLACE PROCEDURE EVAL_LOG AS
mail_body_v varchar2(4000);
BEGIN

FOR I IN ( select LOG.LOG_DATE,LOG.ACTION,LOG.OLD,LOG.NEW from
dw.EVALUATIONS_LOG LOG
where to_char(log_date,'DD-MON-YYYY')
in (select max(to_char(log_date,'DD-MON-YYYY')) from dw.EVALUATIONS_LOG))
loop mail_body_v:=
DBMS_OUTPUT.PUT_LINE (
  ( 'Date : ' || i.log_date
  
|| '  ACTION : ' || i.ACTION
|| '  OLD : ' || i.OLD
|| '  NEW : ' || i.NEW));

end loop;

SEND_MAIL('****@mailid.com',
    'Subject',mail_body_v,'****@mailid.com','host');
end;

Output:

Date : 16-AUG-22  ACTION : INSERT  OLD :   NEW : 2382382
Date : 16-AUG-22  ACTION : UPDATE  OLD : 175  NEW : 195
Date : 16-AUG-22  ACTION : INSERT  OLD :   NEW : 232323
Date : 16-AUG-22  ACTION : UPDATE  OLD : 195  NEW : 175
Date : 16-AUG-22  ACTION : UPDATE  OLD : 195  NEW : 175
Date : 16-AUG-22  ACTION : UPDATE  OLD : 175  NEW : 195
Date : 16-AUG-22  ACTION : DELETE  OLD : 232323  NEW :

CodePudding user response：

You are overwriting value of mail_body_v each time you enter loop, thats why only the last result is visible. Instead of assigning new value to the mail_body_v each time it enters the loop, you should add text to the old value.

So instead of: mail_body_v:= DBMS_OUTPUT...

you should do it like that: mail_body_v:= mail_body_v || DBMS_OUTPUT...

Your code, after required changes could look like:

CREATE OR REPLACE PROCEDURE EVAL_LOG AS
mail_body_v varchar2(4000);
BEGIN

FOR I IN ( select LOG.LOG_DATE,LOG.ACTION,LOG.OLD,LOG.NEW from
dw.EVALUATIONS_LOG LOG
where to_char(log_date,'DD-MON-YYYY')
in (select max(to_char(log_date,'DD-MON-YYYY')) from dw.EVALUATIONS_LOG))
loop 
mail_body_v:= mail_body_v ||
DBMS_OUTPUT.PUT_LINE (
  ( 'Date : ' || i.log_date
  
|| '  ACTION : ' || i.ACTION
|| '  OLD : ' || i.OLD
|| '  NEW : ' || i.NEW));

end loop;

SEND_MAIL('****@mailid.com',
    'Subject',mail_body_v,'****@mailid.com','host');
end;