This discussion is archived
1 2 3 Previous Next 36 Replies Latest reply: Jan 10, 2013 1:07 AM by BluShadow RSS

Cursor fetch with multiple rows

Catfive Lander Explorer
Currently Being Moderated
I have been given code which, effectively, looks like this:
declare
  cursor cTest is select * from (select 'one' from dual union all select 'two' from dual);
  lvText varchar2(10);
begin
  open cTest;
  fetch cTest into lvText;
  close cTest;
  dbms_output.put_line(lvText);
end;
/
That consistently returns just the result "one", even though there are two rows in the cursor.

If I re-write the code like this:
declare
  lvText varchar2(10);
begin
  select thecol into lvText from (select 'one' as thecol from dual union all select 'two' from dual);
  dbms_output.put_line(lvText);
end;
/
...then I get the error I expected (ORA-01422: exact fetch returns more than requested number of rows).

I guess I am a bit surprised the "fetch into" syntax (which I haven't used before) works at all. Why doesn't it spot that multiple rows are being loaded into a single variable and explode? I am guessing that which row 'wins' the competition to be assigned to the variable is completely indeterminate? Any doco on this behaviour you can point me at, please? The bit I read (http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/fetch_statement.htm) simply says that "You must use either a cursor FOR loop or the FETCH statement to process a multiple-row query", but it doesn't say "the code will silent grab one of the rows at random if you use the FETCH statement on a multiple-row resultset".

(tested on 11.2.0.3, if it makes a difference)
  • 1. Re: Cursor fetch with multiple rows
    Solomon Yakobson Guru
    Currently Being Moderated
    declare
      cursor cTest is select * from (select 'one' from dual union all select 'two' from dual);
      lvText varchar2(10);
    begin
      open cTest;
      loop
        fetch cTest into lvText;
        exit when cTest%notfound;
        dbms_output.put_line(lvText);
      end loop;
      close cTest;
    end;
    / 
    one
    two
    
    PL/SQL procedure successfully completed.
    
    SQL>
    SY.
  • 2. Re: Cursor fetch with multiple rows
    Catfive Lander Explorer
    Currently Being Moderated
    Sorry, Solomon. I'm not asking how to get the "right" answer with the FETCH syntax. (I know looping is required, for example)

    I'm asking why, given the wrong syntax example I first posted, the code nevertheless runs without complaint and produces the wrong answer.

    Why isn't there an "ORA-3987183 Fetch into used without loop" error message, for example, to warn you that the code will "work" but fail logically?
  • 3. Re: Cursor fetch with multiple rows
    Solomon Yakobson Guru
    Currently Being Moderated
    Fetch fetches one row from a cursor. It has no relation to how many rows cursor yields. When you issue FETCH you mean "give me next" row from the cursor. You can stop fetching at any time. You do not have to fetch all from the cursor. You can use loop or you can use consecutive fetches - fetch any way you want/need.

    SY.

    Edited by: Solomon Yakobson on Jan 8, 2013 7:53 PM
  • 4. Re: Cursor fetch with multiple rows
    Catfive Lander Explorer
    Currently Being Moderated
    I realise FETCH fetches one row. The problem is that without a loop, doing so is 'wrong', logically. But it's still allowed, and I can't find anywhere in the doco that says "if you use FETCH, you MUST loop through the results, otherwise we will only return the first row, which will then probably be a bug in your code".

    I'm asking for something from the doco (or from a website somewhere, maybe) that explains that "fetch without loops will work, but will be wrong". It's not something I expected, put it that way. It's something I would have thought others would have tripped over before now, anyway. In what I've read, like your code example here, you just see fetch-with-loops, with the unstated assumption made that you'd never do it any other way. I've not seen it explicitly said that it's possible to do it without a loop, but would be a dumb thing to do.
  • 5. Re: Cursor fetch with multiple rows
    Solomon Yakobson Guru
    Currently Being Moderated
    Catfive Lander wrote:
    I realise FETCH fetches one row. The problem is that without a loop, doing so is 'wrong', logically.
    Why? For example, I can get max sal by using aggregation:
    SQL> declare
      2      v_maxsal number;
      3      cursor v_cur
      4        is
      5          select  max(sal) maxsal
      6            from  emp;
      7  begin
      8      open v_cur;
      9      fetch v_cur
     10        into v_maxsal;
     11      dbms_output.put_line(v_maxsal);
     12      close v_cur;
     13  end;
     14  /
    5000
    
    PL/SQL procedure successfully completed.
    
    SQL>
    Or I can get max sal by using ORDER BY:
    SQL> declare
      2      v_maxsal number;
      3      cursor v_cur
      4        is
      5          select  sal maxsal
      6            from  emp
      7            order by sal desc;
      8  begin
      9      open v_cur;
     10      fetch v_cur
     11        into v_maxsal;
     12      dbms_output.put_line(v_maxsal);
     13      close v_cur;
     14  end;
     15  /
    5000
    
    PL/SQL procedure successfully completed.
    
    SQL>
    As you can see, cursor contains 14 rows, but all we need is the first row which holds max sal. So what is logically wrong here?

    SY.
  • 6. Re: Cursor fetch with multiple rows
    rp0428 Guru
    Currently Being Moderated
    >
    I realise FETCH fetches one row. The problem is that without a loop, doing so is 'wrong', logically. But it's still allowed, and I can't find anywhere in the doco that says "if you use FETCH, you MUST loop through the results, otherwise we will only return the first row, which will then probably be a bug in your code".

    I'm asking for something from the doco (or from a website somewhere, maybe) that explains that "fetch without loops will work, but will be wrong". It's not something I expected, put it that way. It's something I would have thought others would have tripped over before now, anyway. In what I've read, like your code example here, you just see fetch-with-loops, with the unstated assumption made that you'd never do it any other way. I've not seen it explicitly said that it's possible to do it without a loop, but would be a dumb thing to do.
    >
    Oracle has no way to know what a developer is trying to do or how many rows a cursor will return.

    It isn't necessarily true that "fetch without loops will work, but will be wrong". My cursor might only return one row.

    What about a BULK COLLECT INTO without a LIMIT clause? Should Oracle tell you that if you query too many records you may run out of memory?

    What about a BULK COLLECT INTO with a limit clause? Should Oracle tell you that "bulk collect with limit without a loop will work, but will be wrong"? It isn't necessarily wrong either.
  • 7. Re: Cursor fetch with multiple rows
    Catfive Lander Explorer
    Currently Being Moderated
    Why? For example, I can get max sal by using aggregation:

    You could. But then you'd be doing a single-row query, which (1) wasn't what I asked about and (2) would probably (IMO) be more conveniently done with a simple 'select max(sal) into v_var'

    Or I can get max sal by using ORDER BY:

    That's a pretty special case too. In general, you could not rely on row 1 of a multi-row cursor being the one you want.

    As you can see, cursor contains 14 rows, but all we need is the first row which holds max sal. So what is logically wrong here?

    In the specific cases of a single-row query or an aggregate obtainable by reliance on ordering, nothing is wrong, I guess. But using FETCH would be a strange way of achieving either, I think. And neither has anything to do with the example code I gave, which is of a multi-row query of which just one is, erroneously, returned.
  • 8. Re: Cursor fetch with multiple rows
    Catfive Lander Explorer
    Currently Being Moderated
    You're right, but again my point was, that I haven't ever seen it documented that FETCH only returns the first row and requires a loop to fetch further rows. My ignorance, if you will.

    It's just that one cursor construct handles multi-rows "properly" (my term, I realise) and the other relies on the developer knowing what he's doing. Which in the case of the code I've just inherited is one assumption too many!

    That developer had a cursor returning two rows, but he'd assumed it would only ever return one. Had he used the 'select into' syntax, the wrong assumption would have been exposed. As it is, it has been silently failing for a long time, because fetch always "works". I'm just surprised at that, that's all.
  • 9. Re: Cursor fetch with multiple rows
    SomeoneElse Guru
    Currently Being Moderated
    As it is, it has been silently failing for a long time
    It isn't failing. PL/SQL is executing the code exactly as written and behaving as expected.
    I'm just surprised at that, that's all.
    Seriously?

    How is PL/SQL supposed to know when you should or shouldn't process all the rows of a cursor?
  • 10. Re: Cursor fetch with multiple rows
    Vite DBA Pro
    Currently Being Moderated
    From the documentation


    "The FETCH statement retrieves the current row of the result set, stores the column values of that row into the variables or record, and advances the cursor to the next row."

    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#BABJIICC




    Seems pretty clear to me.

    Regards
    Andre
  • 11. Re: Cursor fetch with multiple rows
    Catfive Lander Explorer
    Currently Being Moderated
    PL/SQL is executing the code exactly as written and behaving as expected

    I agree with the first part of that statement, but not the second.

    Seriously?

    Yes. I don't write my cursor handling that way, and I'm beginning to see why.

    How is PL/SQL supposed to know when you should or shouldn't process all the rows of a cursor?

    Out of interest, when would it be OK to carefully write a SQL statement that results in a multi-row cursor and then only use the first row (which, without an order by clause, could be any row that happened to turn up at the time)? I just can't see when that would ever be a good thing to do. That could be my lack of imagination, I realise. But is it really common coding practice to do that?

    But in any case, I'm not asking for mind-reading abilities. If you have a multi-row cursor, a 'loop' with an 'exit when not found' will work and makes sense. If you have a single-row cursor, a 'loop' with an 'exit when not found' will still work and still makes sense. Therefore, a loop and exit when not found would meaningfully apply in all cases, and not having the loop would be wrong in many cases. Therefore, my thought was just that it's as syntactically wrong to do a fetch without a loop as it is to do an if without an end if -and the compiler could detect the one as easily as it already detect the other.
  • 12. Re: Cursor fetch with multiple rows
    Catfive Lander Explorer
    Currently Being Moderated
    Seems pretty clear to me.

    Well, maybe. Except that the same doco then goes on to say "*Typically*, you use the FETCH statement inside a LOOP statement". The "typically" means, to me, that you "usually do", but that you might legitimately not do. And the reason I posted was simply that I can't see when you would ever not do, without it being a bug, except in the very special circumstances of a single-row cursor, when there are more obvious syntactical ways of handling them anyway.
  • 13. Re: Cursor fetch with multiple rows
    SomeoneElse Guru
    Currently Being Moderated
    PL/SQL is executing the code exactly as written and behaving as expected
    I agree with the first part of that statement, but not the second.
    Sorry to be blunt, but it doesn't matter if you disagree. That is how PL/SQL works. Ergo, it's expected.
    I just can't see when that would ever be a good thing to do.
    Maybe, maybe not. But the point is, how is PL/SQL supposed to know that?

    The open/fetch/close statements exist to give you full control over a cursor. How you (ab)use it is up to you.
    I don't write my cursor handling that way
    Better yet, try to avoid explicit cursors whenever possible.
  • 14. Re: Cursor fetch with multiple rows
    Catfive Lander Explorer
    Currently Being Moderated
    Ergo, it's expected.

    Not by me, it wasn't. And I'm not "disagreeing" with its behaviour. I'm questioning it and my understanding of it, but that's not the same thing at all.

    how is PL/SQL supposed to know that?

    How does it spot an "if" statement that lacks a closing end-if?

    I'm not even asking it to "know" anything. How about after it opens the cursor, sees it contains more than one row, sees that it's not part of a loop, and says "exact fetch returns more than one row"? Exactly as the "select into" syntax works, in fact.

    How you (ab)use it is up to you.

    All code is like that, of course. But I'm raising a question of consistency of cursor handling. I think it's legitimate to be surprised that one syntactical form handles things different from another that is otherwise basically similar. Obviouisly, you only get surprised the first time you meet it, but I think it's OK to express that surprise when it arises!

    Better yet, try to avoid explicit cursors whenever possible.

    That, I happily agree with. I will remind you that the code which started all this was not my own!
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points