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
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 );
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; /
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>
- 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.
For more information see:
- Oracle Database Object-Relational Developer's Guide 11g Release 2 (11.2)
- Using PL/SQL With Object Types
- Type Evolution
Hope this helps. Regards Tim...