This content has been marked as final. Show 2 replies
ODP.NET enables connection pooling by default, so when you close/dispose a connection it's simply returned back to the pool. This can cause undesirable behavior for things like package state, temporary tables, connections that had ALTER SESSION issued on them, etc. You can never be sure which connection a given con.Open call will give you, or whether it's a brand new connection (if the pool needed to have some added).
You could turn off connection pooling and always get a squeaky clean connection, but that has a fairly large impact.
There's no setting I know of or way to keep that from happening. The only suggestion I have is to add some initialize logic in your code to reset the package variable, either after a con.Open call, or before con.Dispose.
Hope it helps
Been struggling with this same problem for about a week.
Execute DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.REINITIALIZE) BEFORE each SQL called.
Use of connection pools with ODP has resulted in a session aware connection in a perceived stateless web environment.
(How’s that for cool. We have session retained without a state server! Who said the web is stateless? :D)
Fundamental problem(s) identified:
Public variables of packages called (globals), when used in a connection pooled environment, persist when the application closes the connection. This is due to the fact that the connection is not really closed; the pool maintains the connection and therefore session. Subsequent calls by an application using the same pool of connections may result in use of a connection which already opened an instance of the package. This new application connection will have available to it all the previous application connections objects and variable values; weather we want them or not. This is true even if the connection has timed out due to how pooling logic works. [http://rainmanalex.blogspot.com/2008/11/connection-pooling-issues-with-oracle.html]
Additionally, as the package already exists in memory tied to the connection which has remained open due to pooling, the initialization part of a package is not executed again.
This link [http://awads.net/wp/2007/04/04/here-is-how-to-unpersist-your-persistent-plsql-package-data/]is a great resource identifying the problem and viable options. These should be reviewed with a team to discuss the best option for your environment.
1. set packages to be Serially_reusable
2. update packages to prevent use of public package variables
3. use Execute DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.REINITIALIZE) BEFORE each SQL called in DataLogic class
4. Others (See link above in details)
In my opinion options 1 & 2 above are simpler, by default making them better options. Option 3 involves more overhead as it requires additional communication from the web server to the database. However, options 1 and 2 may not be deemed viable in the your environment.
• What's the network overhead of the option?
• What's the database server memory overhead of the option?
• What's the overall overhead to maintainability?
• What's the impact to end users in a multi-user environment?
• Does the option negate the benefit of connection pooling?
• What packages become serially reusable and which are not?
• IIS integration with Connection pooling…
Some links which helped me.
ORA-04068: existing state of packages has been discarded
Tom's article says don't use pools with global variables.
awads.net provides a good synopsis of the entire situation and possible work arounds
the other articles describe pitfalls and problems with what your looking into.
Edited by: 856054 on May 2, 2011 11:29 AM