BULK COLLECT INTO Collection Via SQL Statements In Oracle

Getting all data once from a table with a bulk collect instead of cursor is more useful. Once you get all data and you work on it. When bulk collecting into collection type you must consider some points

• You must create a global type for collections. It is not possible to use local types in SQL statements.
• Oracle can not convert scala types to reference types. You must explicitly convert collection type when bulk collecting.

There is a simple demonstration to show this.

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as HR

SQL>
SQL> drop type employee_tab;

drop type employee_tab

ORA-04043: object EMPLOYEE_TAB does not exist

SQL> drop type employee_obj;

Type dropped

Executed in 0,047 seconds

SQL> CREATE OR REPLACE TYPE employee_obj IS OBJECT
  2  (
  3    full_name       VARCHAR2(64),
  4    department_name VARCHAR2(32),
  5    job_name        VARCHAR2(32)
  6  );
  7  /

Type created

Executed in 0,031 seconds

SQL> CREATE OR REPLACE TYPE employee_tab IS TABLE OF employee_obj;
  2  /

Type created

Executed in 0,031 seconds

SQL> DECLARE
  2    all_employees employee_tab;
  3  BEGIN
  4    SELECT e.first_name || ' ' || e.last_name, d.department_name, j.job_title BULK COLLECT
  5      INTO all_employees
  6      FROM employees e, departments d, jobs j
  7     WHERE e.department_id = d.department_id
  8       AND e.job_id = j.job_id;
  9    dbms_output.put_line('Employees Count : ' || all_employees.COUNT);
 10  END;
 11  /

DECLARE
  all_employees employee_tab;
BEGIN
  SELECT e.first_name || ' ' || e.last_name, d.department_name, j.job_title BULK COLLECT
    INTO all_employees
    FROM employees e, departments d, jobs j
   WHERE e.department_id = d.department_id
     AND e.job_id = j.job_id;
  dbms_output.put_line('Employees Count : ' || all_employees.COUNT);
END;

ORA-06550: line 6, column 5:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

SQL> DECLARE
  2    all_employees employee_tab;
  3  BEGIN
  4    SELECT employee_obj(e.first_name || ' ' || e.last_name, d.department_name, j.job_title) BULK COLLECT
  5      INTO all_employees
  6      FROM employees e, departments d, jobs j
  7     WHERE e.department_id = d.department_id
  8       AND e.job_id = j.job_id;
  9    dbms_output.put_line('Employees Count : ' || all_employees.COUNT);
 10  END;
 11  /

Employees Count : 106

PL/SQL procedure successfully completed

Executed in 0 seconds

SQL>

Performance Tests On Iterating Collections In Oracle

There are some ways to iterate collections on Oracle. One is simple for loop, the other is FIRST-NEXT method. On performance metrics it is better to use for loop, it gains time. FIRST-NEXT method is simply linked list. You take first index and then you take the next index.

SQL>
SQL> DECLARE
  2    TYPE occupation_table IS TABLE OF VARCHAR2(16);
  3    occupations occupation_table;
  4  BEGIN
  5    occupations := occupation_table('Salesman', 'Student', 'Engineer', 'Teacher', 'Architect');
  6    FOR j IN 1 .. 1000000 LOOP
  7      FOR i IN occupations.FIRST .. occupations.LAST LOOP
  8        NULL;
  9      END LOOP;
 10    END LOOP;
 11  END;
 12  /

PL/SQL procedure successfully completed

Executed in 0,172 seconds

SQL>
SQL> DECLARE
  2    TYPE occupation_table IS TABLE OF VARCHAR2(16);
  3    occupations occupation_table;
  4    k           NUMBER;
  5  BEGIN
  6    occupations := occupation_table('Salesman', 'Student', 'Engineer', 'Teacher', 'Architect');
  7    FOR j IN 1 .. 1000000 LOOP
  8      k := occupations.FIRST;
  9      WHILE k IS NOT NULL LOOP
 10        k := occupations.NEXT(k);
 11      END LOOP;
 12    END LOOP;
 13  END;
 14  /

PL/SQL procedure successfully completed

Executed in 1,359 seconds

There are some situations you can not use simple for loop. When you delete in collection or collection subscripts are not incremented sequentially by one you have to use second way. And if your subscripts are not number, you again use second way.


SQL> DECLARE
  2    TYPE occupation_table IS TABLE OF VARCHAR2(16);
  3    occupations occupation_table;
  4  BEGIN
  5    occupations := occupation_table('Salesman', 'Student', 'Engineer', 'Teacher', 'Architect');
  6    occupations.DELETE(2);
  7    FOR i IN occupations.FIRST .. occupations.LAST LOOP
  8      dbms_output.put_line('occupations(' || i || ') is ' || occupations(i));
  9    END LOOP;
 10  END;
 11  /

occupations(1) is Salesman

DECLARE
  TYPE occupation_table IS TABLE OF VARCHAR2(16);
  occupations occupation_table;
BEGIN
  occupations := occupation_table('Salesman', 'Student', 'Engineer', 'Teacher', 'Architect');
  occupations.DELETE(2);
  FOR i IN occupations.FIRST .. occupations.LAST LOOP
    dbms_output.put_line('occupations(' || i || ') is ' || occupations(i));
  END LOOP;
END;
ORA-01403: no data found
ORA-06512: at line 8

SQL> DECLARE
  2    TYPE occupation_table IS TABLE OF VARCHAR2(16);
  3    occupations occupation_table;
  4    k           NUMBER;
  5  BEGIN
  6    occupations := occupation_table('Salesman', 'Student', 'Engineer', 'Teacher', 'Architect');
  7    occupations.DELETE(2);
  8    k := occupations.FIRST;
  9    WHILE k IS NOT NULL LOOP
 10      dbms_output.put_line('occupations(' || k || ') is ' || occupations(k));
 11      k := occupations.NEXT(k);
 12    END LOOP;
 13  END;
 14  /

occupations(1) is Salesman
occupations(3) is Engineer
occupations(4) is Teacher
occupations(5) is Architect

PL/SQL procedure successfully completed

Executed in 0,015 seconds

SQL>

Handling Iteration Variable In FORALL Statements

When doing bulk insertion it is very powerful to use FORALL. Advantages of FORALL is minimize switching PL/SQL engine and database. But it has some restrictions. You can not use iteration variable inside FORALL and can not reference fields of a collection. And you can not use iteration variable inside FORALL.

Let's give a demonstrating to this.

First creating a table

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as HR


SQL>
SQL> drop table coll;

Table dropped

SQL> create table coll(i number, z number);

Table created

Attempt to use iteration variable:

SQL> DECLARE
  2    TYPE t_number IS TABLE OF NUMBER;
  3    numbers t_number;
  4  BEGIN
  5    numbers := t_number();
  6    numbers.EXTEND(10);
  7    FOR i IN 1 .. 10 LOOP
  8      numbers(i) := i;
  9    END LOOP;
 10    FORALL i IN numbers.FIRST .. numbers.LAST
 11      INSERT INTO coll VALUES (i, numbers(i));
 12  END;
 13  /

DECLARE
  TYPE t_number IS TABLE OF NUMBER;
  numbers t_number;
BEGIN
  numbers := t_number();
  numbers.EXTEND(10);
  FOR i IN 1 .. 10 LOOP
    numbers(i) := i;
  END LOOP;
  FORALL i IN numbers.FIRST .. numbers.LAST
    INSERT INTO coll VALUES (i, numbers(i));
END;

ORA-06550: line 11, column 30:
PLS-00430: FORALL iteration variable I is not allowed in this context

Second attempt to use iteration variable:

SQL> DECLARE
  2    TYPE t_number IS TABLE OF NUMBER;
  3    numbers t_number := t_number();
  4  BEGIN
  5    numbers.EXTEND(10);
  6    FOR i IN 1 .. 10 LOOP
  7      numbers(i) := i;
  8    END LOOP;
  9    FORALL i IN numbers.FIRST .. numbers.LAST
 10      INSERT INTO coll VALUES (  nvl(i,-99), numbers(i) );
 11  END;
 12  /

DECLARE
  TYPE t_number IS TABLE OF NUMBER;
  numbers t_number := t_number();
BEGIN
  numbers.EXTEND(10);
  FOR i IN 1 .. 10 LOOP
    numbers(i) := i;
  END LOOP;
  FORALL i IN numbers.FIRST .. numbers.LAST
    INSERT INTO coll VALUES (  nvl(i,-99), numbers(i) );
END;

ORA-06550: line 10, column 36:
PLS-00430: FORALL iteration variable I is not allowed in this context

To clarify iteration value is NULL;

SQL> DECLARE
  2    TYPE t_number IS TABLE OF NUMBER;
  3    numbers t_number := t_number();
  4  BEGIN
  5    numbers.EXTEND(10);
  6    FOR i IN 1 .. 10 LOOP
  7      numbers(i) := i;
  8    END LOOP;
  9    FORALL i IN numbers.FIRST .. numbers.LAST
 10      INSERT INTO coll VALUES (( SELECT nvl(i,-99) FROM dual), numbers(i));
 11  END;
 12  /

PL/SQL procedure successfully completed

SQL> SELECT * FROM coll;

         I          Z
---------- ----------
       -99          1
       -99          2
       -99          3
       -99          4
       -99          5
       -99          6
       -99          7
       -99          8
       -99          9
       -99         10

10 rows selected

To handle this situation it is possible to declare a colleciton:

SQL> ROLLBACK;

Rollback complete

SQL> DECLARE
  2    TYPE t_number IS TABLE OF NUMBER;
  3    numbers  t_number := t_number();
  4    numbers2 t_number := t_number();
  5  BEGIN
  6    numbers.EXTEND(10);
  7    numbers2.EXTEND(10);
  8    FOR i IN 1 .. 10 LOOP
  9      numbers(i) := i;
 10      numbers2(i) := i + 100;
 11    END LOOP;
 12    FORALL i IN numbers.FIRST .. numbers.LAST
 13      INSERT INTO coll VALUES (numbers2(i), numbers(i));
 14  END;
 15  /

PL/SQL procedure successfully completed

SQL> SELECT * FROM coll;

         I          Z
---------- ----------
       101          1
       102          2
       103          3
       104          4
       105          5
       106          6
       107          7
       108          8
       109          9
       110         10

10 rows selected


Lastly, i want to show other restiriction of FORALL

SQL> DECLARE
  2    TYPE t_number_rec IS RECORD(
  3      i NUMBER,
  4      z NUMBER);
  5    TYPE t_number_tab IS TABLE OF t_number_rec;
  6    numbers t_number_tab := t_number_tab();
  7  BEGIN
  8    numbers.EXTEND(10);
  9    FOR i IN 1 .. 10 LOOP
 10      numbers(i).i := i;
 11      numbers(i).z := i + 100;
 12    END LOOP;
 13    FORALL i IN numbers.FIRST .. numbers.LAST
 14      INSERT INTO coll VALUES (numbers(i).i, numbers(i).z);
 15  END;
 16  /

DECLARE
  TYPE t_number_rec IS RECORD(
    i NUMBER,
    z NUMBER);
  TYPE t_number_tab IS TABLE OF t_number_rec;
  numbers t_number_tab := t_number_tab();
BEGIN
  numbers.EXTEND(10);
  FOR i IN 1 .. 10 LOOP
    numbers(i).i := i;
    numbers(i).z := i + 100;
  END LOOP;
  FORALL i IN numbers.FIRST .. numbers.LAST
    INSERT INTO coll VALUES (numbers(i).i, numbers(i).z);
END;

ORA-06550: line 14, column 30:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
ORA-06550: line 14, column 30:
PLS-00382: expression is of wrong type
ORA-06550: line 14, column 44:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
ORA-06550: line 14, column 44:
PLS-00382: expression is of wrong type
ORA-06550: line 14, column 30:
PL/SQL: ORA-22806: not an object or REF
ORA-06550: line 14, column 5:
PL/SQL: SQL Statement ignored