Home » SQL & PL/SQL » SQL & PL/SQL » Global variables in a package (concurrency question)
Global variables in a package (concurrency question) [message #30419] Thu, 29 April 2004 13:38 Go to next message
Jaime Stuardo
Messages: 57
Registered: March 2004
Member
Hi all...

If I have a global variable defined in a package. That global variable is set by some procedure inside the package body.

The question is about concurrency. Suppose some client executes that package and modifies the values of the global variables. If another client executes the same package concurrently, does he keep his own copy of the variables? or the modifications made by the second client will affect the first one?

Thanks
Jaime
Re: Global variables in a package (concurrency question) [message #30422 is a reply to message #30419] Thu, 29 April 2004 14:14 Go to previous message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Each session gets its own copy of the global variables.

You can test this yourself.

Open a SQL*Plus session, call it Session A, and enter the following:
<font color=#660033>SQL> SET SERVEROUTPUT ON
SQL> CREATE OR REPLACE PACKAGE pkg
  2  AS
  3      g_global_value       VARCHAR2(1);
  4      PROCEDURE p (p_param IN VARCHAR2);
  5  END pkg;
  6  /
 
Package created.
 
SQL> CREATE OR REPLACE PACKAGE BODY pkg
  2  AS
  3      PROCEDURE p (p_param IN VARCHAR2)
  4      IS
  5      BEGIN
  6          g_global_value := SUBSTR(p_param,1,1);
  7      END p;
  8  END pkg;
  9  /
 
Package body created.
 
SQL> EXEC pkg.p('A');
 
PL/SQL procedure successfully completed.
 
SQL></font>
Open a second SQL*Plus session, call it Session B, and issue the following:
<font color=#0000cc>SQL> SET SERVEROUTPUT ON
SQL> EXEC pkg.p('B');
 
PL/SQL procedure successfully completed.
 
SQL></font>
Now, when you go back to Session A and type
<font color=#660033>SQL> EXEC DBMS_OUTPUT.PUT_LINE(pkg.g_global_value);</font>
what do you get back? If the global variable were to be applied to all sessions, then the result of the above line typed in Session A would return B. If the global variable were session-specific, OTOH, then the result of the above line typed in Session A would return A.
<font color=#660033>SQL> EXEC DBMS_OUTPUT.PUT_LINE(pkg.g_global_value);
A
 
PL/SQL procedure successfully completed.
 
SQL></font>
According to the documentation,
----------------------------------------------------------------------
The package spec contains public declarations. The scope of these declarations is local to your database schema and global to the package.

----------------------------------------------------------------------
And from here,
----------------------------------------------------------------------
[[A]]ny variable defined outside of a procedure/function is a global variable and maintains its state for the duration of the session.

----------------------------------------------------------------------

HTH,

Art.
Previous Topic: Bug? Syntax Error?
Next Topic: Reg Query
Goto Forum:
  


Current Time: Fri May 16 11:59:30 CDT 2025