Re: Bloody Open Cursors...

From: Jeff Smith (jsmit234)
Date: 12/15/03 

Date: Mon, 15 Dec 2003 08:20:36 -0500

Using a "select INTO" forces Oracle to execute the query twice, simply to
handle the fact that the query may return TOO MANY ROWS. I think it is
ORA-01422: exact fetch returns more than requested number of rows. This is
referred to as an implicit cursor, which like it or not, still comes from
your open cursor pool.

Although it is a hassle, try changing your
> SELECT NVL(parentoid,0)
> INTO nParentOID
> FROM opscategory
> WHERE categoryoid = nParentCategoryOID;
into a cursor, open, fetch into nParentOID and close. See if this helps.

declare
  nParentOID NUMBER;
 CURSOR c1 is
     SELECT NVL(parentoid,0)
     FROM opscategory
     WHERE categoryoid = nParentCategoryOID;

BEGIN
OPEN C1;
FETCH C1 INTO nParentOID;
CLOSE C1;

"alan" <alanbrown@pacbell.net> wrote in message
news:ff1cce22.0312110904.77ea7ab2@posting.google.com...
> I'm having a problem with the following stored procedure.
>
> I have a connection pool that I return my connections to after use. I
> close all the resultsets and I even close the statement before
> returning the connection to the pool. However, I always end up with
> one outstanding cursor until I close the connection. It is the cursor
> attached to the SQL that begins "SELECT NVL(parentoid,0) INTO
> nParentOID...".
>
> I'm confused because this doesn't seem to be a cursor the way I
> imagine it. And it's only used internally in the stored procedure,
> but I'm unsure how I'm supposed to close it without closing the
> connection.
>
> Here's how I'm calling it in Java...
>
> try {
> stmt.registerOutParameter(1, OracleTypes.INTEGER);
> stmt.registerOutParameter(2, OracleTypes.VARCHAR);
> stmt.registerOutParameter(3, OracleTypes.CURSOR);
> stmt.setInt(4, Integer.parseInt(parentCategoryOID));
>
> stmt.execute();
>
> rs = stmt.getCursor(3);
> ...
> } finally {
> rs.close();
> stmt.close();
> }
>
>
> ...and here's the stored procedure...
>
> CREATE OR REPLACE PROCEDURE GET_CATEGORY_LEAF
> (
> nRet OUT SMALLINT,
> vErrMsg OUT VARCHAR2,
> cCategory OUT OPSX.TypeCur,
> nParentCategoryOID IN INT
> )
> IS
> nParentOID INT;
> BEGIN
> nRet := SQLCODE;
> vErrMsg := SQLERRM;
>
> SELECT NVL(parentoid,0)
> INTO nParentOID
> FROM opscategory
> WHERE categoryoid = nParentCategoryOID;
>
> IF nParentOID = 0 THEN
> -- pickup direct leaf nodes
> OPEN cCategory FOR
> SELECT categoryoid, categoryname, parentoid
> FROM category c
> WHERE NOT EXISTS
> (
> SELECT categoryoid
> FROM category
> WHERE parentoid = c.categoryoid
> )
> AND parentoid = nParentCategoryOID
> ORDER BY categoryname;
>
> ELSE
> -- pickup all leaf nodes
> OPEN cCategory FOR
> SELECT categoryoid, categoryname, parentoid
> FROM category c
> WHERE NOT EXISTS
> (
> SELECT categoryoid
> FROM category
> WHERE parentoid = c.categoryoid
> )
> START WITH parentoid = nParentCategoryOID
> CONNECT BY PRIOR categoryoid = parentoid
> ORDER BY categoryname;
> END IF;
>
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> nRet := SQLCODE;
> vErrMsg := SQLERRM;
> WHEN OTHERS THEN
> nRet := SQLCODE;
> vErrMsg := SQLERRM;
> END;
> /
>
>
> Thanks in advance, for your help.
>
> alan