On Nov 8, 2:21 pm, "shakespeare" <what..._at_xs4all.nl> wrote:
> "sujee" <suje..._at_gmail.com> schreef in berichtnews: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_com%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
Thank u Shakespeare. Sorry, that is a cus & paste error.
Received on Fri Nov 09 2007 - 03:23:32 CST
