SQL & PL/SQL

Hi All,

Below is a brief about my requirement,

We have a requirement that we need to send workflow notification reminders 3 times with prefix like

1st notification with prefix "REMINDER# 1"

2nd notification with prefix "REMINDER# 2"

3rd notification prefix "REMINDER#3 ", and these notifications will time out every 2 hrs.

so the logic should be handled in PLSQL, i am not sure how can i achieve this.

I have declared a variable and incremented the value by 1, we will execute the below block 3 times, the problem is all the time the value remains as 1 and lc_prefix is always  REMINDER#1 in all the notifications.

Please advise a solution for this.

when i execute the PLSQL block first time the value should be 1, if i execute the same block again the value should be 2 and 3 time the value should be 3.

DECLARE

ln_loop_count   NUMBER         := 0;

BEGIN

ln_loop_count=ln_loop_count+1

IF ln_loop_count = 1

      THEN

         lc_prefix := 'REMINDER#1:';

      ELSIF ln_loop_count = 2

      THEN

         lc_prefix := 'REMINDER#2:';

      ELSIF ln_loop_count = 3

      THEN

         lc_prefix := 'REMINDER#3:';

      ELSE

         lc_prefix := NULL;

      END IF;

END;

Thanks,

CSK