UNION operator with BULK COLLECT for a collection type
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?