How do I close cursor in recursive stored proc?

From: Will <wboyd_at_infertek.com>
Date: 17 Oct 2001 12:37:40 -0700
Message-ID: <4680f130.0110171137.70a26e9f_at_posting.google.com>


I have MAX CURSOR error problem with my ORACLE 9i database. We use a connection pool and each connection in the pool is holding the cursor in the stored procedure below. Because we have many such stored procedure the potential is that each connection will hold a cursor in each stored proc until that connection is closed by the pool or the app server is shut down.

Our java code is closing the statement each time it calls this stored proc and commiting the connection but nothing short of closing the connection seems to free the cursor.

I've tried to CLOSE the cursor in the stored proc but this closes all cursors in the recursive loop and an INVALID CURSOR error is raised.

Does anyone know how to close the cursor in this recursive loop without causing an INVALID CURSOR error???

Any help would be greatly appreciated, thanks.

PROCEDURE get_root_attribute
(

    in_tc_id IN tc.tc_id%TYPE,
    in_highest_flag IN INTEGER,
    out_attribute OUT VARCHAR2
)
IS

    CURSOR all_panels IS
    SELECT panel_id, attribute
    FROM panel
    WHERE tc_id=in_tc_id;

    temp_parent_id tc.parent_id%TYPE;
    temp_panel_id panel.panel_id%TYPE;

    BEGIN

         SELECT parent_id 
         INTO   temp_parent_id
         FROM   tc
         WHERE  tc_id = in_tc_id;
         IF temp_parent_id != 0 THEN
            k_tc_get_root_attribute (temp_parent_id,
                                     in_highest_flag,
                                     out_attribute);
         ELSE
            IF in_highest_flag = 0 THEN
               temp_panel_id := -1;
                FOR s_panel IN all_panels LOOP
                    IF s_panel.panel_id > temp_panel_id THEN
                        temp_panel_id := s_panel.panel_id;
                        out_attribute := s_panel.attribute;
                    END IF;
               END LOOP;
            ELSE
               temp_panel_id := 999999;
               FOR s_panel IN all_panels LOOP
                   IF s_panel.panel_id < temp_panel_id THEN
                       temp_panel_id := s_panel.panel_id;
                       out_attribute := s_panel.attribute;
                   END IF;
               END LOOP;
            END IF;
         END IF;
         -- CLOSE all_panels;

    END; Received on Wed Oct 17 2001 - 21:37:40 CEST

Original text of this message