Bulk collect into multiple columns

Question

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

Answer 1

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>