Forum Stats

  • 3,767,978 Users
  • 2,252,735 Discussions
  • 7,874,399 Comments

Discussions

Email sending wrong information

bigmac704
bigmac704 Member Posts: 101 Bronze Badge

Hello

I am trying to send out an email that includes specific information for each individual user that is to be emailed. Problem I am having is that it sends out the emails to each person listed but keeps all the all the specific detail that is in the first record in the data list for all the emails that go out. Each person that is emailed should have their on specific detailed information listed.

Thank in advanced.

declare 

 l_body clob := q'{  

Hello %user,

This notification, is to let you know that you have been assinged Gate Duty for %sport by your Athletic Director.

Game Date: %date

Game Time: %time

Location/School: %loc

You will receive reminders as this date gets closer.

Thank You!

***Please DO NOT REPLY to this Message***

    }'; 

begin  

 for c1 in (

SELECT A.* FROM (

    SELECT distinct NAME, GAME_DATE, LOCATION, EMAIL, TIME, SPORT

   ,(GAME_DATE - TRUNC(SYSDATE)) chk 

     FROM ATHLETICS_GATE_DUTY  

) A

WHERE round(A.CHK,0) in (0,1)

      )  

 loop  

 if c1.EMAIL is not null then  

 l_body := replace(l_body, '%user', c1.NAME);

 l_body := replace(l_body, '%date', c1.GAME_DATE);

 l_body := replace(l_body, '%time', c1.TIME);

 l_body := replace(l_body, '%loc', c1.LOCATION);

l_body := replace(l_body, '%sport', c1.SPORT);

  htmldb_mail.send(  

   p_to => c1.EMAIL,  

   p_from => '[email protected]',  

   p_body => l_body,  

   p_subj => 'Gate Duty Reminder');  

  end if;  

 end loop;  

end;

Comments

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,904 Red Diamond
    edited Oct 18, 2021 2:37PM

    Isn't it obvious? First iteration of loop replaces %user, %date, %time, %loc, %sport with first user info. Second, third,... iterations will not find %user, %date, %time, %loc, %sport anymore. Move l_clob initialization inside the loop:

    declare
    l_body clob; 
    begin  
     for c1 in (
    SELECT A.* FROM (
        SELECT distinct NAME, GAME_DATE, LOCATION, EMAIL, TIME, SPORT
       ,(GAME_DATE - TRUNC(SYSDATE)) chk 
         FROM ATHLETICS_GATE_DUTY  
    ) A
    WHERE round(A.CHK,0) in (0,1)
          )  
     loop  
     if c1.EMAIL is not null then  
    l_body := q'{  
    Hello %user,
    This notification, is to let you know that you have been assinged Gate Duty for %sport by your Athletic Director.
    Game Date: %date
    Game Time: %time
    Location/School: %loc
    You will receive reminders as this date gets closer.
    Thank You!
    ***Please DO NOT REPLY to this Message***
        }';
     l_body := replace(l_body, '%user', c1.NAME);
     l_body := replace(l_body, '%date', c1.GAME_DATE);
     l_body := replace(l_body, '%time', c1.TIME);
     l_body := replace(l_body, '%loc', c1.LOCATION);
     l_body := replace(l_body, '%sport', c1.SPORT);
      htmldb_mail.send(  
       p_to => c1.EMAIL,  
       p_from => '[email protected]',  
       p_body => l_body,  
       p_subj => 'Gate Duty Reminder');  
      end if;  
     end loop;  
    end;
    

    SY.

  • cormaco
    cormaco Member Posts: 1,721 Bronze Crown

    You are overwriting the textmarkers in your original template, so it doesn't work anymore for all other users after the first. You have to make a copy of the template and make the substitutions there.

    loop
     l_user := l_body;
     ...
     l_user := replace(l_user, '%user', c1.NAME);
     ...
    end loop;
    
    
  • bigmac704
    bigmac704 Member Posts: 101 Bronze Badge

    Thank You both.

    That makes since now. It is working..