BULK COLLECT INTO clause (PL/SQL)

A SELECT INTO statement with the optional BULK COLLECT keywords preceding the INTO keyword retrieves multiple rows into an array.

Syntax

Description

BULK COLLECT INTO array-variable

Identifies one or more variables with an array data type. Each row of the result is assigned to an element in each array in the order of the result set, with the array index assigned in sequence.

If exactly one array-variable is specified:
- If the data type of the array-variable element is not a record type, the SELECT list must have exactly one column, and the column data type must be assignable to the array element data type.
- If the data type of the array-variable element is a record type, the SELECT list must be assignable to the record type.
If multiple array variables are specified:
- The data type of the array-variable element must not be a record type.
- There must be an array-variable for each column in the SELECT list.
- The data type of each column in the SELECT list must be assignable to the array element data type of the corresponding array-variable.

If the data type of array-variable is an ordinary array, the maximum cardinality must be greater than or equal to the number of rows that are returned by the query.

LIMIT expression

Provides an upper bound for how many rows are being fetched. The expression can be a numeric literal, a variable, or a complex expression, but it cannot depend on any column from the select statement.

Notes

Variations of the BULK COLLECT INTO clause are also supported with the FETCH statement and the EXECUTE IMMEDIATE statement.

Example

The following example shows a procedure that uses the BULK COLLECT INTO clause to return an array of rows from the procedure. The procedure and the type for the array are defined in a package.

CREATE OR REPLACE PACAKGE bci_sample
IS
 TYPE emps_array IS VARRAY (30) OF VARCHAR2(6);

 PROCEDURE get_dept_empno (
  dno       IN   emp.deptno%TYPE,
  emps_dno  OUT  emps_array
  );
END bci_sample;

CREATE OR REPLACE PACKAGE BODY bci_sample
IS
 PROCEDURE get_dept_empno (
  dno       IN   emp.deptno%TYPE,
  emps_dno  OUT  emps_array
  )
 IS
  BEGIN
   SELECT empno BULK COLLECT INTO emps_dno
    FROM emp
    WHERE deptno=dno;
  END get_dept_empno;
END bci_sample;