cursor

[Tarry]

when running this test code it fails giving me the invalid cursor error,
why?
the book says that the cursor must be closed outside the loop but the explnation is not getting into my skull(maybe i'm tired) that the since the cursor is closed the 2nd row fetch will return an error... can somebody pls expl why??

SQL> declare
2 v_ename emp.ename%type:='no name';
3 cursor c1 is
4 select ename
5 from emp;
6 begin
7 open c1;
8 loop fetch c1 into v_ename;
9 if c1%notfound
10 then exit;
11 else
12 dbms_output.put_line(v_ename);
13 end if;
14 close c1;
15 end loop;
16 end;
17 /
thanx in adv...
Tarry

[shimi123]

End the loop before closing the cursor.

[tamilselvan]

Change if to
exit when c1%notfound

[Tarry]

Tamilselvan,
I forgot a little detail...given the emp table has 8 rows....

Following what he said(that is closing the cursor outside the loop) the procedure completed successfully.....

He says(the book) that the loop executes 8 times(that's understood)when the fetch reaches the 2nd row it return an error...

SQL> declare
2 v_ename emp.ename%type:='no name';
3 cursor c1 is
4 select ename
5 from emp;
6 begin
7 open c1;
8 loop fetch c1 into v_ename;
9 if c1%notfound
10 then exit;
11 else
12 dbms_output.put_line(v_ename);
13 end if;
14 end loop;
15 close c1;
16 end;
17 /

PL/SQL procedure successfully completed.

Why does it return the error?

[Tarry]

WEll ,anybody????

[sjadhavdba]

cause after cursor is opened and fetching the first row it print the first output on screen ( dbmsoutput) , then it closes the cursor. after closing the cursor end loop ?????

[Tarry]

i'M SORRY BUT I COUDN'T GET IT,CAN U PLS ELUCIDATE THERE :(??


I'LL REPHRASE IT.
ENDING THE CURSOR BEFORE LOOP GIVES ERROR ON LINE 8

BUT ENDING IT AFTER IS NO PROBS....

[Tarry]

Guys can you pls explain to me why a cursor closed before the end loop gives an error when fetching a table with say 9 rows while the same block executes succefully when the cursor is closed after the loop.
:((

[Prettyman]

Try to fetch on a closed cursor? You can't!

[jmodic]

I hope prettymans short and concise answer was clear enough.

In other words, to be able to fetch from a cursor, that cursor must be opened. Once you close it, any subsequent attemp to fetch from that cursor will fail.

In your case it is very simple. The steps involved are:

1. You opened the cursor C1
2. You started your loop
3. You fetched first record from your cursor C1 (which is still open)
4. As the last step of this firs loop itteration you closed your cursor C1
5. You continued your loop with the second itteration
6. You tried to fetch a second record from your cursor C1. But this step failed, because that cursor is not opened any more - you've closed it in step 4 above, remember?