A construct within Oracle contains a very interesting pointer structure that allows a single cell in an entity to contain a whole other entity. Oracle calls these nested tables. They are called nested tables because it provides the illusion that one table has another table nested inside it.
A nested table is a pointer structure. In the example shown, the emp column in the deptnest table contains a nested table.
While this appears to be a recursive structure, in reality Oracle is using an OID to point to this single subordinate table or store table called emp_name
The store table also has an OID back to each row of the owner table.
In theory, a nested table should get the data from the store table faster than a traditional Oracle7 data model where a JOIN would be required to access the data.
Nesting objects within other objects
In this way it is possible to create a structure where objects or tables may be nested within other objects or tables.
For an object/relational database, this means that a single column value in a table may contain a whole table.
These sub-table tables, in turn, may have single column values that point to whole tables, and so on, ad infinitum. The following diagram below comments on this basic code.
Before moving on to the next lesson, click the link below to read about nested tables and pointers. Oracle Data Constructs
Understanding Variable-Size Arrays (Varrays)
A variable-size array (varray) is an item of the data type VARRAY.
A varray has a maximum size, which you specify in its type definition
Can contain a varying number of elements, from zero (when empty) to the maximum size.
A varray index has a fixed lower bound of 1 and an extensible upper bound.
To access an element of a varray, you use standard subscripting syntax.
Figure 5-1 shows a varray named Grades, which has maximum size 10 and contains seven elements.
The current upper bound for Grades is 7, but you can increase it to the maximum of 10.
Grades(n) references the nth element of Grades.