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 NULL.

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.

See "How PL/SQL Treats Uninitialized Objects"

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) );
/

For CHAR and VARCHAR2 attributes whose length is specified without a char qualifier, the NLS_LENGTH_SEMANTICS initialization parameter setting (CHAR or BYTE) indicates the default unit of measure.

Oracle Database Globalization Support Guide for information on character length semantics

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 );

The object type location_typ defined in Example 2-3 is the type of the dept_loc column in the department_mgrs table in Example 2-4.

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"

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 dept_addr.

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.

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;

See"INSTEAD OF Triggers to Control Mutating and Validation"

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.