Oracle course going through the last item twice

Question

I have a cursor loop that builds a row, combining the contents of the table together, using the code in these rows:

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

Of course, the problem is that the last element is added twice, because the system goes through it again before realizing that there is nothing to find.

I tried to play 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 this did not seem to return anything.

What syntax should be used so that each line appears only in the resulting line?

Answer 1

You can try the following:

 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 are no extra lines to retrieve. In your example, you checked% notfound after concatenation, and as a result, you had a duplicate at the end.

Answer 2

Correct answers have already been given, but a little worked out.

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. Just by switching the EXIT statement and 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 a loop. Then you can 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 direct SQL to do the job. An example with the suggestion of the SQL model 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 cannot get a new line.

another possible way (this avoids "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

A simple answer, although perhaps 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; 

Answer 5

I made a lot of cursor-based solutions in Microsoft SQL that always worked perfectly (good old days! I want my SQL Server so much!), However all the answers to this question turned out to be wrong for me, the cursor line is ALWAYS executed twice, regardless on how closely I followed the method they proposed.

Forget the non-time loop and forget exit , this is what you SHOULD 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; 

What I really don't understand is why all the Oracle Knowledge Base articles and forum posts (and stackoverflow) support this loop-through solution, which is obviously wrong!