Problem with explicit Cursor

Hi,
I have problem with following program. Here I have a named cursor defined inside the declare section. Then use a cursor for loop. Assume that inside the cursor for loop there is a exception generated.

When there is a exception generated inside the cursor for loop, when will happen to the cursor? Is it closed or opened?

I assume it is in open state, So I try to close it. Here there are to case,
a) Closing cursor inside the inner block exception handler It is possible here.
b ) Closing cursor inside the outer block exception handler It is not possible, bec get_com%ISOPEN return false always. Why????????

To solve my problem, I can use inner block excep handler to close the cursor. That is ok. But I need to forward the some other exceptions to outer block, So I used the raise statement. In this case, Is it necessary to close the cursor again.

DECLARE
CURSOR get_com IS
SELECT 1 FROM company;

BEGIN
FOR tm_rec_ IN get_trans LOOP
BEGIN
-- vialating unique constraint

INSERT INTO testtt VALUES (1);
COMMIT;
EXCEPTION
-- Case 1

WHEN OTHERS THEN
dbms_output.put_line('1111111111');
IF get_trans%ISOPEN THEN
dbms_output.put_line('22222222222');
--CLOSE get_com;

END IF;
RAISE;
END;
END LOOP;
EXCEPTION
-- Case 2

WHEN OTHERS THEN
dbms_output.put_line('333333333333');
IF get_com%ISOPEN THEN
-- Never reach this

dbms_output.put_line('4444444444444');
--CLOSE get_com;

END IF;
END;
/

Hope I will get detail answer for my doubts. /Sujee Received on Thu Nov 08 2007 - 01:18:17 CST