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.

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:

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.