Oracle8 New Features - Object Support

Oracle8 Object Support

Many of the programming languages in use now are object oriented, for example: Java, C++, VB, Delphi. Prior to Oracle8, objects on the client side had to be converted into relational tables to be stored in an Oracle database.

Oracle8 now allows objects to be defined and stored in the database, thereby eliminating the need for this translation between client and server.

To do this Oracle8 has:

Object types
User-defined data types, including methods
Object views - these enable existing relational tables to be combined into virtual objects for manipulation by object-oriented applications
Object extensions to SQL and PL/SQL and the programmatic interfaces: Pro*C, Pro*COBOL and OCI

Object Types

The built in types available in Oracle8 have been expanded to include "collections" and references. Collections in Oracle8 are either nested tables (which are in the ANSI standard) or variable length arrays – VARRAYs.

Nested Tables

These are useful for master-detail relationships, such as invoices and invoice lines, where the number of detail records is unknown.

To create, for example, an invoice table with a nested invoice line table, first define the object type of the nested table:

CREATE OR REPLACE inv_dtl AS OBJECT
(
     Id   NUMBER(5),
     Desc VARCHAR2(120),
     Amt NUMBER(6,2)
);

Then define the table type based on the object type:

CREATE OR REPLACE inv_dtl_tab AS TABLE OF inv_dtl;

Finally define the parent table with the nested table:

CREATE TABLE invoice( Id      NUMBER(5), Cust_id NUMBER(6), Total   NUMBER(9,2), Lines   inv_dtl_tab)
NESTED TABLE lines STORE AS invoice_lines;

The last line defines the name of the segment used to hold the nested table and is necessary because nested tables are stored in a different segment to (but in the same tablespace as) the main table.

To access a nested table, you have to use a flattened sub-query:

SELECT id, amt FROM THE
(SELECT lines FROM invoice WHERE id=1);

VARRAYS

VARRAYS are useful when the maximum number of detail records is small and is known in advance (such as a list of phone numbers for a customer e.g. office, home and mobile). Note that there is no storage clause for the varray, because they are stored in line.

CREATE TYPE phone_no_type AS OBJECT ( Desc VARCHAR2(20) ,Phone_no VARCHAR2(20) ); CREATE TYPE phone_no_list AS VARRAY(3) OF phone_no_type; CREATE OR REPLACE TYPE cust AS OBJECT ( Id NUMBER(12) ,Name VARCHAR2(75) ,Phone_nos phone_no_list) );

The elements of a vararray cannot be accessed individually using SQL, but they can be accessed in PL/SQL and 3GLs.

User Defined Types

As well as the built in data types Oracle8 also supports user-defined object types. These can be composed of the built in types or previously declared object types and can include both attributes and methods. Incomplete object types, without either attributes or methods, can be declared to enable dependent objects to be declared. These user defined object types can be used to define a column in a relational table, to define an object table or to define a relationship to another object.

Object Types in Relational Tables

If address_type were to be defined as:

CREATE OR REPLACE TYPE address_typeAS OBJECT ( line1 VARCHAR2(80) ,line2 VARCHAR2(80) ,line3 VARCHAR2(80) ,post_code VARCHAR2(10) ,country VARCHAR2(50));

A normal relational table – customer - using this type could be defined as:

CREATE TABLE customer (
     Id      NUMBER(12),
     Name    VARCHAR2(75),
     Address address_type);

The table could then be queried as per normal, but to obtain the address details, an extended form of the dot notation is required as in the follwoing example:

SELECT cus.name
, cus.address.post_code
, cus.address.country
FROM customer cus;

Updates also require the use of the dot notation

UPDATE customer cus
SET cus.address.post_code = ‘NW1 3DR’

Inserts are accomplished by use of the type constructor that is created by Oracle automatically when the type is defined.

INSERT INTO customer VALUES ( 1 ,'Royal&SunAlliance' ,address_type( 'Phoenix House' ,'Redcliffe Hill' ,'Bristol' ,'BS1 6SQ' ,'UK') );

Object Tables

These are based solely on an object type. For example, using the previous definition of address_type, a new type, cust, and a table customer can be defined as follows:

CREATE OR REPLACE TYPE cust AS OBJECT (Id NUMBER(12), Name VARCHAR2(75), Address address_type ); CREATE TABLE customer OF cust(id PRIMARY KEY);

This would be accessed with SQL in the same way as a relational table.

Relationships

A relationship from one object to another is defined by means of a reference in the object table definition. This acts like a foreign key, linking one object to another object in the database (via the unique object identifier – the object’s primary key). Oracle8 uses the link automatically to perform the same function as a join between relational tables.

For example consider a new object called invoice which in a relational database would have a foreign key relationship to the customer table. In object terms this becomes a new object referencing an existing object as follows:

CREATE OR REPLACE TYPE inv AS OBJECT ( Id NUMBER(12) ,Client REF cust ,Total NUMBER(9,2)); CREATE TABLE invoice OF inv (id PRIMARY KEY);

To query the invoice and customer details, the SQL would be simply:

SELECT id, total, inv.customer.name FROM invoice inv;

Methods

Each object definition can optionally specify methods as functions to answer questions about the object or as procedures to perform some operations on the object. A method must be defined if sorting/ordering of the objects is required.

For example using the relational definition of the CUSTOMER table, to order customers by their address would require either a MAP or ORDER function to be defined for ADDRESS_TYPE.

A MAP function returns a scalar type, ORDER functions can be used when it is not easy to return a scalar type.

Methods are defined in two parts, like pl/sql packages: the specification is defined in the object type declaration and the code (body) is defined in the type body.

This can be demonstrated by expanding on the type definition address_type shown above as in the following example:

CREATE OR REPLACE TYPE address_type AS OBJECT (line1 VARCHAR2(80) ,line2 VARCHAR2(80) ,line3 VARCHAR2(80) ,post_code VARCHAR2(10) ,country CHAR2(50) ,MAP MEMBER FUNCTION address_sort RETURN VARCHAR2); CREATE OR REPLACE TYPE BODY address_type AS (MAP MEMBER FUNCTION address_sort RETURN VARCHAR2 IS BEGIN RETURN line2||line3||post_code; END address_sort;);

Object Views

Object views can be built on object or relational tables and are particularly useful for providing access to a relational database from an object-oriented application without having to perform any conversion on the existing tables. They also allow object and relational applications to use the same database with little modification.

Inserts, updates and deletes against the view are achieved by creating ‘INSTEAD OF’ triggers, which are fired instead of insert, update, or delete operations against the view.

Object Extensions to SQL, PL/SQL and 3GL Interfaces

Within PL/SQL, the DML extensions for object types can be executed, object types can be declared as parameters and bind variables, and stored procedures and methods can be invoked.

PL/SQL has also been changed to allow calls to be made to external procedures (such as DLLs). At present, only calls to C functions are allowed.

Pro-C and C++ have been enhanced to support database objects (including user defined types).

A new utility called the Object Type Translator helps with the mapping of C/C++ structures to database objects by generating C language structs.

This ensures that data declarations in 3GL programs always agree with the Oracle8 data types.

Oracle8 provides a client based object cache to provide high performance access to objects by reducing the number of round-trips to the server.

The long and long raw data types have been supplemented with 4 large object types (LOBs) which provide random access to the data. The new types are:

BLOB - used for unstructured binary (raw) data
CLOB - used for single byte character data
NCLOB - used for fixed width multi-byte character data (e.g. Japanese)
BFILE - used for raw data stored externally, such as multimedia files or videos.

The LOB types , other than BFILEs, are stored within the database.

---------------------------------------

Looking for more Oracle tips and tricks ? Subscribe to our ezine - it's jam-packed full of tips and tricks to help you make more of your Oracle systems and save you hours of blood, sweat and tears searching for information. Subscribe today and your first issue will soon be winging its way to your mailbox.

Send us your questions and we'll answer them in the next newsletter.

Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320

Contact Us

Click here to view our privacy policy .

This site uses woopra.com to gather statistical information about our visitors. View woopra privacy policy .

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.