Oracle cursor running through the last item twice

Question

I have a a cursor loop that's building a string by concatenating the contents of a table together, using code along these lines:

OPEN cur_t;
LOOP
    FETCH cur_t INTO v_texttoadd;

    v_string := v_string || v_texttoadd;
EXIT WHEN cur_t%notfound;
END LOOP;

The problem is, of course, that the last item gets added twice because the system runs through it once more before realising that there's nothing more to find.

I tried playing around with something like

OPEN cur_t;
WHILE cur_t%found;
LOOP
    FETCH cur_t INTO v_texttoadd;

    v_string := v_string || v_texttoadd;
END LOOP;

But that didn't seem to return anything at all.

What kind of syntax should I be using so that each row only appears in the resulting string once?

Answer 1

I have made many cursor-based solutions in Microsoft SQL, which always worked perfectly (Good old days! I want my SQL Server back so much!) however all answers to this question proved to be wrong for me, the very last row of the cursor is ALWAYS executed twice, no matter how closely I followed the method suggested by them.

Forget the non-while loop and forget exit, this is what you MUST do to avoid double execution (pretty much what you do in T-SQL too!).

  cursor c_mm is select a, b, c, d from mytable;

  begin
    open c_mm;
    fetch c_mm into a, b, c, d;
    while (not c_mm%notfound) loop 
        -- do what you have to do here

        fetch c_mm into a, b, c, d;
    end loop;
    close c_mm;
  end;

Which I really don't understand is why all Oracle knowledge base articles and forum posts (and stackoverflow) promotes this exit-in-a-loop solution which is obviously wrong!

Answer 2

Right answers have already been given, but just elaborating a bit.

Simulating your current situation:

SQL> declare
  2    cursor cur_t
  3    is
  4    select ename
  5      from emp
  6     where deptno = 10
  7    ;
  8    v_texttoadd emp.ename%type;
  9    v_string    varchar2(100);
 10  begin
 11    open cur_t;
 12    loop
 13      fetch cur_t into v_texttoadd;
 14      v_string := v_string || v_texttoadd;
 15      exit when cur_t%notfound;
 16    end loop
 17    ;
 18    dbms_output.put_line(v_string);
 19  end;
 20  /
CLARKKINGMILLERMILLER

PL/SQL-procedure is geslaagd.

Here MILLER is printed twice. By just switching the EXIT statement and the v_string assignment, you get the desired result:

SQL> declare
  2    cursor cur_t
  3    is
  4    select ename
  5      from emp
  6     where deptno = 10
  7    ;
  8    v_texttoadd emp.ename%type;
  9    v_string    varchar2(100);
 10  begin
 11    open cur_t;
 12    loop
 13      fetch cur_t into v_texttoadd;
 14      exit when cur_t%notfound;
 15      v_string := v_string || v_texttoadd;
 16    end loop
 17    ;
 18    dbms_output.put_line(v_string);
 19  end;
 20  /
CLARKKINGMILLER

PL/SQL-procedure is geslaagd.

However, your PL/SQL code becomes easier when using a cursor-for-loop. You can then skip the v_texttoadd variable and the number of lines in your loop decreases:

SQL> declare
  2    cursor cur_t
  3    is
  4    select ename
  5      from emp
  6     where deptno = 10
  7    ;
  8    v_string    varchar2(100);
  9  begin
 10    for r in cur_t
 11    loop
 12      v_string := v_string || r.ename;
 13    end loop
 14    ;
 15    dbms_output.put_line(v_string);
 16  end;
 17  /
CLARKKINGMILLER

PL/SQL-procedure is geslaagd.

You can also use straight SQL to accomplish the job. An example with the SQL model clause, if you are on version 10g or higher:

SQL> select string
  2    from ( select string
  3                , rn
  4             from emp
  5            where deptno = 10
  6            model
  7                  dimension by (rownum rn)
  8                  measures (ename, cast(null as varchar2(100)) string)
  9                  ( string[any] order by rn desc = ename[cv()] || string[cv()+1]
 10                  )
 11         )
 12   where rn = 1
 13  /

STRING
-----------------------------------------------------------------------------------
CLARKKINGMILLER

1 rij is geselecteerd.

Regards, Rob.

Answer 3

%notfound is set when fetch fails to retrieve a new row.

another possible way (this one avoiding the "if"s and "exit when"s):

OPEN cur_t;
FETCH cur_t INTO v_texttoadd;
WHILE cur_t%found LOOP
    v_string := v_string || v_texttoadd;
    FETCH cur_t INTO v_texttoadd;
END LOOP;

Answer 4

You can try this:

OPEN cur_t;
LOOP
  FETCH cur_t INTO v_texttoadd;
  EXIT WHEN cur_t%notfound;
  v_string := v_string || v_texttoadd;
END LOOP;

This works because %notfound is set when FETCH is executed and there aren't any more rows to fetch. In your example you checked %notfound after the concatenation and as a result, you had the duplicate in the end.

Answer 5

Simple answer, though possibly not the best:

OPEN cur_t;
LOOP
    FETCH cur_t INTO v_texttoadd;
    IF cur_t%found THEN
        v_string := v_string || v_texttoadd;
    END IF;
EXIT WHEN cur_t%notfound;
END LOOP;