CODEWITHSUNDEEP
sqloracle-databaseplsql
Viktor Bardakov
Viktor Bardakov

Reputation: 876

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

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:

  1. That FOR doesn't need in this situation. Count without INTO retuns only one row.
  2. 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?

Upvotes: 7

Views: 175

Answers (2)

Frank Ockenfuss
Frank Ockenfuss

Reputation: 2043

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;

Upvotes: 4

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

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

Upvotes: 2

Related Questions