Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

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.

Related articles.

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.

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

For more information see:

Hope this helps. Regards Tim...

Back to the Top.