Ora-01000 Maximum open cursors exceeded

[Karen99]

Hi

I am using Delphi 7 and Oracle 8.1.7. I am getting the above mentioned error. I know I can change the open_cursors parameter in my init.ora file. Which I have done, to 7500. And I am still getting the problem. So I guess I must go and have a look at my delphi application. What I don't
understand is what do they mean by cursors in terms of delphi ? Is that the amount of table and query components that are open ? If that is the case, then I still don't understand what the problem is, because I do close all my queries and tables. I have already run the following query :

select user_name, status, osuser, machine, a.sql_text
from v$session b,
v$open_cursor a
where a.sid = b.sid
/

And according to that the cursor/statement that occurs the most is an insert into a table. So ...... what must I do about that ? I use a table component to insert the records and the insert is in a loop. So must I open and close the table the whole time ? Put a db.starttransaction before the insert and a db.commit after the insert ? I have done all of these and it does not work. I am clearly missing something, but got no idea what.

If anybody have an explanation, please explain like I am a 5 year old kid, because I have read alot of forums about this subject already and I am still not getting it. So I need someone with Delphi experience, because I have a few very nice Java explanations already, but that does not really help me.

Thanks
Karen

 

[aryeh1010]

It sounds like you aren't using bind variables, so that every iteration of the loop is a new cursor. If you use bind variables then it is possible to have many iterations but only one cursor.

Here is an example without bind variables in pseudo code:

for i from 1..100 loop

insert into sam
values (i);

end loop

Here is an example with bind variables in pseudo code:

declare bindVar integer

for i from 1..100 loop
bindVar = i;

insert into sam
values (bindVar);

end loop

I hope that this helps.

 

[spicysudhi]

areyh,

in ur script, example with bind variable, the statement is single line or 2 lines? (if 2 lines it should be bindvar:=i right)

for i from 1..100 loop
bindVar = i;


sorry, i am also getting same error, so just checking.

thanks,
Sudhi

 

[spicysudhi]

continue with that,

i tried both of ur example, when both are running, i found that there is no increase in the number of open cursors.

the below statemtn returns only one row for the above block execution.

SELECT user_name, status, osuser, machine, a.sql_text
FROM v$session b,
v$open_cursor a
WHERE a.sid = b.sid

i must be missing something, can u please point out.

regards,
Sudhi

 

[sem]

aryeh1010's example was not quite descriptive, and it should be emphasized that this is a pseudo code rather than pl/sql. The idea was that

insert into sam values (1);
insert into sam values (2);
insert into sam values (3);

most probably creates 3 cursors while

for i in 1..3 loop
  insert into sam values (i);
end loop;

reuses single one 3 times.
I'd suggest you to ask Delphi gurus, because your issue is not an Oracle one and was caused by specific implementation. I hope you may set some property somewhere to force your component to use bind variables.

Regards, Dima

http://seminihin.narod.ru