Using the TABLE Operator over Collections

In Oracle 12c, the TABLE operator can now be used in PL/SQL with locally defined types. In previous releases, the TABLE operator would only work with locally defined types if they were used within pipelined table functions.
The removal of this restriction means this functionality is available for use with associative arrays, as well as nested tables and varrays in PL/SQL.


Setup
The examples in this article follow the same pattern. They populate a collection, then instantly select from the collection using the TABLE operator, and print out the contents. The code is merely to demonstrate the fact the local collection is accessible using the TABLE operator. It is not suggesting it is sensible to populate the collection then instantly select from it to process the data.

CREATE TABLE DEPT (
  DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
  DNAME VARCHAR2(14),
  LOC VARCHAR2(13)
) ;

CREATE TABLE EMP (
  EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;



CREATE OR REPLACE PACKAGE test_collections AS
  --Associative Array Example
  TYPE t_tab_aa IS TABLE OF emp%ROWTYPE
    INDEX BY BINARY_INTEGER;

    --Nested Table Example
  TYPE t_tab_NT IS TABLE OF emp%ROWTYPE;

  --Varray Example
  TYPE t_tab_VARRAY IS VARRAY(10) OF emp%ROWTYPE;

  PROCEDURE Test_Associat_Array;
  PROCEDURE Test_Nested_Table;
  PROCEDURE Test_Varray;
END;
/

CREATE OR REPLACE PACKAGE BODY test_collections AS

  PROCEDURE Test_Associat_Array IS
    l_tab1 t_tab_aa;
  BEGIN
    SELECT *
    BULK COLLECT INTO l_tab1
    FROM   emp
    WHERE  deptno = 10;

    DBMS_OUTPUT.put_line('Loop Through Associative Array');
    FOR cur_rec IN (SELECT *
                    FROM   TABLE(l_tab1))
    LOOP
      DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
    END LOOP;
  END;

  PROCEDURE Test_Nested_Table IS
    l_tab1 t_tab_NT;
  BEGIN
    SELECT *
    BULK COLLECT INTO l_tab1
    FROM   emp
    WHERE  deptno = 10;

    DBMS_OUTPUT.put_line('Loop Through Nested Table');
    FOR cur_rec IN (SELECT *
                    FROM   TABLE(l_tab1))
    LOOP
      DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
    END LOOP;
  END; 
 
  PROCEDURE Test_Varray IS
    l_tab1 t_tab_VARRAY;
  BEGIN
    SELECT *
    BULK COLLECT INTO l_tab1
    FROM   emp
    WHERE  deptno = 10;

    DBMS_OUTPUT.put_line('Loop Through Varray');
    FOR cur_rec IN (SELECT *
                    FROM   TABLE(l_tab1))
    LOOP
      DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
    END LOOP;
  END;

END;
/

EXEC test_collections.Test_Associat_Array;
EXEC test_collections.Test_Nested_Table;
EXEC test_collections.Test_Varray;

EXEC test_collections.Test_Associat_Array;
Loop Through Associative Array
7782 : CLARK
7839 : KING
7934 : MILLER

EXEC test_collections.Test_Nested_Table;
Loop Through Nested Table
7782 : CLARK
7839 : KING
7934 : MILLER


EXEC test_collections.Test_Varray;
Loop Through Varray
7782 : CLARK
7839 : KING
7934 : MILLER


The previous examples all use a %ROWTYPE of a schema table for the collection type. It is also possible to use %TYPE, a scalar and a local record type.
The following example uses an associated array based on a %TYPE. Notice the column is referenced as COLUMN_VALUE.

CREATE OR REPLACE PACKAGE test_api AS
  TYPE t_tab IS TABLE OF emp.empno%TYPE
    INDEX BY BINARY_INTEGER;

  PROCEDURE test1;
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

  PROCEDURE test1 IS
    l_tab1 t_tab;
  BEGIN
    SELECT empno
    BULK COLLECT INTO l_tab1
    FROM   emp
    WHERE  deptno = 10;

    DBMS_OUTPUT.put_line('Loop Through Collection');
    FOR cur_rec IN (SELECT *
                    FROM   TABLE(l_tab1))
    LOOP
      DBMS_OUTPUT.put_line(cur_rec.column_value);
    END LOOP;
  END;

END;
/

SET SERVEROUTPUT ON
EXEC test_api.test1;
Loop Through Collection
7782
7839
7934



The following example uses an associated array based on a scalar. Notice the column is referenced as COLUMN_VALUE.
CREATE OR REPLACE PACKAGE test_api AS
  TYPE t_tab IS TABLE OF NUMBER
    INDEX BY BINARY_INTEGER;

  PROCEDURE test1;
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

  PROCEDURE test1 IS
    l_tab1 t_tab;
  BEGIN
    SELECT empno
    BULK COLLECT INTO l_tab1
    FROM   emp
    WHERE  deptno = 10;

    DBMS_OUTPUT.put_line('Loop Through Collection');
    FOR cur_rec IN (SELECT *
                    FROM   TABLE(l_tab1))
    LOOP
      DBMS_OUTPUT.put_line(cur_rec.column_value);
    END LOOP;
  END;

END;
/

SET SERVEROUTPUT ON
EXEC test_api.test1;
Loop Through Collection
7782
7839
7934




The following example uses an associated array based on a local record type.

CREATE OR REPLACE PACKAGE test_api AS
  TYPE t_row IS RECORD (
    empno NUMBER(4),
    ename VARCHAR2(10)
  );
 
  TYPE t_tab IS TABLE OF t_row
    INDEX BY BINARY_INTEGER;

  PROCEDURE test1;
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

  PROCEDURE test1 IS
    l_tab1 t_tab;
  BEGIN
    SELECT empno, ename
    BULK COLLECT INTO l_tab1
    FROM   emp
    WHERE  deptno = 10;

    DBMS_OUTPUT.put_line('Loop Through Collection');
    FOR cur_rec IN (SELECT *
                    FROM   TABLE(l_tab1))
    LOOP
      DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
    END LOOP;
  END;

END;
/

SET SERVEROUTPUT ON
EXEC test_api.test1;
Loop Through Collection
7782 : CLARK
7839 : KING
7934 : MILLER


NOTE!!
For this new functionality to work, the type must be defined in package specification, if you declare the type under the Package Body, it will fail.
This will also fail in a PL/SQL Block:

DECLARE
  TYPE t_tab IS TABLE OF emp%ROWTYPE
    INDEX BY BINARY_INTEGER;

  l_tab1 t_tab;
BEGIN
  SELECT *
  BULK COLLECT INTO l_tab1
  FROM   emp
  WHERE  deptno = 10;

  DBMS_OUTPUT.put_line('Loop Through Collection');
  FOR cur_rec IN (SELECT *
                  FROM   TABLE(l_tab1))
  LOOP
    DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
  END LOOP;
END;
/
ERROR at line 14:
ORA-06550: line 14, column 32:
PLS-00382: expression is of wrong type
ORA-06550: line 14, column 26:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 13, column 19:
PL/SQL: SQL Statement ignored
ORA-06550: line 16, column 26:
PLS-00364: loop index variable 'CUR_REC' use is invalid
ORA-06550: line 16, column 5:
PL/SQL: Statement ignored


More Information here:
https://oracle-base.com/articles/12c/using-the-table-operator-with-locally-defined-types-in-plsql-12cr1
https://oracle-base.com/articles/12c/plsql-to-sql-interface-enhancements-for-plsql-only-data-types-12cr1