Oracle Objects and Oracle Object Datatypes

The Basics of Objects

The example below is a model of a university.

An object has attributes. For instance, a student has first name, last name and grade-point-average. These attributes are called properties. Operations which act upon the properties of an object are called methods. For instance, ChangeGPA will change the GPA of the student.

Objects communicate with each other by invoking methods. For example, a professor can invoke the ChangeGPA method for a student in order to change the grade-point-average of that student.

Ideally a client process only manipulates an object and its properties through that object's encapsulated methods. In this respect the client process does not have to know the contents of the method implementation. Effectively the contents of the object's methods are transparent to the client process. This is known as data-hiding where the contents of an object and its method are effectively abstracted to the client process.

One point is very important to note. Different texts use different terminologies for the following concepts. However, with object-design it should be noted that there is a very distinct difference between object templates and the object instances of those object templates. The template is the map or structure for an instance and the instance is an actual occurence of that template as defined by that template. Quite often these terms are mixed up and it should be noted that template and instance are completely different things. An object template is often referred to as a template, a class or an object. This is obviously potentially confusing when the object instance can also be referred to as an object.

A class is like a blue-print for a new automobile and the instances of that class, the objects, are the new cars manufactured from that blue-print.

Object-Relational Databases

Currently there are many object-oriented application programming languages. There are many pure object databases available. However, pure object databases are difficult to comprehend and are thus not widely used in the present day. Oracle is an object-relational database. This means Oracle is trying to combine both object and relational technology into a single product. This is in some ways impossible. However, with the release of Oracle8i (8.1.6) and the incorporation of Java into all tiers of Oracle8i that pure object database structure can be built in Java within the database kernel using JServer if so required. JServer is a JVM (Java Virtual Machine) environment which executes within the kernel of the database itself. This is the most efficient approach possible.

An object or more precisely the template of an object, ie. a class is in general comprised of attributes and methods. Attributes will contain values for object instances of a class and methods allow for programmatic instruction to be executed within each object instance against the values in the attributes of the class. Oracle class structures do not allow inheritance thus the polymorphism of method functionality throughout a class hierarchy is not possible with Oracle. Method functionality inheritance is the part about obejcts which allows for massive reduction of coding development times and much cheaper maintenance as a result.

Objects in the Database

Objects can be used in the database, declared locally in a PL/SQL block or in client-side cache.

Persistent vs. Transient Objects

A persistent object is an object that is stored in the database, as opposed to transient objects, which are local to a PL/SQL block. Transient object, like local PL/SQL variables, are deallocated when they go out of scope.

Row Objects

CREATE TABLE table_name OF object_type

Column Objects

CREATE TABLE customers ( customer CustomerObject, address AddressObject );

Object Identifiers

An object identifier (OID) is a unique locator for certain types of persistent objects. A ROWID identifies a row uniquely. OID's are unique across an Oracle universe. An OID enables unqiue database-wide identification which enables referencing of an object from other other objects or between relational database tables. The REF datatype represents these OIDs. A REF datatype is a container for an object identifier. A REF is a pointer to an object.

	department	CHAR(3),
	course		NUMBER(3),
	room		REF RoomObject

If a REF value points to a non-existent object which used to exist then the REF reference is said to be dangling. Note that a dangling REF is not a NULL REF. A NULL REF contains no value but a dangling contains a non-referanceable value. The IS [NOT] DANGLING predicate will determine if a REF value is dangling or not.


Below is a diagram showing a one-to-many relationship between customers and their many invoices. A relational join between the two tables could access all invoices for all customers or all invoices for a set of customers. Using REF type columns customers or invoices could be used to find the other directly without having to join the two tables. This would increase the database access speed greatly.

Using REF datatypes to avoid time consuming joins

The corollary to the REF function is the DREF function. The DREF (dereference) function will return the data in a row object to which the OID of the child record (passed into the DREF function) points.

How do Table Column References Actually Work in Oracle ?

Oracle8i will now allow the storage into tables of unique, instance-wide (Oracle universe) Object Identifiers (OID). Thus a table can be created where one of the columns is an instance-wide unique reference to that table row element. Effectively this reference column uniquely identifies a table row throughout the Oracle database instance.

This is how an object database works. Therefore Oracle is effectively semi-capable of being utilized as an object database. In this way one can access the rows in a one-to-many relationships between tables by directly accessing tuples (table rows) by using these references.

The reference is actually a physical disk or memory address of the tuple. Therefore this reference effectively becomes a constant database global OID, ie. since the address of the tuple never changes that record is always findable as a direct disk search without having to do table selections or joins. These references probably reside as a list of binary stored flat-file integers matching reference addresses possibly with their parent or related tuples.

The most effective way of implementation of this type of architecture is by loading the reference file into memory as a binary tree of objects with their contained collections. The result is that any object can be retrieved from the database once the root node of that tree has been retrieved by accessing the child collection objects of each node. All database access would occur at extremely high speed using the reference addresses contained within each object.


Object Types and Attributes

Classes or object templates are created in Oracle using what are called TYPES. An object type is used to describe both the properties of an object and the methods associated with that object. Use of objects does require the objects option in the Oracle8 database. This additional option is included when installing Oracle8.

Defining Object Types

Object types are created using the CREATE TYPE ... AS OBJECT statement.

	attribute_name datatype[, attribute_name datatype]...
	| [{MAP | ORDER} MEMBER function_specification]
	| [MEMBER {procedure_specification | function_specification} [, MEMBER {procedure_specification | function_specification}]...]
	| [PRAGMA RESTRICT_REFERENCES (method_name, constraints) [, PRAGMA RESTRICT_REFERENCES (method_name, constraints]...]
	ID		NUMBER(10),
	first_name	VARCHAR2(16),
	last_name	VARCHAR2(16),
	gpa		NUMBER(3,2)

Object type attributes can be any Oracle datatype except the following. Some or all of these restrictions will be lifted in the future.

Note that in an object hierarchy created in an Oracle8 database using TYPEs there is one slight problem. In order to alter a type such as changing an attribute it appears that all child objects must be removed in order to make this change. Thus all included object definitions must be recreated in order to effect a change. This could be a serious headache and is probably due to the fact that Oracle8 is an object-relational and not an object database.

Altering and Dropping Types


ALTER TYPE type_name REPLACE AS OBJECT (object_type_specification);

DROP TYPE [schema.]type_name [FORCE];

Declaring and Initializing Objects

Declaring Objects

An object is declared, as with a variable, simply by placing it syntactically after its type in the declarative section of a block as shown below.

	vCustomer CustomerObject;

Initializing Objects

Objects are initialised with a constructor. A constructor is a function (method) which returns an initialised object and takes the values of the object's attributes as its arguments. Note that a constructor is not explicitly defined but can be thought of as having the specification as defined below.

FUNCTION CustomerObject (ID IN NUMBER, firstName IN VARCHAR2, lastName IN VARCHAR2, balance IN NUMBER)
RETURN CustomerObject;

The code shown below would instantiate the object into a variable.

	vCustomer = CustomerObject (0001, 'James', 'Dean', 1000000.00);
	vCustomer.balance = 2000000; This increases this customer's balance from 1 million to 2 million.

Object NULL vs Attribute NULL

An object declaration which does not use a constructor creates a NULL object. It is illegal to refer to the attributes of a NULL object. For instance, the code below will produce an error.

	vCustomer CustomerObject;
BEGIN = 'Derek';

Forward Type Definitions

Types can be created before the attribute values are known. A type definition is really the equivalent of C programming struct, a recordset in Visual Basic, a template or a class. A type is effectively a definition containing other definitions.

CREATE TYPE <type_name>


In Oracle8 a method is intended to act upon an object created by a type.

TYPE Declaration

	  attribute_name datatype[, attribute_name datatype]...
	| [{MAP | ORDER} MEMBER function_specification]
	| [MEMBER {procedure_specification | function_specification}
	  [, MEMBER {procedure_specification | function_specification}]... ]
	| PRAGMA RESTRICT_REFERENCES (method_name, constraints)
	  [, PRAGMA RESTRICT_REFERENCES (method_name, constraints)]... ]

Below is an example of a type declaration containing methods.

	firstName	VARCHAR2(35),
	lastName	VARCHAR2(35),
	balance		NUMBER(12,2),

	MEMBER FUNCTION formatName --returns concatenation of first and last names

	MEMBER PROCEDURE changeBalance (pNewBalance in NUMBER), --updates the balance


Important points about method declarations.

TYPE Body Section

CREATE [OR REPLACE] TYPE [schema.]type_name BODY {IS | AS}
	[ {MAP | ORDER} MEMBER function_declaration;]
	| [MEMBER {procedure_specification | function_specification};
	  [, MEMBER {procedure_specification | function_specification}]... ]

Below is an example of a type body containing methods.


		RETURN SELF.firstName || ' ' || SELF.lastName;
	END formatName;

	MEMBER PROCEDURE changeBalance (pNewBalance IN NUMBER) IS
		SELF.balance := pNewBalance;
	END changeBalance;


An object type body can only include member subprograms and can not include private variable declarations in the body which are not declared in the declaration of the object.

Calling a Method


	vCustomer1 CustomerObject := CustomerObject (100, 'Jim', 'Jones', NULL);
	vCustomer2 CustomerObject := CustomerObject (200, 'Jennifer', 'Juniper', 20000);
	vCustomer1.changeBalance (200000);
	vCustomer2.changeBalance (25000);
	DBMS_OUTPUT.PUT_LINE (vCustomer1.formatName());
	DBMS_OUTPUT.PUT_LINE (vCustomer2.formatName());

The SELF Keyword

The SELF keyword binds a variable to the instantiating object.

Using %TYPE with Objects

A variable can be used to declare a variable which takes on the type of the variable used.

vID vCustomer.ID%TYPE;

Exceptions and Object Type Attributes

	SELF.attribute := pOutParam;
	pOutParam := pOutParam + 1;
	IF pRaiseIt THEN
END raiseError;

Types of Methods

There are four types of methods in Oracle8.

  1. Constructor methods.
  2. Member methods.
  3. MAP methods.
  4. ORDER methods.

Constructor Methods

Constructor methods are automatically executed on creation of an object, ie. the declaration of a variable instance of a type, ie. object instance of a class, as shown in the examples in the section Declaring and Initialising Objects.

MEMBER Methods

MEMBER methods are stored procedures or functions which are a part of, ie. encapsulated with the class, ie. the object instances of the class.

MAP and ORDER Methods

An object type can only be declared for equality. This prevents object types from being used in clauses such as ORDER BY or DISTINCT, since they require ordering. MAP and ORDER methods are used to solve this problem. Object values can only be compared using the MAP and ORDER comparison functions. MAP and ORDER functions both compare object type instances.

MAP Methods

A MAP method is a function which returns a scalar type. When the database needs to sort the object, it can call the MAP function to convert the object to a type which can be sorted. The method acts like a hash function.

	END ReturnID;
END RoomObject;
ORDER Methods

You can create an ORDER method. ORDER methods take one parameter (of the object type) and return the following values.

ORDER MEMBER FUNCTION FUNCTION CompareStudent (pStudent IN StudentObject)


Nested tables and Varrays are collection objects. A collection object is an object type which can contain other objects, or even the references to those other objects. Both nested tables and varrays can be accessed like arrays using subscripts and both can be stored into database tables. Varrays have a maxmimum size, nested tables are unlimited. Varrays are can be stored in tables whereas nested tables can be stored in their own database tables. Therefore Varrays retain subscript values and their ordering and nested tables do not. The size of a varray is predefined and thus constant. nested table elements can be deleted using the TRIM method.

Nested Tables

A nested table is a dynamic array object with two columns, a key column and a value column. A dynamic array is like a pointer; not fixed number of elements.

TYPE table_name IS TABLE OF table_type [NOT NULL];
	TYPE tbl_Course IS TABLE OF CourseObject;
	TYPE tbl_Student IS TABLE OF students%ROWTYPE;
	lCourses tbl_Course;
	lStudents tbl_Student;

The above example declare two table type collections of course and student. The variables are declared for those collection data types.

Tables are automatically initialised to NULL. Values must be added to the tables in order to avoid a NULL collection error (COLLECTION _IS_NULL). Thus referring to for instance lStudents(1) at present will produce a NULL error since the lStudents(1) object does not yet exist, only variables have so far been declared.

In order to initialise or instantiate an object a class constructor method must be executed. In general, a class constructor is executed on creation of an object. Since object have not yet been created, only their definitions, therefore the object instantiations of the classes must be explicitly created. This is somewhat like creating an object by initialising it to an initial value, such as 0 or spaces. The constructor method will have the same name as the table. The example below will place three names into the lStudents nested table variable. These values will be referencable as lStudents(1), lStudents(2) and lStudents(3).

lStudents tbl_Course := tbl_Course ('Jim', 'Joe', 'Derek');

Note that it is possible to create a collection object, for instance a nested table, with no elements such that the collection object itself, ie. the nested table, is not NULL but there are no subscriptable elements, as shown below.

lStudents tblCourse := tbl_Course ();

Nested tables can be placed into database tables. These nested tables could be used to contain multiple values within a single database table row. Nested tables could also be used to contain references to other objects or rows in other database tables. For instance, ROWIDs of rows in other database tables, therebye avoiding potentially costly SQL joins where nested tables containing rows could access rows from other database tables using ROWID searches. ROWID searching is very, very fast. This could potentially get close to the efficiency of an object database. Also the access to these nested tables and database tables could be controlled from within the database using stored procedures and functions thus hiding data access complexity from developers. Hiding of data access complexity is one of the very strong points in favor of the use of object databases such as Jasmine or ObjectStore. Speed increases in data access in this respect in Oracle could be staggering. A nested table can be placed into a database table as shown below.

CREATE TYPE StudentObject AS OBJECT ( name VARCHAR2(32), id VARCHAR2(32) );
CREATE TYPE StudentCollection AS TABLE OF StudentObject;
	 name VARCHAR2(32)
	,lecturer VARCHAR2(32)
	,students StudentCollection NESTED TABLE students STORE AS student_table

Nested tables stored in database tables can be manipulated using SQL as shown below. Note that it may be possible to embed SQL statements into nested table object. I have not experimented with this. If this is not possible the ROWIDs can be selected and stored as individual PL/SQL declared variables or even as a locally declared nested table object where the pre-built nested table variable can be put into the parent database table. Also note that the lecturer, Prof. Plum, could be stored elsewhere and stored in this table as an individual reference, not a collection, to another database table containing details of lecturers.

	 'Appied Mathematics II'
	,'Prof. Plum'
		 StudentObject (SELECT ROWID FROM Student WHERE name='Jim')
		,StudentObject (SELECT ROWID FROM Student WHERE name='Joe')

UPDATE Course SET students = StudentCollection 
	 StudentObject (SELECT ROWID FROM Student WHERE name='Jim')
	,StudentObject (SELECT ROWID FROM Student WHERE name='Joe')
	,StudentObject (SELECT ROWID FROM Student WHERE name='Derek')
) WHERE name = 'Appied Mathematics II' AND lecturer = 'Prof. Plum';

DELETE FROM Course WHERE name = 'Appied Mathematics II' AND lecturer = 'Prof. Plum';

Note in the examples above that control over individual collection objects, ie. students, is not possible. The nested table simply contains a list of references from each course to the collection of students contained within that course. Control of individual objects in a collection is possible using methods attached to the collection object. It is possible to manipulate individual database table stored nested table elements using DML with the THE operator. The THE operator takes a subquery as an argument and returns a nested table. Note however that the THE gives access to the nested table as a whole, ie. all values in a nested table column can be changed. Access to individual rows in terms of changing a single nested table row is not possible. This would obviously be completely illogical for the examples I have given so far. However, if a nested table was used to store actual values and not references then whenever a single nested table column row value is required to be altered then this approach would not apply. In fact storage of nested tables containing literal values into database tables is probably a bad idea anyway. Why not just use a related table ?


Varrays types or vertical arrays are fixed length and not variable length arrays. Varrays are non-dynamic arrays. A varray must be declared as having a maximum number of elements. Varrays can not contain BOOLEAN, NCHAR, NCLOB, NVARCHAR2, REF CURSOR, TABLE or other VARRAY types.

TYPE type_name IS {VARRAY | VARYING ARRAY } (maximum_size) OF element_type [NOT NULL];
	TYPE vCourses IS VARRAY(1) OF CourseObject;
	TYPE vStudents IS VARRAY(3) OF students%ROWTYPE;
	lCourses vCourses := ('Applied Mathematics II');
	lEmpty vEmpty (NULL); 					This array contains a single NULL element.
	lStudents vStudents ('Jim', 'Joe', 'DXXXrek');

	lStudents (3) = 'Derek';				Corrects the name Derek using a subscript.

Not much can be done in terms of manipulating elements in a varray stored in a database table. Thus if storage in database tables is required then use a nested table declaration rather than a varray. However, a varray should be faster than a nested table since it is probable dynamic arrays require more CPU time.

Collection Methods

A class is a definition for an object. An object is the instantiation of that class. Thus many unique objects can be created from a single class. A class is a template for an object and the object contains the values for an instance or occurence of that class. A collection is a class. A class can have methods or processing attached to it. Methods are basically functions or processing units which apply to objects created from that class containing those methods.

Accessing Objects in DML Statements


UPDATE classes SET room = vRoomRef WHERE department = 'NUT' and course = 307;

DELETE FROM customers c WHERE c.customer.balance = 0;

SELECT customer FROM customers WHERE = 100;

SELECT VALUE(r) INTO vRoomObject FROM rooms r WHERE ID = 100;

The REF operator will return a REF to the requested object, rather than the object itself. Like VALUE, REF takes a correlation variable as an argument.

SELECT REF(r) INTO vRoomRef FROM rooms r WHERE ID = 1000;

The DEREF operator will return the original object given a reference.

SELECT DEREF(vRoomRef) INTO vRoom FROM dual;

A dangling REF is a reference to which is pointed by is deleted. Use the IS DANGLING predicate to check for this case.


RETURNING select_list INTO into_list;