cursor
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: cursor

  1. #1
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    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)
    TarryBlogging
    --- Everything was meant to be---

  2. #2
    Join Date
    Feb 2002
    Posts
    45

    Question I think...

    End the loop before closing the cursor.
    Shimi the Cat

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Change if to
    exit when c1%notfound

  4. #4
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    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)
    TarryBlogging
    --- Everything was meant to be---

  5. #5
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    WEll ,anybody????
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  6. #6
    Join Date
    Dec 2001
    Posts
    221
    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

  7. #7
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    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)
    TarryBlogging
    --- Everything was meant to be---

  8. #8
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587

    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)
    TarryBlogging
    --- Everything was meant to be---

  9. #9
    Join Date
    Feb 2002
    Posts
    166
    Try to fetch on a closed cursor? You can't!
    MCSE/MCDBA, OCP DBA 8i
    Working on SCJP -> SCJD

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
  •  



Click Here to Expand Forum to Full Width