Problem with cursors in pl/sql functions.

From: <lamej_at_my-dejanews.com>
Date: Wed, 30 Sep 1998 14:05:58 GMT
Message-ID: <6utds6$edh$1@nnrp1.dejanews.com>

When I open a cursor inside a pl/sql function, I can't get it to close untill I kill my session I'm even explicitly closing the cursor. In fact, I'm basically doing exactly the same type of thing that Steven Feuerstein does in his oracle pl/sql programming book (First edition, p. 758, total_salary function at bottom page).

Here's a simple example of exactly what I mean.

---

SQL> create function oc return number as   2 cursor cur is
  3 select 1 from dual;
  4 rv number;
  5 begin
  6 open cur;
  7 fetch cur into rv;
  8 close cur;
  9 return rv;
 10 end;
 11 /

Function created.

SQL> select sql_text from v$open_cursor; select sql_text from v$open_cursor

SQL> select oc from dual;

         1

SQL> select sql_text from v$open_cursor; select sql_text from v$open_cursor
SELECT 1 FROM DUAL

---

This is a really big problem for me since the Java application I'm writing is not permitted to connect to the database as a dba and explicitly kill the session.
As a result, after the application has been running for a while, I start getting
"maximum open cursors exceeded" errors.

Anyway, is this a bug or am I just doing something silly? Any help in getting these cursors to close would be greatly appreciated.?

Thanks in advance,
John

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Wed Sep 30 1998 - 09:05:58 CDT