This discussion is archived
10 Replies Latest reply: Jul 30, 2013 11:16 AM by Lalit Kumar B RSS

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

CSK Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    Agree with you on Global context variable.

     

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points