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
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>