TOracleQuery - Open Cursor Problem

[Robertio]

If we run a TOracleQuery.Execute then it opens the appropriate number of cursors, but upon closing and freeing the component the cursors still exist. We realise these are kept open for performance reasons, but is there not some way to close them? (Having read old posts there is a closeall for the dataset component, but this does not exist for the query).

If we subsequently execute another TOracleQuery which requires less open cursors it still hangs on to the higher number for the original query. Closing the form makes no difference, only when the TOracleSession component is disconnected do we get to reclaim the cursors.

This issue has only come to light as one of our customers has started hitting the maximum open_cursors (set at 1000, which we aren't happy about increasing any further for 45 users). Trying to diagnose the offending code is proving difficult when the application keeps hanging onto open cursors it doesn't need.

 

[Robertio]

This is running DOA 3.4.6.1 in Delphi 6 against Oracle 8.17. Same problem exists against Oracle 9.
When using DOA 4.0.5 it adds to the number of open cursors each time, rather than reusing them across queries (as it does in 3):

q1 uses 4 cursors
q2 uses 1 cursor
q3 uses 1 cursor

In 3 that would be a total of 4 open cursors, in 4 it would be 6.

 

[Marco Kalter]

I can't reproduce this. Can you send me a little demo project, either based on the dept and emp table, or based on tables for which you can send me the creation DDL and some data, to reproduce this?

 

[Robertio]

Tried it against the emp table and got different behaviour. Not had a chance yet to work out why, may have something to do with us having triggers on insert/update/delete to do custom auditing. Would there be any reason why an 'insert into audit-table' sql command ran inside a trigger would leave open cursors? (sorry, I know it's not DOA related).

If I can work out why it is happening / can replicate against the emp table I'll post/email.

Thanks

 

[al0]

Originally posted by Robertio:
Tried it against the emp table and got different behaviour. Not had a chance yet to work out why, may have something to do with us having triggers on insert/update/delete to do custom auditing. Would there be any reason why an 'insert into audit-table' sql command ran inside a trigger would leave open cursors? (sorry, I know it's not DOA related).
...
Thanks

Triggers are just regular PL/SQL code and cursors in them behave in absolutely standard waz. Particulary, if you have opened and explicit cursor in trigger (or any other PL/SQL block) you have explicitely close that cursor, regardless has trigger finished normally or it has raised an exception. BTW, it is good reason to avoid explicit cursors as much as possible. Take a look on asktom.oracle.com for a very good discussion explicit vs. implicit cursors.

 

[Robertio]

Thanks (sorry, not had much time to look at this recently) but the trigger is just running a straight forward insert (via stored procedures/functions)which if my understanding is correct is already an implicit cursor.

It can be demonstrated by creating an after-insert trigger for emp:
CREATE OR REPLACE TRIGGER EMPAFTERINSERT
after insert on emp
for each row
declare
-- local variables here
begin
insert into dept (deptno) values new.deptno);
end EMPAFTERINSERT;

Open a new SQL window in plsql and run:
select count(*) from sys.gv_$open_cursor

Then in another new SQL window run:
insert into emp (empno,deptno) values (5570,60)

Go back to the open cursor query and rerun it. You will see that there are a couple of extra open cursors. This is exactly what happens when we run the same query through Delphi, any ideas on how to tell Oracle to close these cursors? (closing the session after every insert/update isn't ideal )