Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003

    Unanswered: global variables

    Hi all

    i want to know is using global package variables advisable or not.

    i have a package say pkg_test

    with a global datatype

    with 2 procs

    proc1 & proc2

    in proc1 i fill the array &

    using forall, update the status col in the table to disabled state with the array in where clause

    the array will have the state as enable
    i want to preserve the initial state somewhere

    now in proc2
    i want to update the status of the table bak to the 1 i had stored in the array(globl package variable)

    now suppose this code is being called by say n number of clients
    each will have its own set of values for the status columns

    will this have any repercussions


  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1

    Re: global variables

    There is no particular issue with the global package variables; as you say, each session will have its own set of values for the global variables (its own "package state").

    The potential issue is that 2 sessions may both try to select/update the same ACCOUNT record(s). Session A could select accounts 101, 102 and 103 and update the status to disabled in proc1; then session B could select accounts 103, 104 and 105 and try to update the status to disabled, but it will be blocked by session A which has already locked account 103. Once session A has completed and committed, B can continue. Now, is it right for B to continue process account 103, given that session A just processed it? If it is, fine. If not, then the initial SELECT in proc1 should use FOR UPDATE so that it locks the accounts. This would prevent session B from obtaining a list of accounts to process while session A was working. Once session A finished, session B would be able to start - taking into account any changes made by session A.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts