Thanks for the question, Mohammed Amine.
Asked: April 04, 2016 - 5:12 pm UTC
Last updated: April 05, 2016 - 3:28 am UTC
Version: 12.1
Viewed 1000+ times
You Asked
Hello,
I can't find any explanation to some "weird" behavior, in some cases (quite rare), a global variable that I declared in my PACKAGE BODY get nullified ... after I initialize it.
The problem is that I can't reproduce the problem in My Dev database, and it happens once in while in client's DB.
The code is quite simple, and the variable in question is referenced only three times: Initialization and used twice.
Any help would be appreciated!
regards,
Amine
and Connor said...
Yup...easy
SQL> create or replace
2 package PKG is
3 var int;
4 end;
5 /
Package created.
SQL> exec pkg.var := 10;
PL/SQL procedure successfully completed.
SQL>
SQL> set serverout on
SQL> exec dbms_output.put_line(pkg.var);
10
PL/SQL procedure successfully completed.
SQL>
SQL> exec dbms_session.reset_package;
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> set serverout on
SQL> exec dbms_output.put_line(pkg.var);
PL/SQL procedure successfully completed.
<code>
Now...you're probably thinking "But I dont call that DBMS_SESSION routine".
This might be the case, but things like connection pool management, or session sharing, or web page app servers, often do this because of the fact they are stateless.
Similarly, if anything is causing your package to need recompilation, eg
<code>
SQL> exec pkg.var := 10;
PL/SQL procedure successfully completed.
SQL> alter package pkg compile;
Package altered.
SQL> set serverout on
SQL> exec dbms_output.put_line(pkg.var);
PL/SQL procedure successfully completed.
Hope this helps.