Syntax error in for loop

Question

Error starting at line 1 in command:

DECLARE
   x NUMBER := 0;
   counter NUMBER := 0;
BEGIN
   FOR i IN 1..4 LOOP
      x := x + 1000;
      counter := counter + 1;
      INSERT INTO temp VALUES (x, counter, 'in OUTER loop');
      END;
   END LOOP;
   COMMIT;
END;

Error report:

ORA-06550: line 11, column 10:
PLS-00103: Encountered the symbol ";" when expecting one of the following:

loop
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Have you got the `END`, `END LOOP`, `END` nesting corect? – ta.speot.is Apr 29 '13 at 09:25 — ta.speot.is, Apr 29 '13 at 09:25

score 4 · Answer 1 · answered Apr 29 '13 at 09:50

4

You don't need PL/SQL for this insert pattern. The following works in pure SQL:

create table temp(x number, counter number, text varchar2(20));

insert into temp
select (rownum-1)*1000, rownum-1, 'in OUTER loop'
from dual
connect by level <=4;

answered Apr 29 '13 at 09:50

Colin 't Hart

7,372
3
28
51

score 2 · Answer 2 · answered Apr 29 '13 at 09:31

The first END; on line 11 should not be there:

DECLARE
   x NUMBER := 0;
   counter NUMBER := 0;
BEGIN
   FOR i IN 1..4 LOOP
      x := x + 1000;
      counter := counter + 1;
      INSERT INTO temp VALUES (x, counter, 'in OUTER loop');
   END LOOP;
   COMMIT;
END;

You would only need it if you had a sub-block around the INSERT (say), for example for specific exception handling.

It would also be better generally to specify the column names in the INSERT:

      INSERT INTO temp(col1, col2, col3) VALUES (x, counter, 'in OUTER loop');

Syntax error in for loop

2 Answers2