8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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
Related articles.
- Oracle Pipelined Table Functions
- Collections in Oracle PL/SQL
- Bulk Binds (BULK COLLECT & FORALL) and Record Processing in Oracle
- Associative Arrays in Oracle 9i
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;
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>
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>
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>
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>
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>
For more information see:
- Oracle Pipelined Table Functions
- Collections in Oracle PL/SQL
- Bulk Binds (BULK COLLECT & FORALL) and Record Processing in Oracle
- Associative Arrays in Oracle 9i
Hope this helps. Regards Tim...