2 Replies Latest reply: May 2, 2011 1:33 PM by 859057 RSS

    ODP and package global variables

    766743
      Has anyone had problems with global variables defined in a package keeping data across sessions? We have a global variable defined in a package and it is making a function call to get user information. The variable is then used in multiple procedures within the package. The problem we are seeing is the information in the variable seems to be cached and does not always get reset. This causes our data to have the wrong user information on who made the change.

      We are using connection pooling and what our DBA's suspect is happening, is the global variable gets set and is cached by the session. Next time a connection is made, if it reuses an existing connection, the global variable pulls from the cache instead of getting re-initialized.

      Is there any way in the ODP connection string to force the session information to get reset or any settings that could be done on the database side?

      Thanks!
      Ryan
        • 1. Re: ODP and package global variables
          gdarling - oracle
          Hi,

          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
          Greg
          • 2. Re: ODP and package global variables
            859057
            Been struggling with this same problem for about a week.

            Execute DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.REINITIALIZE) BEFORE each SQL called.

            SUMMARY:
            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.

            DETAILS:
            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.

            Options:
            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.

            Evaluation Factors:
            •     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…
            o     http://www.dbforums.com/oracle/1212475-odp-net-connection-pool-problem-web-application.html


            Some links which helped me.
            http://awads.net/wp/2007/04/04/here-is-how-to-unpersist-your-persistent-plsql-package-data/
            http://rainmanalex.blogspot.com/2008/11/connection-pooling-issues-with-oracle.html
            http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10795/adfns_pc.htm#1008344
            http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5809900747879
            ORA-04068: existing state of packages has been discarded
            http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sessio.htm

            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