Click here to Skip to main content
15,861,168 members
Articles / Programming Languages / SQL
Article

Avoid Max Open Cursor Error In .NET

Rate me:
Please Sign up or sign in to vote.
3.80/5 (4 votes)
18 Aug 20043 min read 135.1K   21   9
Handling Oracle Max Open cursor error when fetching multiple rows 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:

SQL
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:

C#
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:

SQL
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:

C#
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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Generalhi Pin
mungara1-Feb-07 9:52
mungara1-Feb-07 9:52 
Generalthnks Pin
royan7-Feb-06 2:19
royan7-Feb-06 2:19 
Generalcmd.Cancel also seems to do the trick - though dont know any other pitfalls of the same Pin
akashr1-Nov-05 17:51
akashr1-Nov-05 17:51 
Questionwhy can it dont work...? Pin
Anonymous25-Oct-05 16:36
Anonymous25-Oct-05 16:36 
GeneralAttention!!! Everyone!----http://www.ebook5.com a lot of database ebooks Pin
wtrewt11-Aug-05 23:44
susswtrewt11-Aug-05 23:44 
Generalopen cursor limit Pin
Anonymous24-Mar-05 5:33
Anonymous24-Mar-05 5:33 
Questioncmd.dispose() couldn't close the cursor??? Pin
Anonymous28-Dec-04 17:16
Anonymous28-Dec-04 17:16 
GeneralNot particular to Oracle Pin
Sebastien Lorion24-Aug-04 10:08
Sebastien Lorion24-Aug-04 10:08 
GeneralFix doesn't account for Exception Pin
Barry Etter19-Aug-04 3:41
Barry Etter19-Aug-04 3:41 
To be safer, the cmd.Dispose() should be in the "Finally" (VB?) block of a Try/Catch block. If not, cmd.Dispose() won't get called if the stored procedure raises an Oracle error.

Barry Etter

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.