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>