This section describes SQL object types and references.
You create Oracle SQL object types with the
TYPE statement. A typical example of object type creation is shown in Example 2-1.
2.1.1 Null Objects and Attributes
An object whose value is
NULL is called atomically null.
An atomically null object is different from an object that has null values for all its attributes.
In an object with null values, a table column, object attribute, collection, or collection element might be
NULL if it has been initialized to
NULL or has not been initialized at all. Usually, a
NULL value is replaced by an actual value later on. When all the attributes are null, you can still change these attributes and call the object's subprograms or methods. With an atomically null object, you can do neither of these things.
Example 2-1 creates the
contacts table and defines the
person_typ object type and two instances of this type.
Example 2-1 Inserting NULLs for Objects in a Table
CREATE OR REPLACE TYPE person_typ AS OBJECT ( idno NUMBER, name VARCHAR2(30), phone VARCHAR2(20), MAP MEMBER FUNCTION get_idno RETURN NUMBER, MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ) ); / CREATE OR REPLACE TYPE BODY person_typ AS MAP MEMBER FUNCTION get_idno RETURN NUMBER IS BEGIN RETURN idno; END; MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ) IS BEGIN -- use the PUT_LINE procedure of the DBMS_OUTPUT package to display details DBMS_OUTPUT.PUT_LINE(TO_CHAR(idno) || ' - ' || name || ' - ' || phone); END; END; / CREATE TABLE contacts ( contact person_typ, contact_date DATE ); INSERT INTO contacts VALUES ( person_typ (NULL, NULL, NULL), '24 Jun 2003' ); INSERT INTO contacts VALUES ( NULL, '24 Jun 2003' );
Two instances of
person_typ are inserted into the table and give two different results. In both cases, Oracle Database allocates space in the
contacts table for a new row and sets its
DATE column to the value given. But in the first case, Oracle Database allocates space for an object in the
contact column and sets each of the object's attributes to
NULL. In the second case, Oracle Database sets the
person_typ field itself to
NULL and does not allocate space for an object.
In some cases, you can omit checks for null values. A table row or row object cannot be null. A nested table of objects cannot contain an element whose value is
A nested table or array can be null, so you do need to handle that condition. A null collection is different from an empty one, one that has no elements.
2.1.2 Character Length Semantics
Lengths for character types
VARCHAR2 may be specified as a number of characters, instead of bytes, in object attributes and collections even if some of the characters consist of multiple bytes.
To specify character-denominated lengths for
VARCHAR2 attributes, you add the qualifier
char to the length specification.
NVARCHAR2 may also be used as attribute types in objects and collections.
NVARCHAR2 are always implicitly measured in terms of characters, so no
char qualifier is used.
For example, the following statement creates an object with both a character-length
VARCHAR2 attribute and an
Example 2-2 Creating the employee_typ Object Using a char Qualifier
CREATE OR REPLACE TYPE employee_typ AS OBJECT ( name VARCHAR2(30 char), language NCHAR(10), phone VARCHAR2(20) ); /
VARCHAR2 attributes whose length is specified without a
char qualifier, the
NLS_LENGTH_SEMANTICS initialization parameter setting (
BYTE) indicates the default unit of measure.
Oracle Database Globalization Support Guide for information on character length semantics
2.1.3 Defining Object Tables with Single Constraints
You can define constraints on an object table just as you can on other tables.
You can define constraints on the leaf-level scalar attributes of a column object, with the exception of
REFs that are not scoped.
Example 2-3 places a single constraint, an implicit
KEY constraint, on the
office_id column of the object table
Example 2-3 Creating the office_tab Object Table with a Constraint
-- requires Ex. 2-1 CREATE OR REPLACE TYPE location_typ AS OBJECT ( building_no NUMBER, city VARCHAR2(40) ); / CREATE OR REPLACE TYPE office_typ AS OBJECT ( office_id VARCHAR(10), office_loc location_typ, occupant person_typ );/ CREATE TABLE office_tab OF office_typ ( office_id PRIMARY KEY );
2.1.4 Defining Object Tables with Multiple Constraints
You can define object tables with multiple constraints.
You can define object tables with multiple constraints.
Example 2-4 Creating the department_mgrs Table with Multiple Constraints
Example 2-4 defines constraints on scalar attributes of the
location_typ objects in the table.
-- requires Ex. 2-1 and 2-3 CREATE TABLE department_mgrs ( dept_no NUMBER PRIMARY KEY, dept_name CHAR(20), dept_mgr person_typ, dept_loc location_typ, CONSTRAINT dept_loc_cons1 UNIQUE (dept_loc.building_no, dept_loc.city), CONSTRAINT dept_loc_cons2 CHECK (dept_loc.city IS NOT NULL) ); INSERT INTO department_mgrs VALUES ( 101, 'Physical Sciences', person_typ(65,'Vrinda Mills', '1-1-650-555-0125'), location_typ(300, 'Palo Alto'));
See "Constraints on Objects"
2.1.5 Defining Indexes for Object Tables
You can define indexes on an object table or on the storage table for a nested table column or attribute just as you can on other tables.
Define indexes on leaf-level scalar attributes of column objects. You can only define indexes on
REF attributes or columns if the
REF is scoped.
Example 2-5 Creating an Index on an Object Type in a Table
-- requires Ex. 2-1, 2-3, CREATE TABLE department_loc ( dept_no NUMBER PRIMARY KEY, dept_name CHAR(20), dept_addr location_typ ); CREATE INDEX i_dept_addr1 ON department_loc (dept_addr.city); INSERT INTO department_loc VALUES ( 101, 'Physical Sciences', location_typ(300, 'Palo Alto')); INSERT INTO department_loc VALUES ( 104, 'Life Sciences', location_typ(400, 'Menlo Park')); INSERT INTO department_loc VALUES ( 103, 'Biological Sciences', location_typ(500, 'Redwood Shores'));
This example, Example 2-5, indexes
city, which is a leaf-level scalar attribute of the column object
Wherever Oracle Database expects a column name in an index definition, you can also specify a scalar attribute of a column object.
For an example of an index on a nested table, see Storing Elements of Nested Tables.
2.1.6 Defining Triggers for Object Tables
You can define triggers on an object table just as you can on other tables.
You cannot define a trigger on the storage table for a nested table column or attribute. You cannot modify
LOB values in a trigger body. Otherwise, there are no special restrictions on using object types with triggers.
Example 2-6 Creating a Trigger on Objects in a Table
-- requires Ex. 2-1 and 2-3 CREATE TABLE movement ( idno NUMBER, old_office location_typ, new_office location_typ ); CREATE TRIGGER trigger1 BEFORE UPDATE OF office_loc ON office_tab FOR EACH ROW WHEN (new.office_loc.city = 'Redwood Shores') BEGIN IF :new.office_loc.building_no = 600 THEN INSERT INTO movement (idno, old_office, new_office) VALUES (:old.occupant.idno, :old.office_loc, :new.office_loc); END IF; END;/ INSERT INTO office_tab VALUES ('BE32', location_typ(300, 'Palo Alto' ),person_typ(280, 'John Chan', '415-555-0101')); UPDATE office_tab set office_loc =location_typ(600, 'Redwood Shores') where office_id = 'BE32'; select * from office_tab; select * from movement;
2.1.7 Rules for REF Columns and Attributes
REF columns and attributes can be enforced by the use of constraints.
In Oracle Database, a
REF column or attribute can be unconstrained or constrained using a
SCOPE clause or a referential constraint clause. When a
REF column is unconstrained, it may store object references to row objects contained in any object table of the corresponding object type.
Oracle Database does not ensure that the object references stored in such columns point to valid and existing row objects. Therefore,
REF columns may contain object references that do not point to any existing row object. Such
REF values are referred to as dangling references.
SCOPE constraint can be applied to a specific object table. All the
REF values stored in a column with a
SCOPE constraint point at row objects of the table specified in the
SCOPE clause. The
REF values may, however, be dangling.
REF column may be constrained with a
REFERENTIAL constraint similar to the specification for foreign keys. The rules for referential constraints apply to such columns. That is, the object reference stored in these columns must point to a valid and existing row object in the specified object table.
KEY constraints cannot be specified for
REF columns. However, you can specify
NULL constraints for such columns.
2.1.8 Name Resolution
There are several ways to resolve names in Oracle Database.
Oracle SQL lets you omit qualifying table names in some relational operations.
For example, if
dept_addr is a column in the
department_loc table and
old_office is a column in the
movement table, you can use the following:
SELECT * FROM department_loc WHERE EXISTS (SELECT * FROM movement WHERE dept_addr = old_office);
Oracle Database determines which table each column belongs to.
Using dot notation, you can qualify the column names with table names or table aliases to make things more maintainable. For example:
Example 2-7 Using the Dot Notation for Name Resolution
-- requires Ex. 2-1, 2-3, 2-5, and 2-6 SELECT * FROM department_loc WHERE EXISTS (SELECT * FROM movement WHERE department_loc.dept_addr = movement.old_office); SELECT * FROM department_loc d WHERE EXISTS (SELECT * FROM movement m WHERE d.dept_addr = m.old_office);
In some cases, object-relational features require you to specify the table aliases.
188.8.131.52 When Table Aliases Are Required
Table aliases can be required to avoid problems resolving references.
Oracle Database requires you to use a table alias to qualify any dot-notational reference to subprograms or attributes of objects, to avoid inner capture and similar problems resolving references.
Inner capture is a situation caused by using unqualified names. For example, if you add an
assignment column to
depts and forget to change the query, Oracle Database automatically recompiles the query so that the inner
SELECT uses the
assignment column from the
Use of a table alias is optional when referencing top-level attributes of an object table directly, without using the dot notation. For example, the following statements define two tables that contain the
person_typ object type.
person_obj_table is an object table for objects of type
contacts is a relational table that contains a column of the object
The following queries show some correct and incorrect ways to reference attribute
These statements are not related to other examples in this chapter.
#1 SELECT idno FROM person_obj_table; --Correct #2 SELECT contact.idno FROM contacts; --Illegal #3 SELECT contacts.contact.idno FROM contacts; --Illegal #4 SELECT p.contact.idno FROM contacts p; --Correct
idnois the name of a column of
person_obj_table. It references this top-level attribute directly, without using the dot notation, so no table alias is required.
idnois the name of an attribute of the
person_typobject in the column named
contact. This reference uses the dot notation and so requires a table alias, as shown in #4.
#3 uses the table name itself to qualify the reference. This is incorrect; a table alias is required.
You must qualify a reference to an object attribute or subprogram with a table alias rather than a table name even if the table name is itself qualified by a schema name.
For example, the following expression incorrectly refers to the
dept_addr column, and
city attribute of that column. The expression is incorrect because
department_loc is a table name, not an alias.
The same requirement applies to attribute references that use
Table aliases should uniquely pick out the same table throughout a query and should not be the same as schema names that could legally appear in the query.
Oracle recommends that you define table aliases in all
SELECT statements and subqueries and use them to qualify column references whether or not the columns contain object types.
2.1.9 Restriction on Using User-Defined Types with a Remote Database
Objects or user-defined types (specifically, types declared with a SQL
TYPE statement, as opposed to types declared within a PL/SQL package) are currently useful only within a single database.
Oracle Database restricts use of a database link as follows:
You cannot connect to a remote database to select, insert, or update a user-defined type or an object
REFon a remote table.
You can use the
TYPEstatement with the optional keyword
OIDto create a user-specified object identifier (OID) that allows an object type to be used in multiple databases. See the discussion on assigning an OID to an object type in the Oracle Database Data Cartridge Developer's Guide.
You cannot use database links within PL/SQL code to declare a local variable of a remote user-defined type.
You cannot convey a user-defined type argument or return value in a PL/SQL remote procedure call.