For Better, Faster, Smarter,
Oracle8 Object Support (part 3)
Part 1 of this discussion of the new object features in Oracle8 looked at nested tables and varrays. Part 2 looks at user
defined types and this part (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.
Methods in User Defined Types
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
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
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
addr_type created in Oracle 8 object support part 1, as in the following
CREATE OR REPLACE TYPE address_type AS OBJECT
,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 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 in Oracle8
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.
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.