Oracle PL/SQL - Bulk Query into Nested Tables

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Statement
  4. SELECT INTO

Introduction

You must have the same data types in both the SELECT and INTO clauses.

Demo

SQL>
SQL> drop table emp;

Table dropped.--  ww  w  .  j  av a 2s  . com

SQL> create table emp(
  2    empno    number(4,0),
  3    ename    varchar2(10),
  4    job      varchar2(9),
  5    mgr      number(4,0),
  6    hiredate date,
  7    sal      number(7,2),
  8    comm     number(7,2),
  9    deptno   number(2,0)
 10  );

Table created.
SQL>
SQL> insert into emp values(7369, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10);
SQL> insert into emp values(7499, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 10);
SQL>
SQL> declare
  2        type text_nt is table of VARCHAR2(256);
  3        type number_nt is table of NUMBER;
  4        v_eName_nt text_nt;
  5        v_deptNo_nt number_nt;
  6  begin
  7       select eName, deptNo
  8          bulk collect into v_eName_nt,v_deptNo_nt
  9          from emp
 10       where deptNo=10;
 11       DBMS_OUTPUT.put_line('Records:'||v_eName_nt.count());
 12       DBMS_OUTPUT.put_line('Fetched:'||sql%ROWCOUNT);
 13  end;
 14  /
Records:2
Fetched:2

PL/SQL procedure successfully completed.

Related Topic