Object Tables   «Prev  Next»

Lesson 6 Nested tables
Objective Determine when to use nested tables in querying.

Querying Nested Tables using PL/SQL

A nested table is a single-dimension, unbounded collection of homogenous elements. It is initially dense and can become sparse through deletions. The association of a nested table with another table is similar to a parent-child relationship. You have the option of adding or deleting records from the nested table. Therefore, when you define a nested table, you define its attributes and associate it with an object table.
When you add records to the nested table, it is considered dense. When you delete records from the nested table, it becomes sparse. The primary difference between a nested table and a varray is that a nested table is unbounded. In other words, there is no preset limit on the maximum number of elements that the table can hold. On the other hand, a varray is bounded. You must specify an upper limit that indicates how large the varray can be. After you define the upper limit, the varray is always considered dense.
The following example defines DETAIL_TABLE as a nested table, each row of which contains an object of type SALE_DETAIL.
To represent the sale details, a nested table is a better choice than a varray for the following reasons:

Nested Table is Better Choice than varray

  1. Querying the contents of sale details is likely to be a requirement for most applications. This is an inefficient operation for a varray because a varray has a fixed number of elements. On the other hand, a table can have unlimited number of elements (that is, records).
  2. Indexing on sale details data may be a requirement for some applications. A nested table allows indexing. However, this is not possible with a varray.
  3. There is no practical upper bound on the number of sale details on a sale. Using a nested table does not require specifying an upper bound; using a varray does.
The following Slide Show explains the three steps used in creating a nested table and associating it with a different table:

Oracle Any Datatypes

Most of the time, our programming tasks are fairly straightforward and very specific to the requirement at hand. At other times, however, we write more generic kinds of code. For those situations, the Any datatypes might come in very handy. The Any types were introduced in Oracle9i Database and are very different from any other kind of datatype available in an Oracle database. They let you dynamically encapsulate and access type descriptions, data instances, and sets of data instances of any other SQL type. You can use these types (and the methods defined for them, as they are object types) to do things like determine the type of data stored in a particular nested table without having access to the actual declaration of that table type. The Any datatypes include AnyType, AnyData, and AnyDataSet.

Nested Tables in Oracle

1) First, create an object SALE_DETAIL with its attributes as defined
1) 
CREATE OR REPLACE TYPE PETSTORE.SALE_DETAIL
AS OBJECT
 (PRODUCT_ID REF PETSTORE.PRODUCT_TYPE,
  ITEM QTY NUMBER(3,0)
  DETAIL_AMOUNT NUMBER(10,2)
);
First, create an object SALE_DETAIL with its attributes as defined

2) After creating the object, create a nested table using the following.
2) 
CREATE OR REPLACE TYPE PETSTORE.DETAIL_TABLE
AS TABLE OF SALE_DETAIL;
After creating the object, create a nested table using the following DDL statement:

3) After creating the nested table, create a different table and associate the nested table with it
3) 
CREATE TABLE PETSTORE.SALE_HEADER
(SALE_ID NUMBER(10,0),
  CUST_REF REF PETSTORE.CUSTOMER_TYPE,
DETAIL_TOTAL NUMBER(10,2),
TAX_AMOUNT NUMBER(10,2),
SHIPPING_AMOUNT NUMBER(10,2),
SALE_TOTAL NUMBER(10,2),
RETAIL_NEST DETAIL_TABLE
)
After creating the nested table, create a different table and associate the nested table with it, as shown within the DDL statement.

  1. First, create an object SALE_DETAIL with its attributes as defined
  2. After creating the object, create a nested table using the following DDL statement:
  3. After creating the nested table, create a different table and associate the nested table with it, as shown within the DDL statement.

We will look in more detail at querying varrays and nested tables later in the course.
In the next lesson, you will learn how to write SQL to query an object table.

Nested Table - Quiz

Click the Quiz link below to test your knowledge of object-relational concepts.
Nested Table - Quiz

PL/SQL Programming