-
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
Tarry Singh
I'm a JOLE(JavaOracleLinuxEnthusiast)
--- Everything was meant to be---
-
I think...
End the loop before closing the cursor.
Shimi the Cat
-
Change if to
exit when c1%notfound
-
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 Singh
I'm a JOLE(JavaOracleLinuxEnthusiast)
--- Everything was meant to be---
-
Tarry Singh
I'm a JOLE(JavaOracleLinuxEnthusiast)
--- Everything was meant to be---
-
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 ?????
Santosh Jadhav
8i OCP DBA
-
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 Singh
I'm a JOLE(JavaOracleLinuxEnthusiast)
--- Everything was meant to be---
-
JURIJ,SAM,JULIAN ,TIM,TAmilsl and more plss
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.
:((
Tarry Singh
I'm a JOLE(JavaOracleLinuxEnthusiast)
--- Everything was meant to be---
-
Try to fetch on a closed cursor? You can't!
MCSE/MCDBA, OCP DBA 8i
Working on SCJP -> SCJD
-
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?
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|