ORA-01000: maximum open cursors exceeded

Using Delphi 7, BDE, and Oracle

I perform a SQL select statement and then step through each of the records of the returned set and perform the following 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;

It works for the forst 500 + records, but i then get a : ORA-01000: maximum open cursors exceeded message

Is there something i need to do on the BDE side, orac开发者_如何学JAVAle side, or within my code (Im using standard TQuery and TDatabase components)

Your close of the query is out of place, meaning you're orphaning cursors for each row. Try this instead:

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;