Re: Question on Package Variables

On Jan 25, 12:45 am, sybra..._at_hccnet.nl wrote:

>
> There was nothing patronizing. You don't seem to understand the
> difference between a function and a procedure (looking at your code),
> so I explained the difference.
>
> --
> Sybrand Bakker
> Senior Oracle DBA

I'd have to disagree Sybrand. Galen never even had a function or procedure in his code. The package spec was as simple as they come; one package variable in the spec. His sql shows he was testing if a package variable can be directly accessed from SQL, which it can not.

The reason is the difference in plsql and sql contexts. You may have heard of context switches. That's what happens when you loop through a cursor in plsql or call a plsql function from within SQL. The code execution must leave the one context, perform it's task in the other and then return. Bulk collects are more efficient than looping through a cursor one by one since they minimize those context switches.

Once the processing is in one context it does not have access to the memory of the other context. Package variable values are unique to each session and are stored in the PGA memory area of each shadow process (for dedicated server processes). In the SQL context, which is shared by all sessions, a statement does not have direct access to any one session's memory, which is what accessing the variable is trying to do, i.e. directly read the value at that memory address. That is the reason SQL must use a function to get a session's plsql variable.
The benefit of encapsulation in a package is only effective if the variable is in the package body. Any variable in the spec, as Galen was testing, is effectively global and can be accessed and modified by any process that has access to the package.

Andrew Received on Fri Jan 25 2008 - 08:33:42 CST