Oracle8 Object Support (part 2)

Part 1 of this discussion of the new object features in Oracle8 looked at nested tables and varrays. This part looks at user defined types and part 3 looks at methods in user-defined types before moving on to object views and the object extensions to SQL , PL/SQL and 3GL interfaces.

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 defined as: CREATE OR REPLACE TYPE addr AS OBJECT (line1 VARCHAR2(80) ,line2 VARCHAR2(80) ,post_code VARCHAR2(10)); A normal relational table – customer - using this type could be defined as: CREATE TABLE customer ( Id      NUMBER(12), Name    VARCHAR2(75), Addr    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.addr.post_code, cus.address.country FROM customer cus; Updates also require the use of the dot notation UPDATE customer cus SET cus.addr.post_code = ‘NW1 3DR’ Inserts are accomplished by use of the constructor that is created by Oracle automatically when the type is defined. INSERT INTO customer VALUES (1,'Oracle',addr_type('5 Redclif','Bath','BA1 6SQ'); Object Tables These are based solely on an object type and accessed with SQL in the same way as a relational table. For example, using the previous definition of addr_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); 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; See the Oracle8 new features overview for a summary of other new features, object support part 1 to learn about nested tables and varrays or object support part 3 to learn about methods in user-defined types, object views and the extensions to SQL , PL/SQL and 3GL interfaces. ----------------------------------------------------------- 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.