CODEWITHSUNDEEP
databasedelphioracle10gcursor
PDM
PDM

Reputation: 603

Ora-01000 - maximum open cursors exceeded error

I am receiving the following error message within my Delphi/Oracle application "ora-01000 - maximum open cursors exceeded". The code is as follows:

begin
  for i := 0 to 150 do 
  begin

    with myADOQuery do
    begin
      SQL.Text := 'DELETE FROM SOMETABLE';
      ExecSQL; -- from looking at V$OPEN_CURSOR a new cursor is added on each iteration for the session
      Close; -- thought this would close the cursor but doesn't
    end;

  end;

end;

I'm aware I can resolve the problem by simply increasing the number of OPEN_CURSORS parameters, however, I would rather find a solution whereby the cursor is closed after the query is executed. Any ideas?

Delphi 2006 BDS Oracle 10g

Upvotes: 0

Views: 3590

Answers (4)

RRUZ
RRUZ

Reputation: 136391

try using the TADOCommand component instead.

TADOCommand is most often used for executing data definition language (DDL) SQL commands or to execute a stored procedure that does not return a result set.

or using directly the TADOConnection.Execute function

Upvotes: 1

user160694
user160694

Reputation:

  • Read Oracle Support documents ID 76684.1 and ID 2055810.6. I do not use ADO, but you may have to find a way to tell it how to configure Oracle not to cache statements.
  • The default max_cursor value is usually too low, is usually better to increase it, it will made Oracle use a little more memory but on actual machine it is rarely an issue.
  • To delete a whole table TRUNCATE may be better than DELETE, unless you have to rely on DELETE behaviour (i.e. firing triggers).

Upvotes: 2

user532231
user532231

Reputation:

Check this link. I'm not Oracle user, but as it seems there is some cursor cache and as they say "The best advice for tuning OPEN_CURSORS is not to tune it. Set it high enough that you won't have to worry about it." So I would say even if the Close command closes the cursor it still remains in the cache. There are also some tips, how to check your current situation.

Upvotes: 1

Rikalous
Rikalous

Reputation: 4564

What happens if you omit the Close?

Upvotes: 0

Related Questions