How do I close cursor in recursive stored proc?
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