FOR r in (SELECT ... INTO ...)

Question

Today, I came across a funny piece of code that I think should not compile. It uses an SELECT ... INTO clause within a FOR r IN ... LOOP. Here's a script that compiles on Oracle 11i. The script is a shortened version of actual PL/SQL code compiled in a package, runing in production.

create table tq84_foo (
   i number,
   t varchar2(10)
);

insert into tq84_foo values (1, 'abc');
insert into tq84_foo values (2, 'def');

declare

  rec tq84_foo%rowtype;

begin

  for r in (
     select     i,     t 
       into rec.i, rec.t -- Hmm???
       from tq84_foo
  )
  loop

    dbms_output.put_line('rec: i= ' || rec.i || ', t=' || rec.t);

  end loop;

end;
/


drop table tq84_foo purge;

The output, when run, is:

rec: i= , t=
rec: i= , t=

I believe 1) I can safely remove the INTO part of the select statement and 2) that this construct should either be invalid or exhibits at least undefined behaviour.

Are my two assumptions right?

Answer 1

Your assumptions are partly right:

1) Yes, you can safely remove the INTO part of the SELECT statement. But you should change the line in the loop to this format:

dbms_output.put_line('rec: i= ' || r.i || ', t=' || r.t);

That way it will get the data out of the r variable

2) The problem with this code is that the syntax of the SELECT ... INTO should fail if the query return more than one row. If it does not fail so it might be a bug and will have unexpected behaviour.