Handling Oracle Max Open Cursor Error when Fetching Multiple Rows in ADO.NET

Generally while working with Microsoft technology, the MS-SQL server comes out as a natural choice for the database. While it's a fact that the .net framework has extensive support for SQL server when you work with other databases like oracle the situation might turn out to be a little more tricky than it is with MS-SQL. While fetching records from MS-SQL we are used to simply call the procedure from our .cs /vb file. Even if you are fetching multiple records the same procedure will work as effectively as the one which gets only one row.

But in Oracle, you have to use something called cursor specifically speaking REF_CURSOR to be able to fetch multiple rows through a stored procedure. And oracle calls it a package that will have a specification and a package body.

The oracle package to fetching multiple records will look like this:

If you are using a DAL for accessing your database then the procedure call in your cs page would look something like this:-

OracleParameter ps = new OracleParameter("contactCur", OracleType.Cursor);
ps.Direction = ParameterDirection.Output;
OracleParameter param_sowID = new OracleParameter("P_NM_SOWID", OracleType.Number);
param_sowID.Direction = ParameterDirection.Input;
param_sowID.Value = Request.QueryString["ID"].ToString();
OracleParameter[] param = { ps, param_sowID };
DataSet ds = -OracleHelper.ExecuteDataset(connectString, CommandType.StoredProcedure, "PKG_GET_CONTACTINFO.GetReadOnlyData", param);
//

Now, this code works fine almost always till you exceed the maximum no of open cursors. That's when you get something like this:

ORA-01000: maximum open cursors exceeded

Though you have opened the cursor and are able to fetch the record on the fly, there is a slight problem here. You will find that when the database calls an increase in the number you will get an error. To all those working with SQL server for years, the error will seem funny to say the least. There is no problem with the code per se, but oracle has a severe limitation when working with MS.Net. You will notice in the above-mentioned oracle package that we open a cursor that returns multiple rows but you are nowhere closing it and as the database calls increase oracle reaches its limit and your code bursts. As of now, there is no way you can close the cursor from your .cs or .vb page.

At this point, an easy way out would be to increase the maximum no of open cursor the oracle server permits. You can do this by going to the oracle command prompt and typing

ALTER SYSTEM SET open_cursors=1500 scope=both;

Alternatively, you can go to the init.ora ( urdatabase\admin\cwld\pfile\init.ora ) file and change the parameter to the desired number. Though the maximum no of open cursors could be only 2000, still in any situation I guess it would help.

Remember, these changes will not take effect until you restart your database/machine, and the maximum no of cursor allowed by oracle will also be dependent on the server m/c configuration.

However, if you still get the same problem that is because your application is opening more no. of cursor than your oracle server allows. This happens because each database call opens a cursor that is not getting closed and you have no direct way of closing it. So the only way you can close the cursor is by destroying the command object which opens the cursor. In other words, disposing of your command object by simply calling cmd.dispose()everywhere is getting used. Something like the following:

//--
public static DataSet ExecuteDataset(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters) {
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Dispose();
return ds;
}
//--

This way you control the maximum number of open cursors from your c# or vb.net pages and avoid getting errors when fetching multiple rows from oracle.