8 Replies Latest reply: Feb 6, 2013 7:36 PM by Alex.Keh .Product.Manager-Oracle RSS

    Max Open Cursor Error In .NET

    768312
      Hi,
      I am using vb.net code to connect to an Oracle database (10.2.04). I am using the following code to open and close the connection several time. But it seems the connection is not closed; and eventually I get the max open cursor error.

      I have been working in .net (connecting to oracle database) for several years, this the first time I am getting this error. For the time being I am incresing the open cursor value, but I would like to have a permanent fix for this..

      My code.....

      Dim dt As DataTable = New DataTable
      Dim ds As DataSet = New DataSet
      Dim oraConn As OracleConnection = New OracleConnection(ConfigurationManager.ConnectionStrings("connect_str").ConnectionString)
      Dim oraCmd As New OracleCommand
      Dim oraDa As OracleDataAdapter
      Dim cbSchema As String = ddlSchema_tab.SelectedItem.Text.ToString
      Dim event_key As Integer = CInt(ddlCase.SelectedItem.Value)
      Dim strSQL As String = "SELECT DS_TBL_VIEW_KEY, DS_TBL_VIEW_PHYS_NAME FROM DSIA.T_DS_TBL_VIEW " _
      & "WHERE DS_SCHEMA_NAME = :IN_SCHEMA_NAME " _
      & "AND DS_TBL_VIEW_KEY NOT IN " _
      & "(SELECT TBLV.DS_TBL_VIEW_KEY " _
      & "FROM DSIA.T_DS_EVENT_TBL_VIEW etv " _
      & "JOIN DSIA.T_DS_TBL_VIEW tblv " _
      & "ON etv.DS_TBL_VIEW_KEY = tblv.DS_TBL_VIEW_KEY " _
      & "WHERE etv.DS_EVENT_KEY = :IN_EVENT_KEY ) " _
      & "ORDER BY 2"

      With oraCmd
      Try
      .CommandText = strSQL
      .Connection = oraConn
      .CommandType = CommandType.Text
      .Parameters.AddWithValue(":IN_SCHEMA_NAME", cbSchema)
      .Parameters.AddWithValue(":IN_EVENT_KEY", event_key)
      .Connection.Open()
      .ExecuteReader()
      oraDa = New OracleDataAdapter(oraCmd)
      oraDa.Fill(ds)
      If ds.Tables.Count > 0 Then
      dt = ds.Tables(0)
      Else
      dt = New DataTable
      End If
      Catch oraEx As OracleException
      Dim str_msg As String = "ErrorPage.aspx?Page=TechnicalKnowledge.aspx&Function=GetAllTables&Message="
      str_msg = str_msg + "Error"
      .Connection.Close()
      .Connection.Dispose()
      .Dispose()
      Response.Redirect(str_msg)
      Catch ex As Exception
      Dim str_msg As String = "ErrorPage.aspx?Page=TechnicalKnowledge.aspx&Function=GetAllTables&Message="
      str_msg = str_msg + "Error"
      .Connection.Close()
      .Connection.Dispose()
      .Dispose()
      Response.Redirect(str_msg)
      Finally
      .Connection.Close()
      .Connection.Dispose()
      .Dispose()
      End Try
      End With
      Return dt


      I would like get an answer for this bug.
      Thanks a lot.
      Lalita
        • 1. Re: Max Open Cursor Error In .NET
          gdarling - oracle
          Hi,

          It doesnt look like you're disposing of the command, dataadapter, etc objects. You need to do that too.

          You're calling ExecuteReader but not doing anyting with the returned reader?

          Also, response.redirect causes thread aborts which can prevent ODP from being able to clean up unamanaged resources. http://support.microsoft.com/kb/312629

          Hope it helps,
          Greg
          • 2. Re: Max Open Cursor Error In .NET
            768312
            Thanks for your reply. Actually I was disposing the connection and the data adopter, but forgot to dispose the datareader. I will do that, and let you know how it works. Your reply on the other related questions were very helpful to me. Keep up the good work.


            Lalita
            • 3. Re: Max Open Cursor Error In .NET
              764804
              Use the VB Using statement for everything that has a dispose method.
              • 4. Re: Max Open Cursor Error In .NET
                486393
                It would be nice if the c# and vb.net compilers showed warnings when the dispose is missing.

                (I know that this is not odp.net or Oracle related).
                • 5. Re: Max Open Cursor Error In .NET
                  Jenny -Oracle
                  Hi,

                  There are some third party utilities that will help with this during unit testing. You may want to investigate these if you have a large amount of code you want review to make sure you have called dispose on any object created that implements IDisposable. This will help detect all objects are being properly disposed not just ODP objects :)

                  Regards
                  J.B.
                  • 6. Re: Max Open Cursor Error In .NET
                    410544
                    Did you ever resolve this? We tried all the obvious stesp and finally succeeded by disabling connection pooling. I suspect that the pooling is causing something (like one of the Disposes) from doing what we are explicitly invoking. It's also possible that the Oracle server is simply freeing up the resource more slowly than the client (and the non-pooling version is so much slower that it may just be a solution via throttling)

                    Thanks,

                    James Smyth
                    • 7. Re: Max Open Cursor Error In .NET
                      989548
                      We have tried to fix the open cursor issue by closing the oracle connection,disposing the oracle command object and connection object. By doing that alone the issue didn't fix. Later, we tried setting Pooling=false and it worked.

                      But, now we have another issue which is performance. Earlier to do a particular transaction it used to take 1min and now it takes exactly 2 min. Apart from setting Pooling=false; Do we have any other option to solve Open cursor issue ?

                      Please let us know if you guys have solution for it.

                      Thanks,
                      KD
                      • 8. Re: Max Open Cursor Error In .NET
                        Alex.Keh .Product.Manager-Oracle
                        It's a good idea to close/dispose all ODP.NET objects created explicitly, not just connections and commands.

                        The simplest way to have your application support additional open cursor is to increase the max OPEN_CURSORS that can be supported. Have your DBA increase the number to avoid this error.