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

Question

Today I came across a fun piece of code that I think should not compile. It uses the SELECT ... INTO within FOR r IN ... LOOP . Here is a script that compiles on Oracle 11i. The script is a shortened version of the actual PL / SQL code compiled in a package that starts during 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; 

Output signal at startup:

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

I believe that 1) I can safely remove the INTO part of the select statement and 2) that this construct must either be invalid or show undefined behavior.

Are my two assumptions correct?

Answer 1

Your assumptions are partly right:

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

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

Thus, it will receive data from the variable r

2) The problem with this code is that the SELECT ... INTO syntax should fail if the query returns more than one row. If this does not work, it may be a mistake and will have unexpected behavior.