Generally while working with Microsoft technology, MS-SQL server comes out as a natural choice for the database. While it's a fact that .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 which will have a specification and a package body.
The oracle package to fetching multiple record 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 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 cursor. 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 .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 a desired number .Though the maximum no of open cursor could be only 2000 ,still in many 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 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 its 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.