16.1 How do I…Create object types and use them in relational tables?

Problem

I can see how database objects can closely model some of the business entities in my database. How do I create database objects and include these object types within traditional relational structures?

Technique

Most Oracle users are familiar with the tables in the schema SCOTT that contain data about a fictional company, its locations, and its employees. The structure of the DEPT table in SCOTT’s schema appears in Table 16.1.

Table 16.1 The traditional structure of the DEPT table from the schema SCOTT

Column Column Description DEPTNO Department Number DNAME Department Name LOC Department Location This How-To will create an object type called DEPT_TYPE to model the structure of the DEPT table and use the new object type to define a new version of the EMP table. Table EMP’s traditional structure appears in Table 16.2.

Table 16.2 The traditional structure of the EMP table from the schema SCOTT The Oracle8 data dictionary contains some new entities to accommodate object extensions. This How-To will query the DBA_TYPE_ATTRS view that appears in Table 16.3.

Table 16.3 The structure of the DBA_TYPE_ATTRS data dictionary view

Column Column Description
EMPNO Employee Number
ENAME Employee Name
JOB Employee Job Title
MGR Number of Employee’s Manager
HIREDATE Employee’s Hire Date
SAL Employee’s Salary
COMM Employee’s Commission
DEPTNO Employee’s Department Number
Column Column Description
OWNER Type owner
TYPE_NAME Type name
ATTR_NAME Attribute name
ATTR_TYPE_MOD Attribute’s type modifier
ATTR_TYPE_OWNER Attribute’s type owner
ATTR_TYPE_NAME Type attribute’s name
LENGTH Length of CHAR/VARCHAR attributes
PRECISION Decimal precision of numeric attributes
SCALE Scale of numeric attributes
CHARACTER_SET_NAME Character set name

Steps

1. Start SQL*Plus, connect as the WAITE user, and use the START command to load and execute the script CHP16_1.SQL. The script and its output appear in Figure 16.1.

The script creates a new object type, called DEPT_TYPE16, with attributes matching the column definitions from the familiar DEPT table.

2. Use the START command to load and execute the script CHP16_2.SQL. The script and its output appear in Figure 16.2.

Note that the last line of the DESCRIBE statement’s output lists the DEPT column with a datatype of NAMED TYPE.

3. Use the START command to load and execute the script CHP16_3.SQL. This script queries the DBA_TYPE_ATTRS data dictionary view to confirm the structure of the DEPT_TYPE16 object type created in Step 1. The script and its output appears in Figure 16.3.

How It Works

Step 1 creates an object type that looks exactly like the traditional DEPT table, but the object type is just a template and Oracle8 devotes no physical storage to it. Another important feature of the DEPT_TYPE16 object type is that other DDL statements can use this object as necessary to implement business models. Step 2 creates a new version of the standard EMP table including a reference to the DEPT16 object type. Step 3 queries the DBA_TYPE_ATTRS table to confirm the creation and structure of the DEPT_TYPE16 object type.

Comments

The original version of the EMP table has a not null constraint on the DEPTNO column. Unfortunately, the beta version of Oracle8 under Windows NT will not allow constraint definitions in CREATE TYPE statements nor in ALTER TABLE statements. For example, this statement:

ALTER TABLE emp16 MODIFY (dept.deptno CONSTRAINT cst_deptno_nn NOT NULL) ;

is syntactically correct but is not legal under the current version of Oracle8. It will generate an ORA-01748 message stating that only simple column types are allowed in the statement.

The DBA_TYPE_ATTRS data dictionary view is similar to the DBA_TAB_COLUMNS view from previous versions of Oracle in that there are analogous user level views called USER_TYPE_ATTRS and ALL_TYPE_ATTRS.

Now there is a new “object” version of the EMP table. How do users and applications insert records into it, you may ask? Read on; How-To 16.2 contains the answer.