Oracle PL/SQL Programming: A Developer's Workbook

Chapter 7. Nested Tables

In addition to simply renaming PL/SQL tables to index-by tables in Oracle8, Oracle introduced two new collection datatypes: nested arrays and variable arrays. While conceptually similar to index-by tables, the two new collection types are usable in SQL statements (for example, you can insert multiple rows of a collection into a table via Oracle 8i Release 8.1’s bulk collection operators) and may be stored as columns in a database table. This chapter tests your understanding of nested tables, the first of the new collection types. Specifically, it examines your ability to create a nested table type, store the array in a “store table,” add and delete elements, and traverse each element.

Beginner

7-1.	What SQL syntax is introduced in Oracle8 to create nested table types?
7-2.	True or false? Nested table types can be used as datatypes for columns in Oracle tables. There is no limit to the number of rows that can be stored in a nested table type. Different rows in a nested table type can contain different types of information. Row 1, for example, might contain numeric data while Row 2 contains a string. Nested table types are stored in the database. Nested table types can be created based only on Oracle’s native datatypes such as VARCHAR2 and NUMBER.
7-3.	Fixit Later is the System Architect at Simplistic Procurement Systems, and he has executed the following DDL to create a new system: SQL> CREATE TYPE detail_t AS OBJECT ( detail_no PLS_INTEGER, 2 detail_desc VARCHAR2(30), ...