title: ODP.net Connection Pooling: ClientID, Client Identifier never changes from first user who logs in
body: <p><strong>Scenario</strong>: We have an application that is using Oracle 10g and the latest version of ODP.net within an ASP.net application. We are utilizing the .ClientID WriteOnly property on the OracleConnection object to pass in a specific UserID to the database for auditing purposes. When Connection Pooling is disabled, this works perfectly. </p>
  
  <p>When it is enabled, the first person who logs in (ex: USER1) updates a record and the MODIFIED_BY is USER1, but when a different user heads into the website after, thus grabbing the pooled connection, the MODIFIED_BY is still USER1 despite passing in USER2 to the ClientID.</p>
  
  <p>Our database logic is as follows:</p>
  
  <p>We persist a class in an ASP.net session that has our database connection logic in it. On the initial call, this is our constructor:</p>
  
  <pre><code>Public Sub New(ByVal connection As String, Optional ByVal oracleClientID As String = "")
          MyBase.New()
          _oracleConnection = New OracleConnection(connection)
          _clientID = oracleClientID
          End If
      End Sub
  </code></pre>
  
  <p>Here’s the gist of the code to open connection and close, dispose:</p>
  
  <pre><code>Try
      _OraCmd = New OracleCommand(command, _oracleConnection)
      With _OraCmd
          .BindByName = True
          .Parameters.Clear()
          .CommandType = CommandType.StoredProcedure
          _oracleConnection.Open()
              If _clientID &lt;&gt; "" Then _oracleConnection.ClientId = _clientID
          Dim OraDadpt As New OracleDataAdapter(_OraCmd)
              '' Logic to get data
          OraDadpt.Fill(ds)
      End With
  Catch ex As Exception
      Throw ex
  Finally
  
      ClearParameters()
      _OraCmd.Dispose()
      _oracleConnection.Close()
  End Try
  </code></pre>
  
  <p>The thought is that since the connection is pooled, there is an assumed call to a LOGON Trigger that never happens and the Client Identifier is never set again. ORACLE's documentation, however, states that the ClientID is used for exactly what we are trying to do.</p>
  
  <p>Does anyone have any thoughts as to why the SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') is not being set to a new USERID that is passed into the ClientID when connection pooling is used within our .NET application with ODP.net? Is this a database setting, a listener setting?</p>
  
  <h2>Update</h2>
  
  <p>We forwarded the issue to Oracle. In doing so, we had to create a small test app that mimicked the issue. When doing that, on my localhost -- everything worked perfectly using Visual Studio's built-in Cassini web server. <strong>With IIS, the issue occurs.</strong> </p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Determined that IIS wasn't the problem. It was package variables not being cleared out due to connections that were pooled being re-used, in essence, what pooling is supposed to do. We solved this by using DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.REINITIALIZE).</p>