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: • 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. For example: 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. 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 requirements. 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 Contact Us View our privacy policy 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 woopra privacy policy.  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.