Tutorial 4

4.1 About attribute and type names

There seems to be a difference between tables and types with respect to attribute names. It is not possible to create types where an attribute name is the same as the name of its type. For example,

      CREATE TYPE somename (person person);

causes an error message. But

      CREATE TABLE somename (person person);

is ok. The same problem holds for references (person REF person).

To be on the safe side, it is probably always a good idea to choose distinctive, meaningful names for attributes and types and to use different names for types, tables and attributes.

4.2 Collection Types

Oracle supports two collection datatypes: varrays and nested tables. Varrays are variable-length ordered lists (or arrays). A maximum size of the varray must be specified but can be changed later. Nested tables are tables within tables. In contrast to varrays, nested tables are unordered lists. Varrays and nested tables are defined differently but very similar to use.

For example, people can have several phone numbers. Different categories of phone numbers, such as mobile phone versus home phone, should be stored in different columns. But different numbers of the same category (e.g., if someone has two mobile phones) could be stored in a single column in a varray or nested table.

Using varrays, a list of phone numbers can be stored in a column "phone" of a table company1. Assuming that no company would have more than 10 numbers, the varray is limited to 10 elements (VARRAY(10)).

      CREATE TYPE phone_array AS VARRAY(10) OF VARCHAR2(12);
      CREATE TABLE company1 (
            name VARCHAR2(20),
            phone phone_array
      INSERT INTO company1 VALUES (
            'abc', phone_array('243-4758','485-2534')


The following code shows the same example but uses a nested table. In the nested table, there is no limit of 10 numbers. But it has to be declared that the column "phone" of "company2" is a nested table. A storage table phone_nr_table, which stores the actual values, must be named (although this table cannot be used for anything). In this case, phone_nested is a table of just one column (VARCHAR). But types with multiple columns can also be nested (see Exercise 25).

      CREATE TYPE phone_nested AS TABLE OF VARCHAR2(12);
      CREATE TABLE company2 (
            name VARCHAR2(20),
            phone phone_nested
            NESTED TABLE phone STORE AS phone_nr_table;
      INSERT into company2 VALUES (
            'abc', phone_nested('243-4758','485-2534')



4.3 Multi-level collection types and other advanced features

Optional Exercise:

4.4 Select statements for nested tables

There are two types of queries: queries that retrieve objects in a nested format showing all their types and queries that show the data in an un-nested format (just the data, not the types).

The following examples work for both company1 (using varrays) and company2 (using nested tables). To select all data and types:

      SELECT * from company1;

The SQL function TABLE() un-nests the nested table. In the following example, "c" is an alias for company1, "c.phone" is the nested table in company1. The un-nested c.phone table is assigned its own alias "t".

      SELECT c.name, t.* from company1 c, TABLE(c.phone) t;

An equivalent version is

      SELECT name, t.* from company1, TABLE(SELECT phone FROM company1) t;

Oracle provides a default column name, COLUMN_VALUE, for the column of a nested table that does not have a name:

      SELECT t.COLUMN_VALUE from company1 c, TABLE (c.phone) t;