You either need to make an internal list of each record that becomes flagged or loop back through all the records when you are ready to send. The first record is the only one sent because you didn't move to NEXT_RECORD to check its status and so on...
Thanks for your reply micheal,
i did with the below loop without commit it is checking all the record but not sending mail. anything wrong with my code?
BEGIN GO_BLOCK('BLOCK2'); FIRST_RECORD; LOOP IF :BLOCK2.EVENT_DATE BETWEEN :CTRL.DT1 AND :CTRL.DT1 AND :BLOCK2.DIFFERENCE='1' THEN UTL_MAIL.SEND(SENDER => 'email@example.com', RECIPIENTS => :BLOCK2.MNGR_MAIL||','||:BLOCK2.EMP_EMAIL, CC => 'firstname.lastname@example.org', BCC => 'email@example.com', SUBJECT => :CTRL.FROM_dATE||' '||:CTRL.TO_DATE, MESSAGE => 'Subject Mail'); END IF; EXIT WHEN :SYSTEM.LAST_RECORD='TRUE'; NEXT_RECORD; END LOOP; END;
For testing purposes, add SYNCHRONIZE immediately after LOOP (line 04 in your code above). If this corrects the problem then you may need to add some less expensive way of updating the server side. Sometimes a dummy counter like x := x+1 will fix the problem. But, I would try SYNCHRONIZE first. Keep in mind that using this in production may not be a great idea if the number of records is significant. However, if there are only a few records then using the SYNCHRONIZE should be ok.
Your both ideas are superb, but i will go with SYNCHRONIZE