Object-Relational Features of Oracle

In my previous post, I showed how to create custom types and that was a type which is a collection of varchar2. You can also create a composite column which is composed by varchar2, number or something else.

For example here is a definition of a point type consisting of two numbers:

     CREATE TYPE PointType AS OBJECT (
        x NUMBER,
        y NUMBER
    );
    /

An object type can be used like any other type in further declarations of object-types or table-types. 
For instance, we might define a line type by:

    CREATE TYPE LineType AS OBJECT (
        end1 PointType,
        end2 PointType
    );
    /


Then, we could create a relation that is a set of lines with ``line ID's'' as:

    CREATE TABLE Lines (
        lineID INT,
        line   LineType
    );

Then you can insert into table Lines in following manner - 

    INSERT INTO Lines
        VALUES(27, LineType(
                        PointType(0.0, 0.0),
                        PointType(3.0, 4.0)
                   )
        );

Here are some other queries about the relation lines. Note that here the table alias is required
as it is treated as the object of a class.

    SELECT ll.line.end1.x, ll.line.end1.y
    FROM Lines ll;

Suppose you want to load data from a flat table to this object relational table. Here is the procedure -

     CREATE TABLE LinesFlat(
        id INT,
        x1 NUMBER,
        y1 NUMBER,
        x2 NUMBER,
        y2 NUMBER
    );

   INSERT INTO Lines
        SELECT id, LineType(PointType(x1,y1), PointType(x2,y2))
        FROM LinesFlat;


Fore more detail, please visit this site. It is really nice.