Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

UNION operator with BULK COLLECT for a collection type

user_snehaMar 12 2013 — edited Mar 12 2013
Hi all,

I created a table type as below:
create or replace type coltest is table of number;

Below are 3 PL/SQL blocks that populate data into variables of the above mentioned table type:


BLOCK 1:

DECLARE
col1 coltest := coltest(1, 2, 3, 4, 5, 11);
col2 coltest := coltest(6, 7, 8, 9, 10);
col3 coltest := coltest();

BEGIN

SELECT * BULK COLLECT
INTO col1
FROM (SELECT *
FROM TABLE(CAST(col1 AS coltest))
UNION ALL
SELECT * FROM TABLE(CAST(col2 AS coltest)));

dbms_output.put_line('col1');
dbms_output.put_line('col1.count: ' || col1.COUNT);

FOR i IN 1 .. col1.COUNT
LOOP
dbms_output.put_line(col1(i));
END LOOP;

END;

OUPUT:
col1
col1.count: 5
6
7
8
9
10



BLOCK 2:

DECLARE
col1 coltest := coltest(1, 2, 3, 4, 5, 11);
col2 coltest := coltest(6, 7, 8, 9, 10);
col3 coltest := coltest();

BEGIN
SELECT * BULK COLLECT
INTO col2
FROM (SELECT *
FROM TABLE(CAST(col1 AS coltest))
UNION ALL
SELECT * FROM TABLE(CAST(col2 AS coltest)));

dbms_output.put_line('col2');
dbms_output.put_line('col2.count: ' || col2.COUNT);

FOR i IN 1 .. col2.COUNT
LOOP
dbms_output.put_line(col2(i));
END LOOP;
END;

OUTPUT:
col2
col2.count: 6
1
2
3
4
5
11

BLOCK 3:

DECLARE
col1 coltest := coltest(1, 2, 3, 4, 5, 11);
col2 coltest := coltest(6, 7, 8, 9, 10);
col3 coltest := coltest();

BEGIN

SELECT * BULK COLLECT
INTO col3
FROM (SELECT *
FROM TABLE(CAST(col1 AS coltest))
UNION ALL
SELECT * FROM TABLE(CAST(col2 AS coltest)));

dbms_output.put_line('col3');
dbms_output.put_line('col3.count: ' || col3.COUNT);

FOR i IN 1 .. col3.COUNT
LOOP
dbms_output.put_line(col3(i));
END LOOP;
END;


OUTPUT:

col3
col3.count: 11
1
2
3
4
5
11
6
7
8
9
10

Can anyone please explain the output of BLOCK 1 and 2? Why doesn't bulk collect into col1 and col2 return 11 as count?
This post has been answered by BluShadow on Mar 12 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 9 2013
Added on Mar 12 2013
3 comments
1,202 views