Re: Stored procedure failed: maximum open cursors exceeded

From: <thomas.kyte_at_oracle.com>
Date: 27 Jan 2006 04:32:23 -0800
Message-ID: <1138365143.325044.99690@g43g2000cwa.googlegroups.com>

The cached cursors managed by PLSQL are automagically closed when you get near open_cursors - they are *not* the cause of this.

And in fact, when you use explicit cursors and explicitly close them - guess what? They are not really closed! They are cached in the same exact fashion as implicit cursors!

(so your reasoning for this preference is based on incorrect assumptions)

Consider:

ops$tkyte_at_ORA9IR2> create or replace procedure p   2 as

  3          cursor c is select * from dual explicit;
  4          l_dummy dual.dummy%type;
  5  begin
  6          for x in ( select * from dual implicit )
  7          loop
  8                  null;
  9          end loop;

 10
 11

 12          open c;
 13          loop
 14                  fetch c into l_dummy;
 15                  exit when c%notfound;
 16          end loop;
 17          close c;

 18 end;
 19 /

Procedure created.

ops$tkyte_at_ORA9IR2> alter session set sql_trace=true;

Session altered.

ops$tkyte_at_ORA9IR2> exec p

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA9IR2> exec p

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA9IR2> exec p

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA9IR2> exec p

PL/SQL procedure successfully completed.

Now, if the explicit cursor was "truly closed", we would expect there to be 4 parses of it, once for each invocation - but there is not:

SELECT * FROM DUAL IMPLICIT call count cpu elapsed disk query current

    rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.00       0.00          0          0          0
       0
Execute      4      0.00       0.00          0          0          0
       0
Fetch        8      0.00       0.00          0         12          0
       4

------- ------ -------- ---------- ---------- ---------- ----------

---

total       13      0.00       0.00          0         12          0
       4
********************************************************************************

SELECT * FROM DUAL EXPLICIT call count cpu elapsed disk query current

    rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.00       0.00          0          0          0
       0
Execute      4      0.00       0.00          0          0          0
       0
Fetch        8      0.00       0.00          0         12          0
       4

------- ------ -------- ---------- ---------- ---------- ----------

---

total       13      0.00       0.00          0         12          0
       4

explicit cursors are cached just like implicit ones. Received on Fri Jan 27 2006 - 06:32:23 CST