Good Day Folks,
I have one query regarding the BULK COLLECT clause.
Could you please let me know how we can fetch/handle multiple column's data using Bulk Collect clause. Please feel free to alter below code.
-- BULK COLLECT CLAUSE WITH FETCH INTO CLAUSE
DECLARE
CURSOR my_cur IS
SELECT FIRST_NAME FROM EMPLOYEES;
TYPE nt_emp IS TABLE OF VARCHAR2(30);
nt_var nt_emp;
BEGIN
OPEN my_cur;
LOOP
FETCH my_cur BULK COLLECT INTO nt_var;
FOR indx IN nt_var.FIRST..nt_var.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(indx||' '||nt_var(indx));
END LOOP;
EXIT WHEN my_cur%NOTFOUND;
END LOOP;
CLOSE my_cur;
END;
/
Here's an example
SQL> create table t as
2 select owner, object_name, object_type
3 from all_objects
4 where rownum <= 5;
Table created.
SQL>
SQL> set serverout on
SQL> declare
2 cursor c is select * from t;
3 type row_list is table of c%rowtype;
4 r row_list;
5 begin
6 open c;
7 loop
8 fetch c bulk collect into r limit 100;
9 for i in 1 .. r.count
10 loop
11 dbms_output.put_line(r(i).owner);
12 dbms_output.put_line(r(i).object_name);
13 dbms_output.put_line(r(i).object_type);
14 end loop;
15 exit when c%notfound;
16 end loop;
17 close c;
18 end;
19 /
SYS
I_FILE#_BLOCK#
INDEX
SYS
I_OBJ3
INDEX
SYS
I_TS1
INDEX
SYS
I_CON1
INDEX
SYS
IND$
TABLE
PL/SQL procedure successfully completed.
But a nice thing with PL/SQL is that we auto fetch in batches of 100 anyway for simple cursor loops, eg
SQL> begin
2 for i in ( select * from t )
3 loop
4 dbms_output.put_line(i.owner);
5 dbms_output.put_line(i.object_name);
6 dbms_output.put_line(i.object_type);
7 end loop;
8 end;
9 /
SYS
I_FILE#_BLOCK#
INDEX
SYS
I_OBJ3
INDEX
SYS
I_TS1
INDEX
SYS
I_CON1
INDEX
SYS
IND$
TABLE
PL/SQL procedure successfully completed.
is getting the same bulk collect benefit with much easier coding