I wonder if using BULK COLLECT INTO
multiple collections guarantees that corresponding columns in row will have the same index in nested table.
Example:
CREATE TABLE tx AS
SELECT CAST(level AS INT) AS col1, level || 'a' AS col2, level || 'b' as col3
FROM dual
CONNECT BY level <= 100000;
Data:
col1 col2 col3
1 1a 1b
2 2a 2b
3 3a 3b
4 4a 4b
5 5a 5b
... ... ...
And code:
DECLARE
CURSOR cur IS SELECT /*+parallel(4)*/col1, col2, col3 FROM tx;
TYPE t1 IS TABLE of INT;
TYPE t2 IS TABLE of VARCHAR2(20);
vt1 t1;
vt2 t2;
vt3 t2;
BEGIN
OPEN cur;
FETCH cur BULK COLLECT INTO vt1, vt2, vt3 LIMIT 1000;
LOOP
FOR i IN 1..vt1.COUNT LOOP
DBMS_OUTPUT.put_line('i => '|| i || ' vt1 => ' || vt1(i)
|| ' vt2 => '|| vt2(i) || ' vt3 =>' || vt3(i));
END LOOP;
EXIT WHEN cur%NOTFOUND;
FETCH cur BULK COLLECT INTO vt1, vt2, vt3 LIMIT 1000;
END LOOP;
CLOSE cur;
END;
Output:
i => 1 vt1 => 22418 vt2 => 22418a vt3 =>22418b
i => 2 vt1 => 22419 vt2 => 22419a vt3 =>22419b
i => 3 vt1 => 22420 vt2 => 22420a vt3 =>22420b
i => 4 vt1 => 22421 vt2 => 22421a vt3 =>22421b
i => 5 vt1 => 22422 vt2 => 22422a vt3 =>22422b
i => 6 vt1 => 22423 vt2 => 22423a vt3 =>22423b
So in every row there is always the same prefix. Or maybe in some circumstances it is possible to get something like:
i => 1 vt1=> 100 vt2=>200a vt3=>300b
Remarks: I am aware that I could define record type and use just one collection.
Looking for an answer drawing from official sources.
The PL/SQL Language Reference documentation from Oracle does not explicitly say that values from the same row will have the same index, but it does say:
PL/SQL processes the BULK COLLECT clause similar to the way it processes a FETCH statement inside a LOOP statement.
Also, the chapter on BULK COLLECT
gives examples that implicitly assume that values from the same row will have the same index. E.g. this from example 12-22 in the Oracle PL/SQL Language Reference for version 12.1.
FOR i IN names.FIRST .. names.LAST LOOP DBMS_OUTPUT.PUT_LINE(' Employee ' || names(i) || ': $' || sals(i)); END LOOP;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments