Tutorial 2

2.1 Information about object types

As you will have noticed by now, there is no information about types in the user catalog ("select * from cat"). The following select statements can be used to list user types and their attributes.

      select * from user_types;
      select * from user_objects;
      select * from user_source;

2.2 Subtypes

Subtypes can be created under an existing type using "UNDER". But subtypes can only be created under types that are not "FINAL", i.e. not at the bottom of the type hierarchy. To create a subtype "employee" under "person", the type "person" (from last week's exercises) must first be changed to "NOT FINAL". Because there is already an object table (person_table) with objects attached to "person", the last word in the alter statement should be "CASCADE". That means that an alteration of "person" also applies to "person_table" and its objects.

      ALTER TYPE person NOT FINAL CASCADE;

If "describe person" now produces an error message, you need to logout of SQLPLUS and login again. This is so that Oracle can update the altered type and its objects.

Next, a subtype "employee" can be generated under "person". This subtype can itself be either NOT FINAL or FINAL. A subtype inherits all columns from its supertype but can also have additional columns, which are declared within the brackets.

      CREATE TYPE employee UNDER person () NOT FINAL ;
      /

The "IS OF" clause can be used to check the type of objects. The following statement selects all employees who are also a person, i.e., it selects all rows in employee_table.

      SELECT value(p) FROM employee_table p WHERE value(p) IS OF (person);

Exercise

2.3 Primary Keys

Even though an object-relational database maintains object IDs for all objects (i.e., for types, row objects, column objects), it is still a good idea to use primary keys for some tables. The following statement shows the object IDs. Obviously they are too long and would be too difficult to remember to be used directly by users.

      select SYS_NC_OID$ from person_table;

Object tables can be altered so that they have primary keys:

      ALTER TABLE job_table
      ADD (CONSTRAINT jobID PRIMARY KEY (job_ID));

In this case "jobID" is the name of the constraint whereas job_ID is the name of an actual column in job_table. If job_ID contains duplicates, then the alter statement produces an error.

Exercises

2.4 References or REFs

References (REF) can be used instead of foreign keys in many-to-one relationships. Note that the references point to object types not object tables.

      CREATE TABLE employment (
            employee REF employee,
            position REF job);

In addition to referencing the type it is also possible to restrict the references to actual object tables by using "SCOPE IS". Scoped references are implemented more efficiently by Oracle and are processed faster. But scope can only be defined when creating a table, not when creating a type.

      CREATE TABLE employment (
            employee REF employee SCOPE IS employee_table,
            position REF job SCOPE IS job_table);

The data to be inserted into tables with REFs comes from the corresponding object tables (i.e., employee_table and job_table). The function REF in the following statement provides the pointers to the objects in employee_table and job_table which are then inserted into employment.

      INSERT INTO employment
            SELECT REF(e), REF(j)
            FROM job_table j, employee_table e
            WHERE e.emp_ID = 2
            AND j.job_ID = 1;

Exercises: