oracle - ORA-01000 : maximum open cursors exceeded

Question

使用 Delphi 7、BDE 和 Oracle

我执行 SQL select 语句，然后单步执行返回集的每条记录并执行以下更新 sql

var
 AQuery: TQuery;
begin
 AQuery:= TQuery.Create(nil);
 AQuery.DatabaseName:= ADatabase.DatabaseName;
 with AQuery do
 begin
  SQL.Text:= 'UPDATE AP_Master SET CMCL_FORECAST_CLEARED=:AClearedDate WHERE ID=:AMasterId';
  ParamByName('AMasterId').AsString:= IntToStr(AId);
  ParamByName('AClearedDate').AsDateTime:= StrToDateTime(FormatDateTime('mm/dd/yyyy', AForeCastClearedDate));
  try
   ExecSql;
  except on E: Exception do
   begin
    raise Exception.create('Error Updating AP_Master Tables Forecast Cleared Date!' + E.Message);
   end;//except
  end; //try
 end; //with
 AQuery.Close;
 AQuery.Free;
end;

它适用于前 500 多条记录，但我随后收到一条:ORA-01000:超过最大打开游标数消息

我需要在 BDE 端、oracle 端或我的代码中执行某些操作(我使用标准 TQuery 和 TDatabase 组件)

Answer 1

您的查询结束不合适，这意味着您正在孤立每一行的游标。试试这个:

var
 AQuery: TQuery;
begin
  AQuery:= TQuery.Create(nil);
  try
    AQuery.DatabaseName:= ADatabase.DatabaseName;

    with AQuery do
    begin
      SQL.Text:= 'UPDATE AP_Master'#13 +
                 'SET CMCL_FORECAST_CLEARED = :AClearedDate'#13 +
                 'WHERE ID= :AMasterId';
      ParamByName('AMasterId').AsInteger := AId;
      // Note the date->string->date is not necessary; setting the param
      // AsDateTime with a TDateTime value will format it correctly for you.
      ParamByName('AClearedDate').AsDateTime:= AForeCastClearedDate;

      try // Protect open
        try
          ExecSql;
          except 
           on E: Exception do
             raise Exception.create('Error Updating AP_Master Tables' +
                                    ' Forecast Date Cleared' +
                                    E.Message);   
          end;//except
        end; // except try
      finally
        AQuery.Close;  // finally means it's closed every time always
      end; //finally try for opening
    end; //with
  finally
    AQuery.Free;  // finally here ensures free
  end;
end;