4 Replies Latest reply: Jan 15, 2014 8:10 AM by Michael Ferrante-Oracle RSS

    Emails

    Moazam Shareef

      Guyz,

       

      I have one tubular form. where the below columns are exists.

       

      Empid, Emp_name, Division, Manager_name, mngr_email, emp_email,dis_date, dis_time, flag

       

       

      I fetch the records from multiple tables into the above table. and its working fine, and when i commit data the flag field become 1 by some calculation, and send mail to those employee and their manager where flag become 1

       

      but it will send the mail to only first record. it was not checking whole tubular block and commit the data.

       

      how can i check the tubular record before saving to send mail where status become 1 and when it complete the mail sending it commit the record into the table; how can i achieve this task?

      BEGIN
        IF :BLOCK2.DIFFERENCE='1' THEN
        UTL_MAIL.SEND(SENDER => :BLOCK2.SENDER,
        RECIPIENTS => :BLOCK2.MNGR_MAIL||','||:BLOCK2.EMP_EMAIL,
        CC => :BLOCK2.EMAIL1,
        BCC => :BLOCK2.EMAIL1,
        SUBJECT => :CTRL.FROM_dATE||' '||:CTRL.TO_DATE,
        MESSAGE => 'Subject Messages');
      ELSIF
      BLOCK2.DIFFERENCE='0' THEN
      NULL;
      END IF;
      END;
      
      

      im using the below proceddure to send utl_mail.

      form snap here.... FRM - Download - 4shared

      i'm using oracle 10g and forms 10g.

        • 1. Re: Emails
          Michael Ferrante-Oracle

          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...

          • 2. Re: Emails
            Moazam Shareef

            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 => 'mailalerts@xyz.com.com',
              RECIPIENTS => :BLOCK2.MNGR_MAIL||','||:BLOCK2.EMP_EMAIL,
              CC => 'xyz@gmail.com',
              BCC => 'xyz@gmail.com',
              SUBJECT => :CTRL.FROM_dATE||' '||:CTRL.TO_DATE,
              MESSAGE => 'Subject Mail');
              END IF;
              EXIT WHEN :SYSTEM.LAST_RECORD='TRUE';
              NEXT_RECORD;
              END LOOP;
            END;
            

             

            Regards

            • 3. Re: Emails
              Michael Ferrante-Oracle

              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. 

              • 4. Re: Emails
                Moazam Shareef

                Thanks Micheal,

                 

                Your both ideas are superb, but i will go with SYNCHRONIZE

                 

                Regards

                 

                Moazam