oracle-databaseplsqlbulk-collect

Bulk collect into multiple columns


I can define a one-column table and bulk collect into it. i.e:

create type table_of_strings as table of varchar2(200);

DECLARE
    l_tab table_of_strings;
BEGIN
    
    select emp_name bulk collect into l_tab from emp;

END;

But how do I collect into multi-column tables? Say:

create type emp_row as object (emp_name varchar2(200), emp_salary Number);
create type emp_table as table of emp_row ;

DECLARE
    l_tab emp_table ;
BEGIN
    -- I have tried things like this but would fail:
    select (emp_name, emp_salary) bulk collect into l_tab from emp;
    select emp_name, emp_salary bulk collect into l_tab from emp;
    select * bulk collect into l_tab from (
         select emp_name, emp_salary  from emp);

END;

Thank you in advance! Peter


Solution

  • Like this:

    select emp_row(emp_name, emp_salary) bulk collect into l_tab from emp;
    

    Here's a slightly modified example based on Scott's schema.

    SQL> CREATE TYPE emp_row AS OBJECT (emp_name VARCHAR2 (200), emp_salary NUMBER);
      2  /
    
    Type created.
    
    SQL> CREATE TYPE emp_table AS TABLE OF emp_row;
      2  /
    
    Type created.
    
    SQL> SET SERVEROUTPUT ON
    

    PL/SQL code:

    SQL> DECLARE
      2     l_tab      emp_table;
      3     l_max_sal  NUMBER;
      4  BEGIN
      5     SELECT emp_row (ename, sal)
      6       BULK COLLECT INTO l_tab
      7       FROM emp
      8      WHERE deptno = 10;
      9
     10     FOR i IN l_tab.FIRST .. l_tab.LAST
     11     LOOP
     12        DBMS_OUTPUT.put_line (
     13           l_tab (i).emp_name || ' - ' || l_tab (i).emp_salary);
     14     END LOOP;
     15
     16     SELECT MAX (emp_salary) INTO l_max_sal FROM TABLE (l_tab);
     17
     18     DBMS_OUTPUT.put_line ('Max salary = ' || l_max_sal);
     19  END;
     20  /
    CLARK - 2450
    KING - 5000
    MILLER - 1300
    Max salary = 5000
    
    PL/SQL procedure successfully completed.
    
    SQL>