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.
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.
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
CREATE OR REPLACE PACKAGE check_global_var AS
CREATE OR REPLACE PACKAGE BODY check_global_var AS
PROCEDURE procedure1 IS
id1:=sysdate; /*insert into emp (store_last_process_date) values(sysdate); commit;*/
PROCEDURE procedure2 IS
insert into emp (last_process_date) values(id1);/*update emp set last_process_date=store_last_process_date; commit;*/
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
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?