ORA-01000:超过了最大打开游标数

Question

使用Delphi 7、BDE和Oracle

我执行SQL select语句，然后遍历返回集的每个记录，并执行以下update 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;

它适用于forst500+记录，但随后我得到一条: ORA-01000: maximum open cursors消息

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