Oracle. Can't understand how FOR works with subquery SELECT INTO

Question

Found strange thing. Can't understand why Oracle allow this query and why cnt variable doesn't change after it's execution:

declare cnt number;
begin
 for r in (Select count(1) into cnt from  v$session) loop
   dbms_output.put_line(cnt);
 END LOOP; 
end;

Select count(1) from v$session returns not null value

Of course I've understand:

That FOR doesn't need in this situation. Count without INTO retuns only one row.
That I can use it without INTO and it will works.

Just curious how and why it works in query above.

It's strange why Oracle allow subquery with SELECT INTO, because in common situation Oracle returns compilation error ORA-06550

declare cnt number;
 begin
        select count(1) from  (Select count(1) into cnt from  v$session)
 end;
or

And if first query works - why it doesn't return cnt value correctly?

please have a look at my answer. You should name your columns in the for select statement. Handle r like a table row. — Frank Ockenfuss
– Frank Ockenfuss, Commented Oct 15, 2015 at 8:49

Frank Ockenfuss · Accepted Answer · 2015-10-16 06:46:07Z

4

Correct sql statement

try this

declare cnt number;
begin
 for r in (Select count(1) as cnt from  v$session) loop
   dbms_output.put_line(r.cnt);
 END LOOP; 
end;

Explanation

select_statement

SQL SELECT statement (not PL/SQL SELECT INTO statement). For select_statement, PL/SQL declares, opens, fetches from, and closes an implicit cursor. However, because select_statement is not an independent statement, the implicit cursor is internal—you cannot reference it with the name SQL.

see http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/cursor_for_loop_statement.htm#LNPLS1155

the cnt variable is ignored, this can be proved by the following example, because no error is raised:

declare
  cnt       number;
  a_varchar varchar2(1);
begin
  for r in (Select 'AA' into a_varchar from v$session) loop
    dbms_output.put_line(a_varchar);
  end loop;
end;

edited Oct 16, 2015 at 6:46

answered Oct 15, 2015 at 8:47

Frank Ockenfuss

2,04312 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Frank Ockenfuss Over a year ago

@RaduGheorghiu I've tested it without problems.

Radu Gheorghiu Over a year ago

My bad, I've made a mistake in my code. You're correct.

Viktor Bardakov Over a year ago

Yep, it works. +1 to answer. But this doesn't explain why it works incorrect with INTO

Frank Ockenfuss Over a year ago

@ViktorBardakov That's right, this is unclear. I've upvoted your question for more attention

Frank Ockenfuss Over a year ago

@sstan you are right, i have merged the second answer to the first.

|

Jeffrey Kemp · Accepted Answer · 2015-10-16 02:52:42Z

2

Very interesting, I'd say this is a bug except it's a benign one. The INTO cnt bit is valid PL/SQL but is ignored in this context, which can be proven with a simple test case:

declare cnt number;
begin
 for r in (select count(1) into cnt from dual) loop
   dbms_output.put_line('cnt=' || cnt);
   dbms_output.put_line('r=' || r."COUNT(1)");
 end loop;
end;

As you can see, the INTO clause is ignored, and as expected the r record is correctly populated. Output on my 11gR2 instance:

cnt=
r=1

answered Oct 16, 2015 at 2:52

Jeffrey Kemp

60.5k15 gold badges111 silver badges161 bronze badges

1 Comment

MT0 Over a year ago

A slightly more interesting test case is to replace the first line with declare cnt number := -3.141; and then the output is cnt=-3.141. The INTO clause is ignored and the default value for cnt will be displayed.

Collectives™ on Stack Overflow

Oracle. Can't understand how FOR works with subquery SELECT INTO

2 Answers 2

8 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related