COLLECT INTO inside a Cursor Definition?

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!

One thought on “COLLECT INTO inside a Cursor Definition?”

  1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *