Learning Objectives – after this chapter you will be able to:
- Explain collections and the three collection types: index-by tables, nested tables and VARRAYs.
- Create and use VARRAY types in tables and PL/SQL.
- Create nested-table columns with the NESTED TABLE … STORE AS clause.
- Use DML on nested tables as atomic values and, with the TABLE operator, as rows.
- Use collection methods – EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, NEXT, EXTEND, TRIM, DELETE.
What is a Collection?
A collection is a group of values that are all of the same type – like a single-dimension array. Oracle provides index-by tables, nested tables and VARRAYs. A table column, an object attribute, or the elements of another collection can be of a collection type (including collections of object types).
VARRAY
VARRAY = variable-size array: it has a maximum size and holds 0 to that many elements. It is stored in-line (with the rest of the row) and can be used for a table column, a PL/SQL variable, a procedure/function parameter, a function return type and an object attribute.
CREATE TYPE array_name AS VARRAY(limit) OF data_type;Creating and using a VARRAY
create type project_type as object
( name varchar2(50),
role varchar2(20) );
/
create type projectlist as varray(5) of project_type;
/
create table emp
( empno number(5),
ename varchar2(30),
projects projectlist );
insert into emp values (1, 'Ellison',
projectlist( project_type('Telephone Billing', 'System Analyst'),
project_type('Housing Loans', 'Oracle DBA') ));
select projects from emp where empno = 1; -- whole VARRAY
select projects(1) from emp where empno = 1; -- ORA-00904: not allowed in SQL- Insert with the collection constructor, and the object constructor for each element.
- In SQL a VARRAY is always handled as a single value – to change one element, update the whole VARRAY. Individual elements cannot be referenced in INSERT, UPDATE or DELETE.
- In PL/SQL, elements are reachable with subscripts and collection methods:
Adding an element through PL/SQL
create or replace procedure new_project (p_empno number, p_projname varchar2, p_role varchar2) is pl projectlist; begin select projects into pl from emp where empno = p_empno; pl.extend; -- room for one more element pl(pl.last) := project_type(p_projname, p_role); -- set the new last element update emp set projects = pl where empno = p_empno; -- write the VARRAY back end;
Nested Table
A nested table is a table stored in the database as the data of a column. It is like an index-by table, but can be stored in the database (an index-by table cannot) and allows SELECT, INSERT, UPDATE and DELETE on its rows. Its data is stored out-of-row in a separate storage table named in the NESTED TABLE … STORE AS clause, which Oracle creates and maintains.
Creating a nested-table column
create type projecttable as table of project_type;
/
create table emp
( empno number(5),
ename varchar2(30),
projects projecttable )
nested table projects store as projects_nt;
insert into emp values (1, 'Ellison',
projecttable( project_type('Telephone Billing', 'System Analyst'),
project_type('Housing Loans', 'Oracle DBA') ));Calling the table constructor with no arguments creates an empty table, which is not equal to NULL.
DML on nested tables
- As an atomic value – the whole nested table is read or assigned:
update emp set projects = pl where empno = 3;,delete from emp where projects is null; - As a table – the TABLE operator takes a nested-table (or VARRAY) value and supplies its data as rows:
The TABLE operator
select * from table ( select projects from emp where empno = 1 );
select name from table ( select projects from emp where empno = 1 )
where role = 'Oracle DBA';
insert into table ( select projects from emp where empno = 1 )
values ('BillsOnline.com', 'Web Developer');
update table ( select projects from emp where empno = 1 )
set role = 'Project Leader' where name = 'Telephone Billing';
delete from table ( select projects from emp where empno = 1 )
where name = 'BillsOnline.com';| VARRAY | Nested table | |
|---|---|---|
| Size | Maximum limit | No limit given (LIMIT returns NULL) |
| Storage | In-line, with the row | Out-of-row, in the STORE AS table |
| Individual elements in SQL DML | Not possible – update the whole array | Possible with TABLE( … ) |
| Deleting middle elements | – | Possible; leaves gaps (LAST may exceed COUNT) |
Collection Methods
Used only in PL/SQL (not SQL) as collection_name.method[(parameters)].
| Method | Meaning |
|---|---|
| EXISTS(n) | TRUE if the nth element exists |
| COUNT | Number of elements currently in the collection |
| LIMIT | Maximum size for a VARRAY; NULL for a nested table |
| FIRST / LAST | Smallest / largest index (NULL if empty). For a VARRAY FIRST is always 1 and LAST = COUNT; for a nested table they may differ after deletions |
| PRIOR(n) / NEXT(n) | Index before / after n (NULL if none) – useful to traverse a nested table with deleted items |
| EXTEND / EXTEND(n) | Append one / n null elements |
| TRIM(n) | Remove element(s) from the end |
| DELETE / DELETE(n) / DELETE(m,n) | Remove all / the nth / elements m to n |
Using collection methods
declare
pl projecttable := projecttable( project_type('payroll', 'designer') );
begin
pl.extend(2); -- now 3 elements
pl(2) := project_type('inventory', 'network adm');
pl(3) := project_type('law.com', 'web developer');
pl.delete(2); -- gap at index 2
for i in pl.first .. pl.last loop
if pl.exists(i) then
dbms_output.put_line(i || ' - ' || pl(i).name);
end if;
end loop;
pl.trim(1); -- remove the last element
end;
Key Takeaways
- VARRAY: bounded, in-line, manipulated as a whole in SQL.
- Nested table: unbounded, stored out-of-row (STORE AS), rows reachable with TABLE().
- Collection methods work only in PL/SQL; COUNT and LAST can differ for nested tables after DELETE.
Chapter 23 Notes (PDF)
ch23.pdfKeys: ← → page · + − zoom · F full screen
Question / Answer (15 questions)
Moderate-level questions based on the Chapter 23 notes (PDF). Click a question to see the answer.
Answer : A collection is a group of values where all values are of the same type, like a single-dimension array. Oracle provides three types of collections – index-by tables, nested tables and VARRAYs. A table column, an object attribute and the elements of a collection can be of a collection type.
Answer : VARRAY stands for variable-size array. It has a maximum size and can contain from 0 up to that maximum number of elements, and it can be manipulated as a whole or (in PL/SQL) as individual elements. It is stored in-line, i.e. with the rest of the data of the row.
Answer : As a column in a relational table, a PL/SQL variable, a parameter of a PL/SQL procedure or function, a PL/SQL function return type, and a data attribute of an object type.
Answer : With CREATE TYPE array_name AS VARRAY(limit) OF data_type, where limit is the maximum number of elements and data_type can be any standard type or object type, e.g. CREATE TYPE projectlist AS VARRAY(5) OF project_type.
Answer : Using the constructor of the VARRAY type and, inside it, the constructor of the object type for each element, e.g. INSERT INTO emp VALUES (1, 'Ellison', projectlist(project_type('Telephone Billing', 'System Analyst'), project_type('Housing Loans', 'Oracle DBA'))).
Answer : No. In SQL a VARRAY is always manipulated as a single value – SELECT projects(1) FROM emp gives ORA-00904, and individual elements cannot be referenced in INSERT, UPDATE or DELETE; the whole VARRAY must be updated. Individual elements can be handled in PL/SQL using subscripts and collection methods.
Answer : Select the VARRAY into a variable, call EXTEND to make room for a new element, assign the new element at the last position (pl(pl.LAST) := project_type(name, role)), and update the table column with the modified variable.
Answer : A nested table is a table stored in the database as the data of a column of another table. It is like an index-by table, but a nested table can be stored in the database whereas an index-by table cannot, and it allows SELECT, DELETE, UPDATE and INSERT operations on its rows.
Answer : NESTED TABLE column STORE AS storage_table is required when the table has a nested-table column. It specifies the name of the table in which Oracle stores the data of the nested table (e.g. PROJECTS_NT); this table is created and maintained by Oracle.
Answer : The data of a VARRAY is stored as part of the row (in-line), whereas the data of a nested table is stored out-of-row in the separate table named in the NESTED TABLE clause.
Answer : No. Calling the table constructor without any parameter creates an empty table, and an empty table is not equal to NULL.
Answer : As an atomic value – the whole inner table is read or assigned, e.g. copying the projects of employee 1 to employee 3, or DELETE FROM emp WHERE projects IS NULL – or as a table whose individual rows are queried and manipulated using the TABLE operator.
Answer : TABLE takes a single operand of nested-table or VARRAY type and supplies its data as rows to the outer statement. Examples: SELECT * FROM TABLE(SELECT projects FROM emp WHERE empno = 1); INSERT INTO TABLE(SELECT projects FROM emp WHERE empno = 1) VALUES ('BillsOnline.com', 'Web Developer'); UPDATE and DELETE work the same way. Inserting, updating and deleting individual rows is not permitted for VARRAYs.
Answer : COUNT returns the number of elements currently in the collection; LIMIT returns the maximum number of elements for a VARRAY and NULL for a nested table; FIRST and LAST return the smallest and largest index (NULL if empty). For a VARRAY FIRST is always 1 and LAST equals COUNT, but for a nested table LAST may be more than COUNT if items have been deleted.
Answer : EXISTS(n) is TRUE if the nth element exists; PRIOR(n)/NEXT(n) return the preceding/following index (NULL if none) and help traverse a nested table with deleted items; EXTEND appends one null element and EXTEND(n) n elements; TRIM(n) removes elements from the end; DELETE removes all elements, DELETE(n) the nth and DELETE(m,n) elements m to n. These methods can be used only in PL/SQL, not in SQL.
MCQ Test : Check Your Understanding (5 questions)
Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.
Bibliography/References
The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.
- Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
- Oracle Corporation. (n.d.). Oracle Database PL/SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/
- Oracle Corporation. (n.d.). Oracle Database Object-Relational Developer’s Guide, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/adobj/
- Feuerstein, S., & Pribyl, B. (2014). Oracle PL/SQL Programming (6th ed.). O’Reilly Media.