Bloody Open Cursors...

From: alan (alanbrown_at_pacbell.net)
Date: 12/11/03 

Date: 11 Dec 2003 09:04:43 -0800

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

Relevant Pages

  • RE: SSIS Exec SQL Task Output Parm Value Not Updating Package Vari
    ... Instead of using an OUTPUT parameter in the sp, set the Execute SQL Task (on ... With stored procedures, the other connection ... listed in the stored procedure, which makes the connection a little more ... I've created a Master Package that will be used as a template for developing ...
    (microsoft.public.sqlserver.dts)
  • RE: SSIS Exec SQL Task Output Parm Value Not Updating Package Variable
    ... With stored procedures, the other connection ... listed in the stored procedure, which makes the connection a little more ... I've created a Master Package that will be used as a template for developing ... At the end of the process, the created audit record will be ...
    (microsoft.public.sqlserver.dts)
  • RE: MS Query "Connections"
    ... handle that with my connection string. ... for using the stored procedure route is that it is faster. ... If you truly want to pass in the query string, ...
    (microsoft.public.excel.programming)
  • Re: How to navigate recursive stored procedures results
    ... connection is inherited by the next user, ... beforehand so any residual #Temp tables would be dropped. ... the identity is not "flow"ing to the SQL Server - ... union a SQL query and the results of a stored procedure. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Really tough ADO Stored Procedure Question. Please Help!!!
    ... Check to be sure you are connecting to the database via VB using the SAME ... Connection Objectss "Errors CVollection" in your VB. ... > Stored Procedure: (All this is in one stored procedure but this is the ... > in a #Temp2. ...
    (microsoft.public.sqlserver.odbc)