ORA-01000: maximum open cursors exceeded (OracleDataReader doesn`t close cursor on Dispose())
We use OraDirect .NET 3.55.20.0
1. We have procedure:
(GLOBAL_VAR.RC is ref cursor)
create or replace procedure ProcForCursorClosingTest (
oRC OUT GLOBAL_VAR.RC, oStringNull OUT VARCHAR2)
IS
BEGIN
OPEN oRC FOR SELECT 1 num, null FROM dual;
oStringNull := null;
END ProcForCursorClosingTest;
2. We have test
for (int i = 0; i < 1000; i++)
{
OracleCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "ProcForCursorClosingTest";
cmd.Parameters.Add("oRC", OracleDbType.Cursor).Direction = ParameterDirection.Output);
cmd.Parameters.Add("oStringNull", OracleDbType.VarChar).Direction = ParameterDirection.Output);
using (XDataReader rd = cmd.ExecuteReader())
{
int num = 0;
while (rd.Read())
{
num = rd["num"].AsInt32 ?? 0;
string s = rd[1].AsString;
string so = cmd.Parameters["oStringNull"].AsVarchar;
!!!!!!!!BUG!!!! variable so has value "" instead of null !!!!!!!!!!!!!
}
OracleCursor cursor = (OracleCursor)cmd.Parameters["oRC"];
!!!!!!!! cursor.IsClosed == true BUT rd.IsClosed == false !!!!!!!!!!!!
}
}
Test FAILS with error ORA-01000: maximum open cursors exceeded
3. Error disappeared after code changing to:
OracleCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "ProcForCursorClosingTest";
cmd.Parameters.Add("oRC", OracleDbType.Cursor).Direction = ParameterDirection.Output);
cmd.Parameters.Add("oStringNull", OracleDbType.VarChar).Direction = ParameterDirection.Output);
for (int i = 0; i < 1000; i++)
{
using (XDataReader rd = cmd.ExecuteReader())
{
int num = 0;
while (rd.Read())
{
num = rd["num"].AsInt32 ?? 0;
string s = rd[1].AsString;
string so = cmd.Parameters["oStringNull"].AsVarchar;
}
}
}