Oracle PL-SQL cursor not closing in a PROCEDURE

[parbaj]

Hi,

I created the following PL/SQL package and procedure

Package Specification:

CREATE OR REPLACE PACKAGE partest1 AS -- Package Spec
PROCEDURE insert_dummy_details(num INTEGER);
END partest1;
/


Package Body:
CREATE OR REPLACE PACKAGE BODY partest1 AS -- package body
PROCEDURE insert_dummy_details(num INTEGER) IS
BEGIN
INSERT INTO pardummy values(num);
END insert_dummy_details;
END partest1;
/


Before running the Procedure insert_dummy_details

I checked the number of cursors opened by,
select count(*) from v$open_cursor;

The result was 1. I think my SQL *PLUS created that one open cursor.

Then, compiled the package, executed the procedure,

Now, the open_cursor count is 2.

I checked the SQL text for which the cursor is opened by,

select a.user_name x1, b.sid x2, b.terminal x9, b.program x10, c.sql_text x11 from

v$open_cursor a,
v$session b, v$sqlarea c where a.saddr=b.saddr and a.address=c.address

The insert statement is responsible for that open cursor
INSERT INTO PARDUMMY VALUES ( :b1 )

I'm running an application which opens many cursors like this and after some time, i'm

getting the error
ORA-0100 Maximum open cursors exceeded.

I assume that the cursor opened for insertion is implicit cursor which oracle should have

closed after the insertion is done.

Also, observed that if i run the same procedure again, the open cursor count is not

increasing.
That gives me the impression that the Oracle caches the statement and associates a cursor to

it and not closing it.

How do I make Oracle to close that implicit cursor immediately after insertion.

Please Help,
Partha

[slimdave]

This is just a guess, but you could see if commiting after the insert fixes it.

[Sameer]

Originally posted by parbaj

I'm running an application which opens many cursors like this and after some time, i'm

getting the error
ORA-0100 Maximum open cursors exceeded.

What is your OPEN_CURSORS value in init.ora?? Try increasing it

Sameer