implicit pl/sql cursors not closing?

Hi,

I'm testing an application for "leaks." We call a lot of pl/sql procedures on the database that manipulates, transforms and moves data around. None of the procedures return result sets.

I have found that none of the implicit cursors used in any of the procedures close automatically (which is what I believe should happen).

These are statements that are doing updates and inserts or single value selects ( "select xxx in yyyy" ...). The procedure commits prior to end. I do have some multi-row cursors which are explicitly defined and which I explicitly close, those seem to be doing fine.

I took our application completely out of the picture and went directly to sqlplus and issued a begin procedure;end; I received the message that the pl/sql completed successfully.

At this point, I issued:
select sql_text from v$open_cursor

And what was returned to me were the inserts, updates and single-value selects that are in my procedure. The explicit cursor is not returned. These cursors are not going away. I've waited about an hour and they are still in this open state.

Does anyone have any idea why these implicit cursors aren't closing? I'm sure they will close if I end my session. However, the actual application uses a connection pool, so we can't close the connections.

Is there anyway to force the closure of an implicit cursor?

This is running on Oracle 8.1.5 on NT.

Does anyone have any ideas?
Thanks,
Gavin

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 26 2000 - 21:34:30 CDT