How to write a for loop in Oracle sqlplus?

Question

I'm trying to write a for loop in Oracle sqlplus interface. When writing the loop statement an pressing enter, I get an error:

SQL> for i in 1..10 loop
SP2-0734: unknown command beginning "for i in 1..." - rest of line ignored.
SQL>

Is there something wrong with my for loop clause?

Answer 1

For loop is a PL/SQL construct. Try wrapping your PL/SQL in BEGIN/END block.

If you need to declare variables, start with a DECLARE. Something like this:

set serveroutput on
begin
  for a in 1..10 loop
    dbms_output.put_line('a='||to_char(a));
  end loop;
end;
/

Hope that helps.

PS Note that set serveroutput on is a SQL*Plus command, and not part of PL/SQL. It just turns on output so you'll see the output from the dbms_output.put_line() function.