I have been programming in PL/SQL since 1993, more or less continuously (I’ve just realized that it’s exactly half of my life until now), so I know the PL/SQL syntax quite well.
So when I saw the following piece of code a few days ago, I was willing to bet it would not compile:
declare
v_number_array sys.odcinumberlist;
begin
for v_rec in (select rownum as num
bulk collect
into v_number_array
from dual
connect by level <= 5)
loop
dbms_output.put_line(v_rec.num);
end loop;
end;
Note it's a Cursor FOR LOOP that includes a BULK COLLECT INTO clause inside the cursor definition.
Luckily, I did not bet. I would have lost...
SQL> declare 2 v_number_array sys.odcinumberlist; 3 begin 4 for v_rec in (select rownum as num 5 bulk collect 6 into v_number_array 7 from dual 8 connect by level <= 5) 9 loop 10 dbms_output.put_line(v_rec.num); 11 end loop; 12 end; 13 / 1 2 3 4 5 PL/SQL procedure successfully completed.
We can see that the Cursor FOR LOOP statement itself worked well. But what about the BULK COLLECT INTO? Did it populate v_number_array as well? Not likely, but I'm not going to guess anymore:
SQL> declare 2 v_number_array sys.odcinumberlist := sys.odcinumberlist(101, 102, 103); 3 begin 4 for v_rec in (select rownum as num 5 bulk collect 6 into v_number_array 7 from dual 8 connect by level <= 5) 9 loop 10 dbms_output.put_line(v_rec.num); 11 end loop; 12 13 dbms_output.new_line; 14 15 for i in 1 .. v_number_array.count 16 loop 17 dbms_output.put_line(v_number_array(i)); 18 end loop; 19 end; 20 / 1 2 3 4 5 101 102 103 PL/SQL procedure successfully completed.
We can see that the "bulk collect into v_number_array" part is simply ignored.
Obviously, the fact that we can write such a confusing code, does not mean we should.
What about explicit cursors?
SQL> declare 2 v_number_array sys.odcinumberlist := sys.odcinumberlist(101, 102, 103); 3 v_array2 sys.odcinumberlist; 4 cursor explicit_cur is 5 select rownum as num 6 bulk collect 7 into v_number_array 8 from dual 9 connect by level <= 5; 10 begin 11 open explicit_cur; 12 fetch explicit_cur bulk collect 13 into v_array2; 14 close explicit_cur; 15 16 for i in 1 .. v_array2.count 17 loop 18 dbms_output.put_line(v_array2(i)); 19 end loop; 20 21 dbms_output.new_line; 22 23 for i in 1 .. v_number_array.count 24 loop 25 dbms_output.put_line(v_number_array(i)); 26 end loop; 27 end; 28 / 1 2 3 4 5 101 102 103 PL/SQL procedure successfully completed.
It's the same behavior. The BULK COLLECT INTO clause in the cursor definition is ignored.
I was wondering if the PL/SQL compiler has a bug or if it is just being forgiving. So I compiled the code with warnings enabled:
SQL> alter session set plsql_warnings='enable:all'; Session altered. SQL> create or replace procedure confusing authid definer as 2 v_number_array sys.odcinumberlist := sys.odcinumberlist(101, 102, 103); 3 cursor explicit_cur is 4 select rownum as num 5 bulk collect 6 into v_number_array 7 from dual 8 connect by level <= 5; 9 begin 10 for v_rec in (select rownum as num 11 bulk collect 12 into v_number_array 13 from dual 14 connect by level <= 5) 15 loop 16 dbms_output.put_line(v_rec.num); 17 end loop; 18 end; 19 / SP2-0804: Procedure created with compilation warnings SQL> show err Errors for PROCEDURE CONFUSING: LINE/COL ERROR -------- ----------------------------------------------------------------- 3/5 PLW-06006: uncalled procedure "EXPLICIT_CUR" is removed. 4/9 PLW-05016: INTO clause should not be specified here 4/9 PLW-05024: Do not use BULK COLLECT clause in a cursor declaration 10/19 PLW-05016: INTO clause should not be specified here 10/19 PLW-05024: Do not use BULK COLLECT clause in a cursor declaration
So the compiler is definitely aware of this, and recommends that we don't do it.
Yet one more reason to enable PL/SQL warnings on a regular basis!
Yes to this:
“Yet one more reason to enable PL/SQL warnings on a regular basis!”
It’s just one of the great tools the PL/SQL dev team has built into the PL/SQL engine, others including conditional compilation and PL/Scope.
And note that PLW-05016 is not specific to BULK COLLECT. It’s basically a quirk of the compiler that the INTO clause is accepted, but then ignored.