Avoid Max Open Cursor Error In .NET

Introduction

Generally, while working with Microsoft technologies, 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 calling the procedure from our C# or VB.NET 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.

The stored procedure for returning multiple rows will look like this:

CREATE OR REPLACE PACKAGE PKG_Candidates_List as
 TYPE Select_List_RefCur is REF CURSOR;
 PROCEDURE Get_Candidates_Passed (Candidates_ListCur OUT Projects_List_RefCur);
 End;
/

CREATE OR REPLACE PACKAGE BODY PKG_Candidates_List as

Procedure Get_Candidates_Passed (Candidates_ListCur OUT Projects_List_RefCur)
IS
  BEGIN
    open Candidates_ListCur for
SELECT A.Candidate_ID, A.Candidate_Name, 
       A.Candidate_Marks, B.Pass_Date, B.Pass_Status
 FROM Candidate A, Marks B
 Where A.Candidate_ID = B.Candidate_ID  AND A.Pass_Date = B.Pass_date
 AND B.Pass_Status = 'Passed';
 End;

End;
/

If you are using a DAL for accessing your database then the procedure call in your C# 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 increase in 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 which 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 C# page.

At this point, an easy way out would be to increase the maximum no of open cursors 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 a desired number. Though the maximum no. of open cursors could be only 2000, still in many situations, I guess it would help.

Remember, these changes will not take effect until you restart your database/machine, and the maximum no. of cursors 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 causing more no. of cursors than your server allows. This happens because each database call opens a cursor which 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 your command object by simply calling cmd.dispose() everywhere it's 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.