Home » SQL & PL/SQL » SQL & PL/SQL » Defining Variables in the Package Spec (Oracle, 12.2, Unix)
Defining Variables in the Package Spec [message #685433] Thu, 06 January 2022 12:03 Go to next message
Duane
Messages: 462
Registered: December 2002
Senior Member
I think I know this question but I thought I had better ask.

CREATE OR REPLACE package Common as

  LineNumber number default 6;

end Common;
CREATE OR REPLACE package body Test as

  procedure Test1 is

    begin
      common.LineNumber := common.LineNumber + 1;
    end;

  end Test1;


  procedure Test2 is

    begin
      common.LineNumber := common.LineNumber * 3;
      .
      .
      .
      common.LineNumber := common.LineNumber + 1;
    end;

  end Test2;


end Test;
If Test1 and Test2 are run close to one another then the common.LineNumber variable would be shared between the two procedures and colliding and not producing the numbers that each procedure would produce if the variable was defined within the procedure.

Basically, the common.LineNumber variable would be unpredictable if two or more procedures were running and modifying the variable at the same time.

Just want to confirm my thoughts.
Re: Defining Variables in the Package Spec [message #685434 is a reply to message #685433] Thu, 06 January 2022 13:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68078
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Package variables are private to a session, PL/SQL is sequential and no procedures are called at the same time in a session, so the variable always has a predictable value given the sequence of calls you make.

Re: Defining Variables in the Package Spec [message #685435 is a reply to message #685434] Thu, 06 January 2022 14:29 Go to previous messageGo to next message
Duane
Messages: 462
Registered: December 2002
Senior Member
Ah, thank you.

I actually thought that was the case but I started to have my doubts so I thought the variable would be colliding when multiple procedures were accessing the same variable. That's not what I wanted.

I want each procedure to have it's own reference to that variable when it's called. If I have ten procedures running at around the same time then I want ten separate variables being used by those ten procedures.
Re: Defining Variables in the Package Spec [message #685439 is a reply to message #685435] Fri, 07 January 2022 06:36 Go to previous message
Frank
Messages: 7885
Registered: March 2000
Senior Member
Be careful though if you would use any form of connection pooling. In such a case, package state would be shared amongst different (middle-tier) sessions.
Previous Topic: Query Performance
Next Topic: Oracle generate from-to DATE ranges and group by
Goto Forum:
  


Current Time: Mon Jan 17 11:16:21 CST 2022