16.5 How do I…Create and use the nested table collection type?

Problem

I want to model master-detail relationships within my database using collection objects, but for some of these relationships I don’t know how many detail elements there will be, and for others there will be a large number of detail elements. I know that VARRAYs are not helpful in these situations. How do I implement nested tables in my database and do I insert and extract data from them?

Technique

Chapter 9 included a nested table definition to help explain unused segment space. This How-To revisits and more closely examines this definition. Nested table definitions start with an object table type definition and conclude by defining a relational table with an embedded object type (see How-To 16.3 for more information about object tables). Nested tables are similar to VARRAYs in that DML commands on them involve some modifications to traditional syntax; the THE keyword is an important new operator for manipulating data residing in nested tables.

Steps

1. The script CHP16_14.SQL contains DDL to define a nested table. Start SQL*Plus, connect as the WAITE user, and use the START command to load and execute this script. The script and its output appear in Figure 16.14.

The first CREATE statement in the script defines an object type, EMP_TYPE16_5, which acts as a template for the employee information the database must store. The second CREATE statement defines an object table type comprised of objects of the EMP_TYPE16_5 type. The final and most interesting CREATE command defines a relational table with an embedded column called EMPS to contain a nested table.

2. Load and execute the script CHP16_15.SQL to insert two rows into the DEPT16_5 table as shown in Figure 16.15.

The insert statements use nested constructor methods. As in earlier examples, Oracle8 defines these constructors for us. The outer constructor, EMP_TABLE16_5, instantiates an object of the nested table type. The inner constructor, EMP_TYPE16_5 instantiates an employee object. In this case, the inner constructor is instantiating an employee object to store in the nested object table called EMPS.

3. Query the rows of the nested table EMPS by using the START command to load and execute the script CHP16_16.SQL. The script and its output appear in Figure 16.16.

The query in Figure 16.15 returns the employee numbers and names for the staff members in the janitorial department. The THE keyword prefixing the subquery beginning in line 2 indicates to Oracle that the subquery’s result is a nested table and not a scalar value.

How It Works

The DDL statements in Step 1 set up the nested table structure by creating an employee object type and an object table type. The CREATE TABLE statement in Step 1 uses these definitions to create a relational table with a column containing a nested table reference. Step 2 uses constructors to populate the relational table and the nested table. Step 3 uses subquery syntax and the THE operator, new in Oracle8, to query the nested table.

Comments

Nested tables are stored in different data segments than the data from the main table (this is called out-of-line storage). Unlike VARRAYs, the amount of data that can reside in a nested table is limited only by available logical and physical space. Nested tables do accommodate SELECT statements in the form appearing in Step 3.

Subqueries prefaced by the THE keyword are sometimes called flattened subqueries to indicate that they return multiple rows from the nested table for a single row in the main table. Although Oracle8 selects this level of SELECT functionality, it is necessary to write a PL/SQL procedure to fashion a query returning columns from the main table and columns from the nested table or to create a subquery returning more than a single column value from the nested table.

Under Oracle8 version 8.02 beta for Windows NT, if you apply the familiar SELECT * construct to a table containing a nested table, you will generate a SQL*Plus internal error.