Mega Code Archive
FETCH cursor BULK COLLECT
<![CDATA[
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60));
Table created.
SQL>
SQL> set serveroutput on format wrapped
SQL>
SQL> DECLARE
2 TYPE t_Numbers IS TABLE OF MyTable.num_col%TYPE;
3 TYPE t_Strings IS TABLE OF MyTable.char_col%TYPE;
4 v_Numbers t_Numbers := t_Numbers(1);
5 v_Strings t_Strings := t_Strings(1);
6 v_Numbers2 t_Numbers;
7 v_Strings2 t_Strings;
8
9 CURSOR c_char IS
10 SELECT char_col
11 FROM MyTable
12 WHERE num_col > 800
13 ORDER BY num_col;
14
15 BEGIN
16 v_Numbers.EXTEND(1500);
17 v_Strings.EXTEND(1500);
18 FOR v_Count IN 1..1000 LOOP
19 v_Numbers(v_Count) := v_Count;
20 v_Strings(v_Count) := 'Element #' || v_Count;
21 IF v_Count > 500 THEN
22 v_Numbers(v_Count + 500) := v_Count;
23 v_Strings(v_Count + 500) := 'Element #' || v_Count;
24 END IF;
25 END LOOP;
26
27 DELETE FROM MyTable;
28 FORALL v_Count IN 1..1500
29 INSERT INTO MyTable (num_col, char_col)
30 VALUES (v_Numbers(v_Count), v_Strings(v_Count));
31
32 SELECT num_col, char_col
33 BULK COLLECT INTO v_Numbers, v_Strings
34 FROM MyTable
35 ORDER BY num_col;
36
37 DBMS_OUTPUT.PUT_LINE('First query fetched ' || v_Numbers.COUNT || ' rows');
38
39 SELECT num_col
40 BULK COLLECT INTO v_Numbers2
41 FROM MyTable;
42
43 DBMS_OUTPUT.PUT_LINE('Second query fetched ' || v_Numbers2.COUNT || ' rows');
44
45 OPEN c_char;
46 FETCH c_char BULK COLLECT INTO v_Strings2;
47 CLOSE c_char;
48
49 DBMS_OUTPUT.PUT_LINE('Cursor fetch retrieved ' || v_Strings2.COUNT || ' rows');
50
51 END;
52 /
First query fetched 1500 rows
Second query fetched 1500 rows
Cursor fetch retrieved 400 rows
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL>
]]>