7
Frequently Asked Questions about Programming with Oracle Objects

Here are some questions and answers that new users often have about Oracle's object-relational features:

• General Questions about Oracle Objects

• Object Types

• Object Methods

• Object References

• Collections

• Object Views

• Object Cache

• Large Objects (LOBs)

• User-Defined Operators

You can use this chapter as introductory information, or refer here if you still have questions after reading the rest of the book.

General Questions about Oracle Objects

Are the object-relational features a separate option?

Not anymore. As of Version 8.1, they are part of the base server product.

What are the design goals of Oracle8i Object-Relational & Extensibility technologies?

The design goals of Oracle8i Objects and Extensibility technologies are to:

• Provide users with the ability to model their business objects in the database by enhancing the type system to support user-defined types. These types are meant to closely model application objects and are treated as built-in types, such as number and character, by the database server.

• Provide an infrastructure to facilitate object-based access to data stored in an Oracle database and minimize the potential mismatch between the data model used in an application and the data model supported by a database.

• Provide built-in support for new data types needed in multi-media, financial and spatial applications.

• Provide a framework for database extensibility so that new multimedia and complex data types can be supported and managed natively in the database. This framework provides the infrastructure needed to allow extensions of the data server by third parties via data cartridges.

This book talks about the object-relational technologies. For details about extensibility, see Oracle8 Data Cartridge Developer's Guide.

What are the key features in Oracle8i Object-Relational Technology?

Building on the standard features of the Oracle data server, the Oracle8i Objects is an additional set of features that enable the creation and manipulation of user-defined object types:

Object Type System

Oracle8i provides the capability to define new types in the database via a new meta-model, simply called the Oracle8i Type System. This type system extends the Oracle relational types to allow users to define types and methods that model objects. Oracle8i stores the meta-data for user-defined types in a schema that is available to SQL, PL/SQL, Java and other published interfaces. A user can create a new object type by using any built-in database types, and/or any known object types, object references and collection types.

Object Views

In addition to natively storing object data in the server, Oracle8i allows the creation of an object abstraction over existing relational data via the object view mechanism. Objects belonging to an object view are accessed in the same manner as row objects via SQL or other call interfaces. To support such data access, Oracle8i server materializes objects of user-defined types from data stored in relational schemas and tables. This view mechanism supports the development of new object-oriented applications without having to modify existing database schemas.

SQL Object Extensions

To support the new features of the Objects Option, extensions have been made to SQL -- including new DDL -- to create, alter, or drop object types; to store object types in tables; and to create, alter, or drop object views. There are DML and query extensions to support object types, references, and collections.

PL/SQL Object Extensions

PL/SQL is Oracle's database programming language that is tightly integrated with SQL. With the addition of user-defined types and other SQL types introduced in Oracle8i, PL/SQL has been enhanced to operate on user-defined types seamlessly. Thus, application developers can use PL/SQL to implement logic and operations on user-defined types that execute in the database server.

Java Support for Oracle8i Objects

Oracle8i's Java VM is tightly integrated with the RDBMS and supports access to Oracle8i Objects via object extensions to JDBC (dynamic SQL) and SQLJ (static SQL). Thus, application developers can use the Java to implement logic and operations on user-defined types that execute in the database server.

External Procedures

Database functions, procedures, or member methods of an object type can be implemented in PL/SQL, Java, or C as external procedures. External procedures are best suited for tasks that are more quickly or easily done in a low-level language such as C, which is more efficient at machine-precision calculation. External procedures are always run in a safe mode outside the address space of the RDBMS server.

Object Type Translator

The Object Type Translator (OTT) available with the Objects Option provides client-side mappings to object type schema by generating header files containing Java classes and C structs and indicators, using schema information from the Oracle data dictionary. These generated header files can be used in host-language applications for transparent access to database objects.

Client-Side Cache

Oracle8i provides an object cache for efficient access to persistent objects stored in the database. Copies of objects can be brought into the object cache. Once the data has been cached in the client, the application can traverse through these at memory speed. Any changes made to objects in the cache can be committed to the database by using the object extensions to Oracle Call Interface programmatic interfaces.

Complex Object Retrieval

Oracle8i also provides support for efficient complex object retrieval. That is, a single request to fetch an object from the server can be used to retrieve other objects, which are connected to the object being fetched via object references, in a single round-trip between the client and the server. Such functionality facilitates the fetching and manipulation of a set of related objects as a single unit.

OCI Object Extensions

Oracle8i Oracle Call Interface provides a comprehensive application programming interface for application and tool developers seeking to use the object capabilities of Oracle8. Oracle Call Interface provides a run-time environment with functions to connect to an Oracle8 server and control transactions that access objects in the server. It allows application developers to access and manipulate objects and their attributes in the client-side object cache either "navigationally" by traversing a graph of inter-connected objects or "associatively" by specifying the nature of the data via declarative SQL DML. Oracle Call Interface also provides a number of functions for accessing meta-data information at run-time about object types defined in the server. Such a set of functions facilitates dynamic access to the object meta-data and the actual object data stored in the database.

PRO*C/C++ Object Extensions

Oracle8i Pro*C precompiler provides an embedded SQL application programming interface and offers a higher level of abstraction than Oracle Call Interface. Like Oracle Call Interface, the Pro*C precompiler allows application developers to use the Oracle8i client-side object cache and the Object Type Translator Utility. Pro*C supports the use of "C" bind variables for Oracle8i object types. Furthermore, Pro*C provides new simplified syntax to allocate and free objects of SQL types and access them by either SQL DML, or via the "navigational" interface. Thus, it provides application developers many benefits, including compile-time type checking of (client-side) bind variables against the schema in the server, automatic mapping of object data in an Oracle8i server to program bind variables in the client, and simple ways to manage and manipulate database objects in the client process.

OO4O Object Extensions

Oracle8i Oracle Objects For OLE (OO4O) is a set of COM Automation interfaces/objects for connecting to Oracle8i database servers, executing queries and managing the results. Automation interfaces in OO4O provide easy and efficient access to features that are unique to Oracle8i, and can be used from virtually any programming or scripting language that supports the Microsoft COM Automation technology. This includes Visual Basic, Visual C++, VBA in Excel, VBScript and JavaScript in IIS Active Server Pages.

Integration with Relational Functionality

Oracle8i Objects continues to support standard relational database functionality such as queries (SELECT...FROM...WHERE), fast commits, backup and recovery, scalable connectivity, row-level locking, read consistency, partitioned tables, parallel queries, parallel server, export/import, loader etc.

What are the new Object-Relational features in Oracle8i?

Oracle8i provides the foundation for modeling complex objects. Here is a list of object-relational features, with the new 8i features in italics:

Type System

Scalars, LOBs, Objects, References, Collections

Execution Environment

PL/SQL methods, External Procedures, Java Methods

Query Processing

Triggers, Constraints, Object Views, User-Defined Operators

Data Indexing

Sorted, Hash, Bitmap, Index-Organized Tables, Extensible Indexing

Query Optimization

Object Query Optimization, Extensible Optimizer

Operational Completeness

Object Support in Export/Import, Loader, Parallel Query, Partitioning

Language Interfaces

Object Support in OCI, C++ (ODD), Pro*C, JDBC, OO4O

Object Types

What is structured data?

The SQL 92 standard defines the 19 atomic datatypes that are used in most database programming. We refer to these kinds of data as "simple structured".

Oracle Objects introduces the ideas of REFs and collections. We refer to these kinds of data as "complex structured".

LOBs provide another way to store information. We refer to them as "unstructured".

Where are the user-defined types, user-defined functions, and abstract data types?

The Oracle equivalent of a user-defined type or an abstract data type is an object type.

The Oracle equivalent of a user-defined function is an object type method.

We use these terms because their semantics are different from the common industry usage. For example, an Oracle object can be null, while an object of an abstract data type cannot.

What is an object type?

Oracle8i supports a form of user-defined data types called object types. Object types are abstractions of real-world entities. An object type is a schema object with the following components:

• A name, which identifies the object type uniquely within a schema

• Attributes, which model the structure and state of the real-world entity

• Methods, which implement the behavior of the real-world entity

Why are object types useful?

An object type is similar to the class mechanism supported by C++ and Java. Object reusability provides faster and more efficient database application development. Object support makes it easier to model complex, real-world business entities and logic. By supporting object types natively in the database, Oracle8i eliminates the impedance mismatch between object-oriented programming languages and the database, and relieves application developers from the need to write a mapping layer between client-side objects and database objects. Object abstraction and encapsulation also make it easier to understand and maintain applications, an important consideration in enterprise database application development.

How is object data stored and managed in Oracle8i?

Objects are managed natively by the data server. Object types can be used as the type of a column (column objects) or as type of each row in an object table (row objects). When used as column objects, object types serve as classical relational domains. Each row object has a unique identity, called an object identifier (OID).

Objects are first-class citizens and are fully integrated with the database components. They can be indexed and partitioned. For example, queries involving objects can be parallelized and are optimized by the cost-based optimizer using statistics.

By building on the proven foundation of the Oracle8i data server, objects are managed with the same reliability, availability, and scalability as relational data.

Is inheritance supported in Oracle8i?

Not directly. Because each language has its own semantics for inheritance -- for example, single inheritance versus multiple inheritance -- Oracle uses techniques that mimic the inheritance behavior of each language.

Oracle8i provides support for client-side inheritance via its C++ and Java mappings. For C++, use the Object Modelling Option of Oracle Designer to produce DDL and C++ code based on diagrams in the Universal Modelling Language (UML). For Java, use the "custom datum" feature of the Oracle JDBC driver.

Server-side method inheritance is provided in Java by the Oracle8i Java VM. Oracle8i does not currently support inheritance in SQL, the ability to store and query instances of a type and its subtypes.

Object Methods

What language can I use to write my object methods?

Methods can be implemented in PL/SQL, Java, C or C++. C & C++ support is via the external procedure functionality in Oracle8i, whereas PL/SQL and Java methods run within the address space of the server. De-coupling of the specification of a method in SQL from its implementation provides a uniform way to invoke methods on object types, even though these object types can be implemented in various programming languages. Oracle8i provides a safe and secure environment for invoking PL/SQL methods, Java methods, and external C procedures from the server. Programming errors in user methods will not crash the server or corrupt the database, thus ensuring the reliability and availability of the server in a mission critical environment.

How do I decide between using PL/SQL and Java for my object methods?

With Oracle8i, PL/SQL and Java can be used interchangeably as a server programming language. PL/SQL is a seamless extension of SQL and is the language of choice for doing SQL intensive operations in the database. Java is the emerging language of choice for writing portable applications that run in multiple tiers, including the database server.

When should I use external procedures?

External procedures are typically used for compute intensive operations that are best written in a low-level language such as C. External procedures are also useful for invoking routines in some existing libraries that cannot be easily rewritten in Java or PL/SQL to run in the data server.

The IPC (inter-process communication) overhead of invoking an external procedure is an order of magnitude higher than that of invoking PL/SQL or Java procedure. However, the overhead of invoking an external procedure become insignificant if the computation done in the external procedure is complex and is in the order of tens of thousands of instructions.

What are definer and invoker rights?

The distinction between definer and invoker rights applies to more than just objects. You may find invoker rights especially useful for object-oriented programs because they typically contain reusable modules.

An object method can be executed with the privileges of its owner (definer rights) or with the privileges of the current user (invoker rights), based on the method definition. Invoker rights are useful for writing reusable objects because users of these objects do not have to grant access privileges to their tables to the implementor of the objects. Definer rights are useful when the as part of the object implementation, the object methods need to access some meta-data maintained by the object implementor. Methods that access the meta-data are executed using the definer rights so that the object implementor does not have to expose the proprietary meta-data to the users.

Object References

What is an object reference?

An object reference (REF) uniquely identify a row object stored in an object table or an object constructed from an object view. Typically, a REF value is comprised of the object's unique identifier, the unique identifier associated with the object table, and the ROWID of the row in the object table in which the object is stored. The optional ROWID is used as a hint to provide fast access to the object.

When should I use object references? How are they different from foreign keys?

Object references, like foreign keys, are useful in modeling complex relationships. Object references are more flexible than foreign keys for modeling complex relationships because:

• Object references are strongly typed and this provides better compile-time type checking

• One-to-many relationships can be modeled using a collection of object references

• Application can easily navigate and retrieve objects using object references without having to construct SQL statements

• REF navigation in SQL avoids the need to do complicated multi-table joins

• Object references allow applications to retrieve objects connected by REFs in a single request to the server

Can I construct object references based on primary keys?

Yes, object references can be constructed based on foreign keys to reference objects in:

• Object views: When constructing objects from relational tables using an object view, the OIDs of the constructed objects are typically based on the primary keys on the underlying relational tables.

• Object tables with primary key-based OIDs: When defining an object table, Oracle8i provides the option of specifying the primary keys as the OIDs of the row objects instead of using the system generated OIDs.

What is a scoped REF and when should I use it?

In general, a column may contain references to objects of a particular declared type regardless of the object table(s) in which the objects are stored. However, a REF type column may be scoped (constrained) to only contain references to objects from a specified object table. One should use scoped REFs whenever possible because scoped REFs are smaller in size than regular REFs on disk because the system does not have to store the table identifier with the scoped REFs. Also, queries containing navigation of scoped REFs can be optimized into joins when appropriate.

Can I manipulate objects using object references in PL/SQL and Java?

Yes, both PL/SQL and Java support object references. In PL/SQL, an object can be retrieved and updated using the UTL_REF package given its object references. In Java, object references are mapped to reference classes with get and set methods to retrieve and update the objects.

Collections

What kinds of collections are supported by Oracle8i?

Oracle8i supports two types of collections: varying arrays (varrays) and nested tables. Attributes of object types and columns of tables can be of collection types. By using varying arrays and nested tables, applications can model one-to-many and many-to-many relationships natively in their database schema.

How do I decide between using varrays and nested tables for modeling collections?

Varrays are useful when you need to maintain ordering of your collection elements. Varrays are very efficient when you always manipulate the entire collection as a unit, and that you don't require querying on individual elements of the collections. Varrays are stored inline with the containing row if it is small and automatically stored as a LOBs by the system when its size is beyond a certain threshold.

Nested tables are useful when there is no ordering among the collection elements and that efficient querying on individual elements are important. Elements of a collection type column are stored in a separate table, similar to parent-child tables in a relational schema.

Do Oracle8i Objects support collections within collections?

A collection cannot contain another collection attribute. However, nested collections can be modeled using a reference to an object which has a collection attribute. Therefore applications can model nested collections with REF indirection.

What is a collection locator?

Collection locators allow applications to retrieve large collections without materializing the collections in memory. This allows for efficient transfer of large collections across interfaces. A collection will be transparently materialized when the application first accesses its elements. Also, applications can query and retrieve subsets of the collection using its locator.

The specification of retrieval of collection locators is done in CREATE and ALTER TABLE DDL. Since access to a collection is encapsulated, applications will use the same interface to retrieve a nested table specified to be returned as a locator as one specified to be returned as a value.

What is collection unnesting?

Collection unnesting allows applications to efficiently query over a set of collections in some specified rows, similar to query on the child rows in a relational schema for some specified parent rows. Collection unnesting allows applications the flexibility to view one-to-many relationships in the collection form or in the flat parent-child form.

Object Views

What are the differences between object views and object tables?

Like the similarity between relational views and tables, an object view has properties similar to an object table:

• It contains objects in rows. The columns of the view map to top-level attributes of the object type.

• Each object has an identifier associated with it. The identifier is specified by the view definer; in most cases, the primary key of the base table serves as the identifier.

Are object views updateable?

It is easy to update an object view where every attribute maps back to a real column in a table. For views that derive some attributes by more complex techniques, such as CAST-MULTISET, INSTEAD-OF triggers can be used to do the updates. When such a view is updated (or inserted into or deleted from), rather than attempting to implicitly modify any base tables, the system simply invokes the INSTEAD-OF trigger specified for the view. You can encapsulate whatever update semantics you want in the trigger body.

Object Cache

Why do we need the object cache?

The object cache gives applications the following benefits:

• Transparent mapping of database objects to host language objects in memory.

• Transparent, efficient memory management for persistent objects. Applications do not have to worry about allocation of memory for accessing database objects.

• Transactional semantics for client-side objects. Modified persistent objects in the object cache can be propagated (flushed) to the database in a single round-trip between the client and the server.

• Navigational object access. Object cache allows for navigational style of object access. Using OCI's object functions, objects can be fetched into the object cache by pinning object REFs. Navigational style of object access may be more suitable when operating on a graph of objects that are inter-connected via object REFs.

• Complex object retrieval. That is, a single request to fetch an object from the server can be used to retrieve other objects, which are connected to the object being fetched via REFs, in a single round-trip between the client and the server.

Does the object cache support object locking?

The object cache supports both a pessimistic locking scheme and an optimistic locking scheme.

• In the pessimistic locking scheme, objects are locked up-front in the server prior to modifying the object in the cache. This ensures no other user can modify the object until the transaction owning the lock commits/rollbacks.

• In the optimistic locking scheme, an object is fetched and modified in the cache without acquiring a lock. The lock is acquired only when the object is flushed to the server. Optimistic locking allows for a higher degree of concurrent access than pessimistic locking. To use optimistic locking effectively, the object cache provides the ability for detecting if an object was changed by any other user since it was fetched into the cache. By turning on the "object change detection mode", object modifications will be made persistent if the nobody else has changed the object since it was fetched into the cache.

Large Objects (LOBs)

How can I manage large objects using Oracle8i?

Support for multimedia data types like text, images, audio, and video requires robust support for binary and character data. The data in these domains tends to be large and requires direct access to different pieces of the binary data. To address this need, Oracle8i provides significantly improved support for large-scale binary and character data. It introduces the Large Object type (LOB) which can be used to store large, domain-specific data from various domains, including images, audio files, text and spatial data.

Oracle8i supports three kinds of large data objects: binary, character-based, and file-based. In addition to providing the ability to create LOBs, Oracle8i server provides several other improvements in managing binary data. These improvements can be summarized as follows:

• Support for defining more than one LOB column in a table

• Random, piece-wise access to LOB data

• Support for transferring LOB data as a single stream

• Support for disabling logging and/or caching for LOB data

• Support for transparently moving LOBs from "in-line" row storage to "out-of-line" storage in another segment or even another tablespace

For more information about LOBs, see Oracle8i Application Developer's Guide - Large Objects (LOBs).

User-Defined Operators

What is a user-defined operator?

Oracle8i allows developers of object-oriented applications to extend the list of built-in relational operators (for example, +, -, /, *, LIKE) with domain specific operators (for example, Contains, Within_Distance, Similar) called user-defined operators. A user-defined operator can be used anywhere built-in operators can be used, for example, in the select list or the where clause. Similar to built-in operators, a user-defined operator may support arguments of different types, and that it may be evaluated using an index.

For more information about user-defined operators, see CREATE OPERATOR in the Oracle8i SQL Reference, and the Oracle8 Data Cartridge Developer's Guide.

Why are user-defined operators useful?

Similar to built-in operators, user-defined operators allow efficient content-based querying and sorting on object data. For example, to find a resume containing certain qualifications, one may specify the Contains operator as part of the SQL where clause. The optimizer may choose to use a Text index on the resume column to perform the query efficiently, similar to using a B-tree index to evaluate a relational operator.