Re: Problem with explicit Cursor

"sujee" <sujee81_at_gmail.com> schreef in bericht news:1194506297.071444.123130_at_y42g2000hsy.googlegroups.com...
> 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
>

You don't have to (and CAN'T) close a cursor used in a cursor for loop. One of the great advantages of this type of cursor (only for programmers who tend to forget to close their cursors). According to Daniel Morgan (see some posts below) you never have to use cursor for loops, and in some way I think he has a point (except fo mentioned programmmers). Furthermore, you declare your cursor as get_com and use it as get_trans and you close get_com..... but that's a cut and paste error I guess.

Shakespeare Received on Thu Nov 08 2007 - 03:21:44 CST