CODEWITHSUNDEEP
odp.netoracle12cora-01000
Marco Zecca
Marco Zecca

Reputation: 57

Implicit ref-cursors not being closed in ODP.net 12c release 2

I'm porting a .net application from SqlServer to Oracle 12c. I'm using the unmanaged 64-bit ODAC 12c Release 2 (12.1.0.1.2) client to access the database.

Oracle 12c introduced the DBMS_SQL.RETURN_RESULT(cur) function that allows me to reuse the .net code as it is without having to add specific output parameters to the ado.net commands. This is a snippet of what my code looks like:

using (var command = CreateCommand("uspGetAllNumericUnits", CommandType.StoredProcedure))
        {
            using (var connectionScope = command.Connection.CreateConnectionScope())
            {
                using (var reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            ....
                        }
                    }
                }
            }
        }

The uspGetAllNumericUnits stored procedure is simply like:

PROCEDURE uspGetAllNumericUnits_RPT
AS
  cv_1 SYS_REFCURSOR;
BEGIN
  OPEN cv_1 FOR
      SELECT * FROM NumericUnit;

  DBMS_SQL.RETURN_RESULT(cv_1);
END;

I believe I properly dispose the dbReader and the dbCommand and the connection is closed and disposed as well by the connectionScope. However, if I check the v$open_cursor view on the server, I see that a cursor for "SELECT * FROM NumericUnit;" remains open. I use connection pooling, if I disable it (which I do not want to do) the cursor gets correctly closed as the connectionScope gets disposed.

My issue is that I have many ado.net calls like this one, and I quickly reach the max allowed cursor limit per session and an ORA-01000 error is raised.

If I use the old Oracle 11g approach, returning the resultset cursor as an output parameter and not using the DBMS_SQL.RETURN_RESULT(cv_1) function, the cursor gets correctly closed as the connectionScope is disposed regardless connection pooling or not.

Are there some extra objects I need to dispose the close the implicit ref-cursors? Is this a known ODAC 12cR2 bug? The introduction of DBMS_SQL.RETURN_RESULT(cv_1) makes the porting from SqlServer to Oracle way easier than having to add output parameters almost everywhere, but I do not want to get rid of connection pools.

Upvotes: 1

Views: 1140

Answers (0)

Related Questions