Learning Bridge to Oracle Objects

Using Oracle objects
Learning bridge to Oracle objects

The first course in the Oracle8 series addresses Oracle objects.
If you need a refresher, read through the following material.

Object-oriented design

The main difference between object-oriented design and relational design is that object-oriented design allows you to not only define what data you can store in your database, you also define a set of actions (methods) associated with the data.
You can transfer a relational database table design into an object-oriented design by making the set of columns in a table into an object type and defining an object table.
There is a different name for a foreign key within object-oriented design. It is called a reference, or REF.
This is similar to a foreign key column because it points to a row in another table.

It is different because it uses an internal format called an object-id (OID) to store the pointer rather than a readable value like a

NUMBER or
VARCHAR2.

There are two object-oriented objects that are useful to store small sets of repeating data within a single column in a table. These are called arrays and nested tables.
The two objects are very similar, but there are some subtle differences. The table below outlines the similarities and differences between these two objects.

Array	Nested table
Has a predefined number of rows	Has an unlimited number of rows
Rows can be retrieved using their position (row 1, 2, and so on)	Rows cannot be retrieved using their position. However, you can add an index onto a nested table.
Cannot be queried using SQL, you must use PL/SQL	Can be queried using SQL via special extensions for "flattening" a nested table.

Here is an example of an array of ten phone numbers:

[[delete break]]

[[delete break]]


CREATE TYPE PHONE_ARRAY AS [[delete break]]


VARRAY (10) OF VARCHAR2(15)[[delete break]]

Here is an example of creating a nested table of phone numbers:

[[delete break]]

CREATE TYPE PHONE_NUMBER_LIST[[delete break]]

AS TABLE OF VARCHAR2(15)[[delete break]]

You can use an object type as the datatype for a nested table or an array.