10 Replies Latest reply: Jul 30, 2013 1:16 PM by Lalit Kumar B RSS

    How to Increment a Value by 1 in each execution of a Package

    CSK

      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

        • 1. Re: How to Increment a Value by 1 in each execution of a Package
          Tubby

          CSK wrote:

           

          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

          You are't going to be able to do this with PLSQL alone (unless you involved something like Advanced Queueing perhaps).

           

          One option would be to create a table to track the reminders, and write some PLSQL to manage that entity (you would need proper concurrency controls to ensure 2 sessions can't concurrently step on each others' toes). For the concurrency controls I would recommend looking at the DMBS_LOCK package.

           

          Cheers,

          • 2. Re: How to Increment a Value by 1 in each execution of a Package
            CSK

            Tubby,

             

            Thanks for your quick response on this.

             

            Even though if we create a table with 3 values as REMINDER1,REMINDER2, REMINDER3, how can i handle it in my package like first time it should fetch first row and when executing the same package second time it should fetch second row.

             

            Can you please explain more about your solution, sorry i am new to PLSQL.

             

             

            Thanks in advance

            CSK

            • 3. Re: How to Increment a Value by 1 in each execution of a Package
              Frank Kulash

              Hi,

              CSK wrote:

               

              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

              Are you sure this is the code you're running?  It looks like it would raise an error because you're using =  where := is required:

               

              ln_loop_count=ln_loop_count+1

               

               

              The answer to your question is in the title of your thread.  You want a package, not an anonymous block.  An anonymous block is compiled anew every time you run it.  A package can contain variables (such as ln_loop_count in the example below) that last as long as your database session lasts.

               

              Here's one way to do what you requested:

               

              CREATE OR REPLACE PACKAGE  pkg_lc
              AS
                  FUNCTION get_lc_prefix
                  RETURN VARCHAR2;
              END   pkg_lc;
              /
              SHOW ERRORS

               

               

               

              CREATE OR REPLACE PACKAGE BODY  pkg_lc
              AS
                  ln_loop_count PLS_INTEGER := 0;

               

                  FUNCTION get_lc_prefix
                  RETURN VARCHAR2
                  IS
                  BEGIN
                      ln_loop_count := ln_loop_count + 1;

                      RETURN  'REMINDER#' || ln_loop_count;
                  END  get_lc_prefix;

               

               

              END  pkg_lc;
              /
              SHOW ERRORS

               

              You can call the function in PL/SQL like this:

               

              str := pkg_lc.get_lc_prefix;

               

              or in SQL like this:

               

              SELECT  pkg_lc.get_lc_prefix

              FROM    dual;

               

              The package name is optional  when you call the function from inside the package itself.

               

              You may want (or need) to store the counter in a table, rather than in a package variable.

              • 4. Re: How to Increment a Value by 1 in each execution of a Package
                rp0428

                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.

                Clarify your requirement. In the above quote does 'i' mean a single session? That is do you mean that the first time a session executes the block you want 1 and the second time that SAME session executes the block you want 2?

                 

                If that is what you mean then declare a package variable to track the executions of the block. Initialize the variable to 0 and in the PL/SQL block increment the variable and then test it for 1, 2 or 3 and do something appropriate.

                 

                Or do you mean that the 'i' means ANY SESSION: if ANY session executes the block the number should increment?

                 

                If THAT is what you mean then create a global CONTEXT variable and increment it when the PL/SQL block is executed.

                • 5. Re: How to Increment a Value by 1 in each execution of a Package
                  Lalit Kumar B

                  1. In the subject you mentioned a package, however, you gave an example of an anonymous block.

                  2. You can never achieve this by using a variable. A variable could be global in the package or local to any of the sub-routine. You cannot refer it once the session ends.

                  3. One straight way(rather the best way) is to maintain an audit table, and you insert a record somewhere in the package. So that each time you call it, any one of the subprogram would do the insert.

                  4. Few odd ways are also there, I won't ever do that, but you could give it a try in Development environment and test. Keep a unique sql in the package, say you do a select 'This sql is to capture EXECUTIONS_TOTAL from the sqlstat and snapshot view' from dual;

                  Capture the sql_id for this sql. And then you can find the number of executions for the sql_id each time the package was called. The view dba_hist_sqlstat uses the V$SQL stats and delta info from snapshot. A very vague way to achieve your task though.

                   

                   

                  SELECT SUM(EXECUTIONS_TOTAL) 
                   FROM dba_hist_sqlstat WHERE sql_id='<sql_id>';
                  
                  
                  • 6. Re: How to Increment a Value by 1 in each execution of a Package
                    rp0428

                    2. You can never achieve this by using a variable. A variable could be global in the package or local to any of the sub-routine. You cannot refer it once the session ends.

                    Sure you can - you use a global CONTEXT variable as I mentioned above. Not only your user, but ANY user can access it.

                     

                    See 'Using Global Appication Contexts' in the Database Security Guide

                    http://docs.oracle.com/cd/B28359_01/network.111/b28531/app_context.htm#CIHFJHCG

                     

                     

                    About Global Application Contexts

                    A global application context enables application context values to be accessible across database sessions. Oracle Database stores the global application context information in the System (sometimes called "Shared") Global Area (SGA) so that it can be used for applications that use a sessionless model, such as middle-tier applications in a three-tiered architecture. These applications cannot use a session-based application context because users authenticate to the application, and then it typically connects to the database as a single identity. Oracle Database initializes the global application context once, rather than for each user session. This improves performance, because connections are reused from a connection pool.

                    There are three general uses for global application contexts:

                     

                    • 7. Re: How to Increment a Value by 1 in each execution of a Package
                      Billy~Verreynne

                      Here is an example of use standard Oracle concurrency (row/transaction locks) to create a PL/SQL counter.

                       

                      Keep in mind that an Oracle sequence is a far better option. And with Oracle 12c one can use auto incrementing columns.

                       

                      SQL> -- table to keep package counters (I would prefer instead
                      SQL> -- a single sequence)
                      SQL> create table pl_counters(
                        2          name    varchar2(30) not null,
                        3          counter number not null,
                        4          constraint pk_counters
                        5          primary key( name, counter )
                        6  ) organization index;
                      
                      Table created.
                      
                      SQL>  -- function that supplies reminder counter - parameter in a package should be
                      SQL> -- $$PLSQL_UNIT
                      SQL> create or replace function GetReminderCount( counterName varchar2 default 'MYPACKAGE' ) return varchar2 is
                        2          pragma autonomous_transaction;
                        3          cnt     number;
                        4  begin
                        5          update pl_counters
                        6          set     counter = counter + 1
                        7          where   name = counterName
                        8          returning counter into cnt;
                        9  
                       10          if SQL%RowCount = 0 then
                       11                  cnt := 1;
                       12                  insert into pl_counters values(
                       13                          counterName, cnt
                       14                  );
                       15          end if;
                       16  
                       17          commit;
                       18          return(
                       19                  'REMINDER#'||trim(to_char(cnt))
                       20          );
                       21  end;
                       22  /
                      
                      Function created.
                      
                      

                       

                       

                      The unit test function:

                      SQL> -- unit test function - we want to run 2 or more of these at the same time and compare results
                      SQL> create or replace function TestCount( maxCount integer default 10 ) return TStrings pipelined is
                        2  begin
                        3          for i in 1..maxCount loop
                        4                  dbms_lock.sleep(1);
                        5                  pipe row( to_char(sysdate,'mi:ss')||' '||GetReminderCount );
                        6          end loop;
                        7  end;
                        8  /
                      
                      Function created.
                      
                      SQL>
                      
                      

                       

                      Now using KDE's konsole and redirecting keyboard input to both consoles (running sqlplus), enables us to enter and execute this SQL at the exact same time in both sessions:

                      SQL> -- session 1
                      SQL> select column_value as REMINDER from table(TestCount);
                      
                      REMINDER
                      ------------------------------
                      23:40 REMINDER#2
                      23:41 REMINDER#4
                      23:42 REMINDER#6
                      23:43 REMINDER#8
                      23:44 REMINDER#10
                      23:45 REMINDER#12
                      23:46 REMINDER#14
                      23:47 REMINDER#16
                      23:48 REMINDER#18
                      23:49 REMINDER#20
                      
                      10 rows selected.
                      ....
                      .....
                      SQL> -- session 2 (executed at the exact same time)
                      SQL> select column_value as REMINDER from table(TestCount);
                      
                      REMINDER
                      ------------------------------
                      23:40 REMINDER#1
                      23:41 REMINDER#3
                      23:42 REMINDER#5
                      23:43 REMINDER#7
                      23:44 REMINDER#9
                      23:45 REMINDER#11
                      23:46 REMINDER#13
                      23:47 REMINDER#15
                      23:48 REMINDER#17
                      23:49 REMINDER#19
                      
                      10 rows selected.
                      
                      

                       

                      The point of this example is that one does not always need to resort to customised methods to obtain concurrent access in PL/SQL to a serialised resource - like using DBMS_LOCK for example. Oracle has a very well designed row lock/concurrency system for the database, that can fairly easily be used for PL/SQL too.

                      • 8. Re: How to Increment a Value by 1 in each execution of a Package
                        CSK

                        Hi Everyone,

                         

                        Many Thanks for all your responses, It was very useful.

                        I have managed to get the reminders using the below code and it is working

                         

                        BEGIN

                                 BEGIN

                                    ln_loop_count :=

                                       wf_engine.getitemattrnumber (p_item_type,

                                                                    p_item_key,

                                                                    'INQ_LOOP_COUNT'

                                                                   );

                         

                                   EXCEPTION

                                    WHEN OTHERS

                                    THEN

                                       IF (wf_core.error_name = 'WFENG_ITEM_ATTR')

                                       THEN

                                          wf_engine.additemattr (p_item_type,

                                                                 p_item_key,

                                                                 'INQ_LOOP_COUNT'

                                                                );

                                          ln_loop_count := 0;

                         

                                       ELSE

                                          RAISE;

                                       END IF;

                                 END;

                         

                                 ln_loop_count := ln_loop_count + 1;

                         

                                --

                                 wf_engine.setitemattrnumber (p_item_type,

                                                              p_item_key,

                                                              'INQ_LOOP_COUNT',

                                                              ln_loop_count

                                                             );

                         

                                 --

                             EXCEPTION

                                 WHEN OTHERS

                                 THEN

                                    lc_error_msg := SQLERRM;

                                    wf_core.CONTEXT ('LOOPCOUNTER', lc_error_msg);

                                    RAISE;

                              END;

                         

                         

                        Thanks again

                         

                        Thanks,

                        CSK

                        • 9. Re: How to Increment a Value by 1 in each execution of a Package
                          CSK

                          Sorry Missed to add the below,

                           

                          BEGIN

                                   BEGIN

                                      ln_loop_count :=

                                         wf_engine.getitemattrnumber (p_item_type,

                                                                      p_item_key,

                                                                      'INQ_LOOP_COUNT'

                                                                     );

                           

                                     EXCEPTION

                                      WHEN OTHERS

                                      THEN

                                         IF (wf_core.error_name = 'WFENG_ITEM_ATTR')

                                         THEN

                                            wf_engine.additemattr (p_item_type,

                                                                   p_item_key,

                                                                   'INQ_LOOP_COUNT'

                                                                  );

                                            ln_loop_count := 0;

                           

                                         ELSE

                                            RAISE;

                                         END IF;

                                   END;

                           

                                   ln_loop_count := ln_loop_count + 1;

                           

                                  --

                                   wf_engine.setitemattrnumber (p_item_type,

                                                                p_item_key,

                                                                'INQ_LOOP_COUNT',

                                                                ln_loop_count

                                                               );

                           

                                   --

                               EXCEPTION

                                   WHEN OTHERS

                                   THEN

                                      lc_error_msg := SQLERRM;

                                      wf_core.CONTEXT ('LOOPCOUNTER', lc_error_msg);

                                      RAISE;

                                END;

                           

                                IF ln_loop_count = 2

                                THEN

                                   lc_prefix := 'REMINDER#1:';

                                ELSIF ln_loop_count = 4

                                THEN

                                   lc_prefix := 'REMINDER#2:';

                                ELSIF ln_loop_count = 6

                                THEN

                                   lc_prefix := 'REMINDER#3:';

                                ELSE

                                   lc_prefix := NULL;

                                END IF;

                           

                           

                          Thanks,

                          CSK

                          • 10. Re: How to Increment a Value by 1 in each execution of a Package
                            Lalit Kumar B

                            Agree with you on Global context variable.

                             

                            I meant it's impossible with local variables whose values are held by PGA.