Re: Stored procedure failed: maximum open cursors exceeded

PL/SQL has the optimization where cursors are not closed. Instead they
are reused.
However, if the open cursors start approaching the max, then pl/sql
automatically starts closing them when it hits the limit.
The optimization is a good thing. Sybrand has opinions which
might not be backed by evidence/proof.

In your case, for that function you are just talking about couple of
implicit cursors.
I cannot believe you would be hitting the limit on just a couple of
them.
So my guess is that you might be diagnosing this wrong. Maybe you are
calling/using
this function from a jdbc app and not properly closing cursors.
You need to tell the whole story .. not just part of it.

Anurag

.

Relevant Pages

  • Re: Creating many cursors, closing them, and c0000005
    ... creation, usage, and closing of cursors. ... > but I can't tell you I'm choking on a chicken bone because I'm choking on ...
    (microsoft.public.fox.programmer.exchange)
  • Slight "I have some string, how lng it it, BTW, its blue" question
    ... I appreciate that with PL/SQL being rather more effective than, ... compare like with like. ... Now any any other RMDBS, at least I've worked with (SQLServer, SQLAnywhere, ... Sorry it this isn't clear - the reasoning behind some of the use of cursors ...
    (comp.databases.oracle.misc)
  • Re: Oracle cursor help
    ... If a "select * from myTable where " takes a long time, then so will a delete with the same predicate and no PL/SQL trick is going to speed it up. ... ctr = 0; ... DELETE/WHERE clauses consume too many resources. ... WHERE clauses defeated the efficiency of cursors ...) ...
    (perl.dbi.users)
  • Re: Slight "I have some string, how lng it it, BTW, its blue" question
    ... can you expand on what you mean by 'Using cursors to do your join'? ... As I stated before....if you can do it in SQL, choose that approach over PL/SQL. ... So if you have a view call a view, and your write SQL to reference the first view, Oracle will merge all the views into your SQL statement and then execute the entire thing as one large SQL statement. ...
    (comp.databases.oracle.misc)
  • Re: Slight "I have some string, how lng it it, BTW, its blue" question
    ... the rule of thumb I use is if it can be done with SQL ... If not, then use PL/SQL. ... can you expand on what you mean by 'Using cursors to do your join'? ... flexible that a COBOL programmer can write COBOL in PL/SQL. ...
    (comp.databases.oracle.misc)