Oracle package question. public variables.

[rzuniga]

Folks,

I have an interesting question regarding public variables. See the package declaration at the end of this message. In the create package declaration, I have a public variable called ag_count which is initialized to 0.
When I called the function testg.f1 from the SQL*Plus command line as below:

set serveroutput on
declare
result number :=0;
begin
result := testg.f1;
end;
/
run
I get the result for ag_count

ag_count :2
PL/SQL procedure successfully completed

Which is correct. However, if I type run a second time I get

 

[patch]

The beauty of package variables is that they stay available during your entire session. They get initialized when the package is first run.
So, it is normal Oracle behaviour that your variable gets updated and never initialized.

By the way: Package variables are great for caching data.

------------------
Check out: http://www.oracledeveloper.nl

 

[aotte]

So to follow up on Patch, the right way to get your variables re-initialized is to actually declare them in the function.

I personally somewhat of a purist on public package variables being a no-no. I would make it private and a have public function and procedure to change the value, and decide on a case by case basis whether the internal packages routines access it through the function/procedure or directly.

------------------
Hakuna Matata,

Arnoud.

 

[patch]

I agree with Arnoud on not using public package variables. The best way is to hide them in the body and provide a get function and a set procedure. This way you have more control on how the variables are being set.
For example, when your variable should only hold values between 0 and 100, then your set procedure could check the value before it is actually set. If you send in a value greater than 100, you decide either NOT to change the value, OR change it to the max allowed value.

------------------
Check out: http://www.oracledeveloper.nl