Oracle Essentials Oracle Database 11g (61 page)

Some of the most popular reasons to use transportable tablespaces include:

• Rapid copying of tablespaces from enterprise data warehouses to data marts

• Copying of tablespaces from operational systems to operational data stores for use in consolidated reporting

• Publishing of tablespaces for distribution on CD-ROM

• Use of backup copies for rapid point-in-time tablespace recovery Oracle9
i
eliminated the restriction that Oracle block sizes needed to be the same in both the source and target databases. Oracle Database 10
g
eliminated the restriction that the source and target databases needed to be running on the same operating system platform.

Moving Data Between Distributed Systems

|

317

Chapter 14

CHAPTER 14

Oracle Extended Datatypes

14

The Oracle database has a rich set of native datatypes, but you may sometimes need to go beyond their capabilities, depending on the specifics of your development and deployment requirements. You can use traditional datatypes, such as those described in
Chapter 4,
to represent a portion of the information that your organization needs to store and manage. Introduction of the XML datatype (described in
Chapter 4)
and support for features such as XMLSchema, an XML DB repository (enabling URL-based access to XML documents stored in Oracle), and SQL/XML (for generating XML documents from SQL) have extended Oracle’s ability to function as a “XML

database.” Oracle also provides datatypes that are specifically designed to provide optimal storage, performance, and flexibility for other specific types of data, the focus of this chapter.

Real-world information used in business, such as purchase orders, claims forms, shipping forms, and so on, may sometimes be best represented as object types, which are more complex than the simple atomic datatypes discussed in
Chapter 4.

Location-oriented data may best be represented using spatial coordinates. Documents, images, video clips, and audio clips have their own special requirements for storage and retrieval.

Oracle has extended the functionality of its basic relational database engine to support the storage and manipulation of these nontraditional datatypes through the introduction of additional features and options. Oracle has also extended the types of data, the SQL that manipulates that data, and the basic Oracle service framework so that you can modify the data and extend its capabilities even further.

Object-Oriented Development

An object-oriented approach to software development shifts the focus from building computing procedures that operate on sets of data to modeling business processes.

Building software components that model business processes with documented interfaces makes programming more efficient and allows applications to offer more
318

flexible deployment strategies. It also makes applications easier to modify when business conditions change. In addition, since the modeling reflects real business use, application performance may improve as objects are built that do not require excessive manipulation to conform to the real-world behavior of the business processes they represent.

Oracle chose to take an evolutionary approach to object technology by allowing
data
abstraction
, or the creation of user-defined datatypes as objects and collections as extensions to the Oracle relational database. The Objects and Extensibility features, included with the database since Oracle8
i
, position Oracle as an object-relational database.

Support of the Java language complements this approach. The JVM (formerly JServer) feature is a Java Virtual Machine integrated with the database. It supports the building and running of Java components, as well as Java stored procedures and triggers, in the server.

The Promise of Code Reuse

Although a number of object-oriented approaches and technologies have been introduced since the 1980s, many of the promised improvements in software development efficiency have largely been unrealized. One of the reasons that these productivity improvements have failed is the difficulty many developers have had in making the adjustment to building reusable components. In addition, the need to learn new languages (such as C++) and technologies (object-oriented databases, CORBA, DCOM, and .NET) slowed the adoption of object-oriented development. Developers did become more familiar with these techniques and skills as Java moved into the main-stream of development. Interestingly, Oracle leverages many of these object features itself in development of new database capabilities.

However, the benefits of code reuse are more likely to be realized in deployment of a Service-Oriented Architecture (SOA) today, which is described in more detail in

Chapter 15.
Oracle’s Application Server/Fusion Middleware is a key component. The database has also evolved in providing web services as we describe in this chapter.

Object-Relational Features

This section describes the major object-relational features available in Oracle.

Objects in Oracle

Objects created in Oracle are reusable components representing real-world business processes. The objects created using the database Objects and Extensibility features occupy the same role as the table in a standard relational model: the object is a template for the creation of individual “instances” of the object, which take the same
Object-Oriented Development

|

319

role as rows within a table. An object is “instantiated” using Oracle-supplied “con-structors” in SQL or PL/SQL.

An
object
consists of a name, one or more attributes, and methods.
Attributes
model the structure and state of the real-world entity, while
methods
model the operations of the entity. Methods are functions or procedures, usually written either in PL/SQL

or Java or externally in a language such as C. Methods make up the interface between an object and the outside programming environment. Each method is identified by the name of the object that contains the method and a method name. Each method can have one or more
parameters
, which are the vehicles for passing data to the method from the calling application.

For example, a purchase order can be represented as an object. Attributes can include a purchase order number, a vendor, a vendor address, a ship-to address, and a collection of items (with their associated quantity and price). You can use a method to add an item to the purchase order, delete an item from the purchase order, or return the total amount of the purchase order.

You can store objects as rows in tables or as values in columns. Each row object has a unique object identifier (OID) created by Oracle. Row objects can be referred to from other objects or relational tables. The REF datatype represents such references.

For column objects, Oracle adds hidden columns for the object’s attributes.

Object views
provide a means of creating virtual object tables from data stored in the columns of relational tables in the database. These views can also include attributes from other objects. Object views are created by defining an object type, writing a query defining the mapping between data and tables containing attributes for that type, and specifying a unique object identifier. When the data is stored in a relational table, the unique identifier is usually the primary key. This implementation means that you can use object-oriented programming techniques without converting existing relational tables to object-relational tables. The tradeoff when using this approach is that performance may be less than optimal, since the data representing attributes for an object may reside in several different tables. Hence, it may make sense to convert the relational tables to object tables in the future.

Objects that share the same attributes and methods are said to be in the same datatype or
class
. For example, internal and external purchase orders can be in the same class as purchase orders.
Collection types
model a number of objects of the same datatype as varying arrays (VARRAYs) if the collection of objects is bounded and ordered or as nested tables if the collection is unbounded and unordered. If a collection has fewer than 4,000 bytes, it is stored as part of the database table; if it is larger, it is stored as a Binary Large Object (BLOB) in a segment separate from the table that is considered

“out-of-line” storage. Nested table rows are stored in a separate table identified through a hidden NESTED_TABLE_ID by Oracle. Typically, VARRAYs are used when an entire collection is being retrieved and nested tables are used when a collection is being queried, particularly if the collection is large and only a subset is needed.

320

|

Chapter 14: Oracle Extended Datatypes

An application can call object methods through SQL, PL/SQL, Pro*C/C++, Java, OCI, and the Oracle Type Translator (OTT). The OTT provides client-side mappings to object types by generating header files containing C structure declarations and indicators. Developers can tune applications by using a client-side object cache to improve performance.

Inheritance
, or the use of one class of objects as the basis for another, more specific class, is one of the most powerful features of object-oriented design. The child class inherits all the methods and attributes of the parent class and also adds its own methods and attributes to supplement the capabilities of the parent class. The great power of inheritance is that a change in a parent class automatically ripples down to the child classes. Object-oriented design supports inheritance over many levels of parent, child, and grandchild classes.

Polymorphism
describes the ability of a child class to supersede or “override” the operation of a parent method by redefining the method on its own. Once a method has been replaced in a child class, subsequent changes to the method in the parent class don’t ripple down to the child class or its descendants. In the purchase order example, as shown in
Figure 14-1,
purchase orders from contracted suppliers and suppliers not under contract inherit the methods and attributes of external purchase orders. However, the procedure for placing the order can exhibit polymorphism because additional approvals may be required for ordering from suppliers not under contract.

Purchase Order

Internal Purchase Order

External Purchase Order

Purchase Order

Purchase Order

Contracted Supplier

Noncontracted Supplier

Figure 14-1. Purchase order class hierarchy

Inheritance and polymorphism were not supported in Oracle8
i
objects, though the Oracle8
i
database could act as persistent storage for objects, and an application interface in an object-oriented language such as C++ or Java could add these features to the client-side implementation of objects. Oracle9
i
added SQL type inheritance to the database, as well as object view hierarchies, type evolution, generic and transient datatypes, function-based indexes for type method functions, and multilevel
Object-Oriented Development

|

321

collections. Oracle Database 10
g
added support for remote access to object types.

Oracle Database 11
g
added an ANSI SQL feature that provides a method invocation scoping operator.

Other extensibility features

Several other extensibility features are included in the Objects and Extensibility features. These include:

• The ability to create new index types by defining the structure of the index

• The ability to store the index data inside or outside the Oracle database

• The ability to create user-defined operators for use in standard SQL statements

• An interface to the cost-based optimizer to extend support for user-defined object types and indexes

The use of object-relational features is most common today among software developers who are building database extensions. Oracle itself has made use of these features in the creation of many of the database features—for example, in the Spatial and Multimedia capabilities.

Java’s Role and Web Services

Java has gained wide acceptance as an application language, particularly for building web-based applications, due to its portability and availability on a wide variety of platforms.

For Java developers wanting to use the Oracle database as a backend to their applications, Oracle first offered support for JDBC 3.0 in Oracle Database 10
g
and continued to offer support for the two common approaches to accessing the database from a Java program: JDBC and SQLJ. Both of these approaches are based on industry-standard application program interfaces (APIs):

JDBC

More commonly used since it can be used where SQL is dynamic, or when a developer wants explicit control over interactions with the database.

SQLJ

An industry standard typically used when static SQL statements have been embedded into a Java program. SQLJ is similar to other Oracle precompilers in that Java source files are created with calls to the SQLJ runtime (as well as to additional profile files). The Java source code is then compiled, and the application is run with the SQLJ runtime library.

SQLJ and JDBC can be mixed in the same program when some SQL is static and other SQL is dynamic.

322

|

Chapter 14: Oracle Extended Datatypes

The Oracle JVM in Oracle9
i
and later releases (formerly JServer in Oracle8
i
) introduced additional component- and object-based development options. Oracle9
i
and subsequent versions feature a tightly integrated Java Virtual Machine (hence the JVM

name) and support for Java stored procedures in the database; these enable component-based development to take place through the use of Enterprise JavaBeans (EJBs). Java Messaging Support (JMS) is provided through Oracle Streams.