在 .NET 中避免最大打开游标错误

引言

通常，在使用 Microsoft 技术时，MS-SQL Server 成为数据库的自然选择。虽然 .NET 框架对 SQL Server 有广泛的支持，但当您使用其他数据库（如 Oracle）时，情况可能比 MS-SQL 更复杂。

从 MS-SQL 提取记录时，我们习惯于简单地从 C# 或 VB.NET 文件中调用存储过程。即使您提取多条记录，相同的存储过程也会像获取单行数据一样有效。

但在 Oracle 中，您必须使用游标；具体来说，使用 REF_CURSOR，才能通过存储过程提取多行数据。

用于返回多行数据的存储过程将如下所示

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;
/

如果您使用 DAL 访问数据库，则 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);

现在，此代码几乎总是在您超过最大打开游标数之前正常工作。当您遇到这种情况时，您会得到类似这样的错误

ORA-01000: maximum open cursors exceeded

尽管您已经打开了游标并且能够即时获取记录，但这里存在一个问题。您会发现，当数据库调用次数增加时，您将遇到错误。对于那些多年使用 SQL Server 的人来说，这个错误说起来很有趣。

代码本身没有问题，但 Oracle 在与 MS .NET 协同工作时存在严重的限制。您会注意到，在上面提到的 Oracle 包中，我们打开了一个返回多行数据的游标，但您并没有在任何地方关闭它，并且随着数据库调用的增加，Oracle 达到了其限制，您的代码崩溃了。目前，您无法从 C# 页面关闭游标。

此时，一个简单的解决方法是增加 Oracle 服务器允许的最大打开游标数。您可以通过转到 oracle 命令行并输入

ALTER SYSTEM SET open_cursors=1500 scope=both;

或者，您可以转到 init.ora (urdatabase\admin\cwld\pfile\init.ora) 文件并将参数更改为所需数字。尽管最大打开游标数只能是 2000，但在许多情况下，我认为这会有所帮助。

请记住，这些更改只有在您重新启动数据库/机器后才会生效，并且 Oracle 允许的最大游标数也将取决于服务器 m/c 配置。

但是，如果您仍然遇到相同的问题，那是因为您的应用程序导致了比服务器允许的更多的游标数。发生这种情况是因为每个数据库调用都会打开一个未关闭的游标，并且您没有直接的方法来关闭它。因此，您关闭游标的唯一方法是销毁打开游标的命令对象。换句话说，通过简单地在 cmd.dispose() 使用的任何地方调用 cmd.dispose() 来释放您的命令对象。类似于以下内容

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;
  }

通过这种方式，您可以控制 C# 或 VB.NET 页面中最大打开游标的数量，并避免从 Oracle 提取多行数据时出现错误。