5 Replies Latest reply: Oct 10, 2013 12:30 AM by Billy~Verreynne RSS

    Global variable

    Magneto

      Hi,

      I want use global variable which i would refer in more than one procedure of a pakage.

      I want to know the potential drawbacks associated with it for the following scenario.

      I have package

       

      CREATE OR REPLACE PACKAGE check_global_var  AS

           PROCEDURE procedure1;

           PROCEDURE procedure1;

           id1 VARCHAR2(100):=to_char(SYSDATE,'dd-mm-yy hh24:mi:ss');

           id2 VARCHAR2(100):=to_char(SYSDATE,'dd-mm-yy hh24:mi:ss');

      END;

       

       

      CREATE OR REPLACE PACKAGE BODY check_global_var   AS

      PROCEDURE procedure1 IS

        BEGIN   

               id1 :=to_char(SYSDATE,'dd-mm-yy hh24:mi:ss');

               dbms_output.put_line(ID1);

               id2 :=to_char(SYSDATE,'dd-mm-yy hh24:mi:ss');

        END;

      PROCEDURE procedure2 IS

        BEGIN

              dbms_output.put_line(id2);

         END;

      END;

       

      calling 1st proc:

      BEGIN

        check_global_var.procedure1 ;

      END;

       

      after 5 mins calling 2nd proc:

      BEGIN

        check_global_var.procedure2;

      END;

       

      Doubts:

      what would be id2 value when i call  procedure1?

      whether will it always have the value which is being set in procedure1 or will it have the globaly declared value

      i need always the value which is being set in procedure1

        • 2. Re: Global variable
          Pleiadian

          In your scenario id2 would have the value set in procedure 1, but only for the session that called procedure 1.

           

          Remember that package varables are session based.  This means that the id2 that you see can have a different value than the id2 that another user sees. Every session has its own copy of the variables.

          • 3. Re: Global variable
            rp0428

            I want use global variable which i would refer in more than one procedure of a pakage.

            I want to know the potential drawbacks associated with it for the following scenario.

             

            what would be id2 value when i call  procedure1?

            whether will it always have the value which is being set in procedure1 or will it have the globaly declared value

            i need always the value which is being set in procedure1

            A variable will have the LAST value assigned to it by the session.

             

            If you don't want the value to be changeable define a CONSTANT.

            • 4. Re: Global variable
              Magneto

              Hi

              My business requirement is ,

              Java api first calls procedure1 (where I'm holding  sysdate in a variable id1)

              again the api calls(say after 10 mins) procedure2 to insert the value in the variable id1 to a table

               

              lets say

               

              CREATE OR REPLACE PACKAGE check_global_var  AS

                   PROCEDURE procedure1;

                   PROCEDURE procedure1;

                   id1 VARCHAR2(100);

              END;

               

              CREATE OR REPLACE PACKAGE BODY check_global_var   AS

              PROCEDURE procedure1 IS

              begin

              id1:=sysdate; /*insert into emp (store_last_process_date) values(sysdate); commit;*/

              end;

               

              PROCEDURE procedure2 IS

              begin

              insert into emp (last_process_date) values(id1);/*update emp set last_process_date=store_last_process_date; commit;*/

              commit

              end;

              end;

               

              is it advisable to use global variable in this case or

              should i follow the approach which i have mentioned in comments.

              ( our concern is not to add new column store_last_process_date into emp table, that leads us to think of global variable)

               

              PS: API always call procedure1 first and then procedure2

              thanks,

              Sasidharan

              • 5. Re: Global variable
                Billy~Verreynne

                If the client code is Java, then using package state for  global/static session variables, is not a good idea.

                 

                Sessions are often reused in this fashion. This means that app1 can make a database call using connection1 (and Oracle session1), do some processing, and then make a database call using the current available/idle connection, which is connnection10 (and Oracle session10).

                 

                Package state set by app1 in its first database call will not be available in its second database call.

                 

                The basic architectural question to ask is whether your Java app tier is a stateful or stateless database client. Only stateful clients can rely on Oracle server session state (e.g. use pessimistic locking). Stateless clients (typical of  web client-server architecture) cannot use server session state and are required to resort to techniques like optimistic locking.

                 

                Another question to ask is why does Java app code need to maintain state in Oracle server sessions, and why not maintain state locally?