Oracle Object Type

Object Types

Oracle implements Objects through the use of TYPEs, defined in a similar way to packages. Unlike packages where the instance of the package is limited to the current session, an instance of an object type can be stored in the database for later use. The definition of the type contains a comma separated list of attributes/properties, defined in the same way as package variables, and member functions/procedures. If the a type contains member functions/procedures, the procedural work for these elements is defined in the TYPE BODY.

• Define A TYPE
• Define A TYPE BODY
• Defining A Table
• Constructors
• Data Access
• Issues

Define A TYPE

First we define a type PersonObj to represent a person. In this example, a person is defined by three attributes (first_name, last_name, date_of_birth) and one member function (getAge).

CREATE OR REPLACE TYPE PersonObj AS OBJECT (
  first_name  VARCHAR2(50),
  last_name   VARCHAR2(50),
  date_of_birth  DATE,
  MEMBER FUNCTION getAge RETURN NUMBER
);
/

Define A TYPE BODY

Next we define a TYPE BODY to add functionality to the getAge member function.

CREATE OR REPLACE TYPE BODY PersonObj AS
  MEMBER FUNCTION getAge RETURN NUMBER AS
  BEGIN
    RETURN Trunc(Months_Between(Sysdate, date_of_birth)/12);
  END getAge;
END;
/

Define A TYPE

First we define a type PersonObj to represent a person. In this example, a person is defined by three attributes (first_name, last_name, date_of_birth) and one member function (getAge).

CREATE OR REPLACE TYPE PersonObj AS OBJECT (
  first_name  VARCHAR2(50),
  last_name   VARCHAR2(50),
  date_of_birth  DATE,
  MEMBER FUNCTION getAge RETURN NUMBER
);
/

Define A TYPE BODY

Next we define a TYPE BODY to add functionality to the getAge member function.

CREATE OR REPLACE TYPE BODY PersonObj AS

  MEMBER FUNCTION getAge RETURN NUMBER AS
  BEGIN

    RETURN Trunc(Months_Between(Sysdate, date_of_birth)/12);
  END getAge;
END;

/

Defining A Table

Now the object type is defined we can use it as a datatype in a table.

CREATE TABLE people (
  id      NUMBER(10) NOT NULL,
  person  PersonObj
);

Constructors

To insert data into the PEOPLE table we must use the PersonObj() constructor. This can be done as part of a regular DML statement, or using PL/SQL.

INSERT INTO people
VALUES (1, PersonObj('John','Doe',
        TO_DATE('01/01/1999','DD/MM/YYYY')));
COMMIT;

DECLARE
  v_person  PersonObj;
BEGIN
  v_person := PersonObj('Jane','Doe',
              TO_DATE('01/01/1999','DD/MM/YYYY'));
  INSERT INTO people VALUES (2, v_person);
  COMMIT;
END;
/

Data Access

Once the data is loaded it can be accessed using the dot notation.

• alias.column.attribute
• alias.column.function()

SELECT p.id,
       p.person.first_name,
       p.person.getAge() age
FROM   people p;

        ID PERSON.FIRST_NAME                  AGE
---------- --------------------------- ----------
         1 John                                 2
         2 Jane                                 2

2 row selected.

SQL>

Issues

• Once an object is used to define a table only it's BODY cannot be altered. To alter the type all table references to it must be dropped. This means the type definition should be very stable before it is used in a table. Note. Later releases of the database allow Type Evolution.
• The Export & Import often have difficulties with Object Types.
• The SQL*Plus COPY command does not work with Object Types.
• There are a number of issues with database links in conjunction with Object Types. This makes the use of object tables in distributed systems impossible without the use of views to hide the object functionality. This defeats the "object" of Objects somewhat.