DBA Hub

📋Steps in this guide1/6

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.

oracle 12cconfigurationintermediate
by OracleDba
14 views
1

Setup

The examples in this article follow the same pattern. They populate a collection, then instantly select from the collection using the operator, and print out the contents. The code is merely to demonstrate the fact the local collection is accessible using the 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 table from the 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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 defined in the package specification.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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 defined in the package specification.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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 defined in the package specification.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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 of a schema table for the collection type. It is also possible to use , a scalar and a local record type. The following example uses an associated array based on a . Notice the column is referenced as . The following example uses an associated array based on a scalar. Notice the column is referenced as . The following example uses an associated array based on a local record type.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
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>

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>

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. The following example attempts to use the functionality in an anonymous block, which results in an error. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
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>

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!