Tuesday, 6 September 2016

Using the TABLE Operator with Locally Defined Types in PL/SQL

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
  • Associative Array Example
  • Nested Table Example
  • Varray Example
  • Supported Types
  • Location of Type Definition

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

All examples use the EMP table from the SCOTT schema. If it is not present you can install the schema using the "$ORACLE_HOME/rdbms/admin/utlsampl.sql" script on the server, or build the tables in your local schema using the script below.

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;

2. Associative Array Example


This example uses an associative array based on a %ROWTYPE defined in the package specification.

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

  PROCEDURE test1;
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

  PROCEDURE test1 IS
    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;

END;
/

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

PL/SQL procedure successfully completed.

SQL>

3. Nested Table Example


This example uses a nested table based on a %ROWTYPE defined in the package specification.

CREATE OR REPLACE PACKAGE test_api AS
  TYPE t_tab IS TABLE OF emp%ROWTYPE;

  PROCEDURE test1;
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

  PROCEDURE test1 IS
    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;

END;
/

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

PL/SQL procedure successfully completed.

SQL>

4. Varray Example


This example uses a varray based on a %ROWTYPE defined in the package specification.

CREATE OR REPLACE PACKAGE test_api AS
  TYPE t_tab IS VARRAY(10) OF emp%ROWTYPE;

  PROCEDURE test1;
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

  PROCEDURE test1 IS
    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;

END;
/

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

PL/SQL procedure successfully completed.

SQL>

5. Supported Types


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

PL/SQL procedure successfully completed.

SQL>

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

PL/SQL procedure successfully completed.

SQL>

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

PL/SQL procedure successfully completed.

SQL>


6. Location of Type Definition


For this new functionality to work, the type must be defined in package specification. The examples below show how alternate locations fail.

The following example moves the type definition to the package body, which results in a compilation failure.

CREATE OR REPLACE PACKAGE test_api AS
  PROCEDURE test1;
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

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

  PROCEDURE test1 IS
    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;

END;
/
Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY TEST_API:

LINE/COL ERROR
-------- -----------------------------------------------------------------
15/21    PL/SQL: SQL Statement ignored
16/28    PL/SQL: ORA-22905: cannot access rows from a non-nested table
             item

16/34    PLS-00382: expression is of wrong type
18/7      PL/SQL: Statement ignored
18/28    PLS-00364: loop index variable 'CUR_REC' use is invalid
SQL>

The following example attempts to use the functionality in an anonymous block, which results in an error.

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

SQL>