For Better, Faster, Smarter,
Oracle8 Object Support
Many programming languages in use now are object oriented including Java, C++, Python, Ruby, VB.NET etc. 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.
New features introduced in Oracle8 to enable this comprise:
User-defined data types, including methods
Object views - these enable existing relational tables to be combined into virtual objects for manipulation by
Object extensions to SQL and PL/SQL and the programmatic
interfaces: Pro*C, Pro*COBOL and OCI
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 –
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),
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(
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 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. For example:
CREATE TYPE phone_no_type AS OBJECT
( Desc VARCHAR2(20)
CREATE TYPE phone_no_list AS VARRAY(3) OF phone_no_type;
CREATE OR REPLACE TYPE cust AS OBJECT
The elements of a vararray cannot be accessed individually using SQL, but they can be accessed in PL/SQL and
See the Oracle8 new features overview for a summary of other new features.
Looking to sky-rocket productivity, slash costs and accelerate innovation?
Training is a highly cost-effective, proven method of boosting productivity leaving time, money and staff available for
more innovation. Smartsoft offers instructor-led training in Oracle and related technologies on or off site in cities
across the UK as well as self-study online training. See our training course schedule, or let us know your
Oracle tips and tricks
Subscribe to our newsletter, jam-packed full of tips and tricks to help you slash costs, sky-rocket productivity and
make your systems better, faster and smarter.
Smartsoft Computing Ltd, Bristol, England
Tel: 0845 003 1320
This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such
as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
© Copyright Smartsoft Computing Ltd 2014. All rights reserved.