Oracle ODP.NET Cursor Leak?

Question

I'm running into an open cursor limit issue with using the following code. The open cursor limit on the oracle db is set to around 1000. The following code seems to hold onto the cursors even though I've got everything in a using statement (I think) that requires it. (Note, I don't need to read anything from outRefCursor2)

Am I missing a using or some other clean up with ODP.net?
The exception occurs consistently at iteration 596.

static List<Thing> GetDetailsForItems(List<string> items) {
  DateTime start = DateTime.UtcNow;
  var things = new List<Thing>();
  var spname = "SP_GET_THING_DETAILS";
  var outRefCursorName1 = "p_ref_cursor1";
  var outRefCursorName2 = "p_ref_cursor2";

  // Create params
  var pInput1 = new OracleParameter("p_input1",
                  OracleDbType.Varchar2, ParameterDirection.Input);
  pInput1.Value = "";
  // Input 2 can be blank
  var pInput2 = new OracleParameter("p_input2",
                  OracleDbType.Varchar2, ParameterDirection.Input);
  pInput2.Value = "";

  var outRefCursor1 = new OracleParameter(outRefCursorName1,
                  OracleDbType.RefCursor, ParameterDirection.Output);
  var outRefCursor2 = new OracleParameter(outRefCursorName2,
                  OracleDbType.RefCursor, ParameterDirection.Output);

  int count = 0;
  using (var conn = new OracleConnection(CONN_STR)) {
    conn.Open();
    using (var cmd = conn.CreateCommand()) {
      cmd.Parameters.Add(pInput1);
      cmd.Parameters.Add(pInput2);
      cmd.Parameters.Add(outRefCursor1);
      cmd.Parameters.Add(outRefCursor2);
      cmd.CommandText = spname;
      cmd.CommandType = CommandType.StoredProcedure;
      foreach (string value in items) {
        count++; 
        cmd.Parameters[pInput1.ParameterName].Value = value;
        var execVal = cmd.ExecuteNonQuery();
        using (var refCursor = (Types.OracleRefCursor)
                                cmd.Parameters[outRefCursorName1].Value) {
          using (var reader = refCursor.GetDataReader()) {
            while (reader.Read()) {
              // read columns
              things.Add(reader["COLUMN_A"].ToString());
            }
          } // close reader
        } // close cursor
      } // end foreach
    } // close command
  } // close connection           
  int seconds = (DateTime.UtcNow - start).Seconds;
  Console.WriteLine("Finished in {0} seconds", seconds);
  return things;
}

I'm using this snippet found online to monitor DB cursors. I can watch the cursors add up while stepping through the code. And they just keep adding at the cmd.ExecuteNonQuery() line. I never see a drop after any using statement closes.

select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, 
s.username, s.machine
from v$sesstat a, v$statname b, v$session s 
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'opened cursors current'
and machine='MY COMPUTER' 
group by s.username, s.machine
order by 1 desc;

Answer 1

Even though you're not using outRefCursor2 you still need to extract it and close it if it returns a valid cursor. ODP.net doesn't dispose of resources as nicely as the .Net version did so you need to make sure you dispose everything that is returned by ODP.net commands. As an extra step, it may not hurt to go and call .Close() explicitly on the cursors either to ensure that you're actually closing them (though the dispose should take care of that).

Answer 2

You need to dispose of the parameters:

• yes I confirmed this before showing native resource leaks (Virtual working set) when we didn't

• I prefer to dispose of the params within the life-time of the connection so as to prevent any issues when the ref cursors need/want to use the connection on dispose (superstition, probably)

  static List GetDetailsForItems(List items) { DateTime start = DateTime.UtcNow; var things = new List(); var spname = "SP_GET_THING_DETAILS"; var outRefCursorName1 = "p_ref_cursor1"; var outRefCursorName2 = "p_ref_cursor2";

  try
  {
      int count = 0;
      using (var conn = new OracleConnection(CONN_STR))
      try
      {
          conn.Open();
          // Create params
          var pInput1 = new OracleParameter("p_input1", OracleDbType.Varchar2, ParameterDirection.Input);
          pInput1.Value = "";
          // Input 2 can be blank
          var pInput2 = new OracleParameter("p_input2", OracleDbType.Varchar2, ParameterDirection.Input);
          pInput2.Value = "";
  
          var outRefCursor1 = new OracleParameter(outRefCursorName1, OracleDbType.RefCursor, ParameterDirection.Output);
          var outRefCursor2 = new OracleParameter(outRefCursorName2, OracleDbType.RefCursor, ParameterDirection.Output);
  
          using (var cmd = conn.CreateCommand())
          {
              cmd.Parameters.Add(pInput1);
              cmd.Parameters.Add(pInput2);
              cmd.Parameters.Add(outRefCursor1);
              cmd.Parameters.Add(outRefCursor2);
              cmd.CommandText = spname;
              cmd.CommandType = CommandType.StoredProcedure;
              foreach (string value in items)
              {
                  count++;
                  cmd.Parameters[pInput1.ParameterName].Value = value;
                  var execVal = cmd.ExecuteNonQuery();
                  using (var refCursor = (Types.OracleRefCursor)
                                         cmd.Parameters[outRefCursorName1].Value)
                  {
                      using (var reader = refCursor.GetDataReader())
                      {
                          while (reader.Read())
                          {
                              // read columns
                              things.Add(reader["COLUMN_A"].ToString());
                          }
                      } // close reader
                  } // close cursor
              } // end foreach
          } // close command
      } // close connection
      finally
      {
  
          pInput1.Dispose();
          pInput2.Dispose();
          outRefCursorName1.Dispose();
          outRefCursorName2.Dispose();
      }
  }
  int seconds = (DateTime.UtcNow - start).Seconds;
  Console.WriteLine("Finished in {0} seconds", seconds);
  return things;
  

  }