Oracle9i Application Developer’s Guide – Object-Relational Features

Gietz B.

Oracle9i Application Developer’s Guide – Object-Relational Features is intended for programmers developing new applications or converting existing applications to run in the Oracle environment. The object-relational features are often used in multimedia, Geographic Information Systems (GIS), and similar applications that deal with complex data. The object views feature can be valuable when writing new applications on top of an existing relational schema.This guide assumes that you have a working knowledge of application programming and that you are familiar with the use of Structured Query Language (SQL) to access information in relational database systems.

---

Table of contents :
Contents……Page 3
Send Us Your Comments……Page 15
Preface……Page 17
New Object-Relational Features……Page 25
1 Introduction to Oracle Objects……Page 29
Advantages of Objects……Page 30
Key Features of the Object-Relational Model……Page 32
2 Basic Components of Oracle Objects……Page 37
Object Types……Page 38
Object Tables……Page 39
Object Views……Page 40
Dangling REFs……Page 41
Collections……Page 42
Defining Object and Collection Types……Page 43
Null Objects and Attributes……Page 44
Default Values for Objects and Collections……Page 45
Constraints for Object Tables……Page 46
Indexes for Object Tables and Nested Tables……Page 47
Rules for REF Columns and Attributes……Page 48
When Table Aliases are Required……Page 49
Methods……Page 51
Member Methods……Page 52
Map Methods……Page 53
Order Methods……Page 54
Comparison Methods in Type Hierarchies……Page 55
Constructor Methods……Page 56
Varrays……Page 57
Nested Tables……Page 58
Multi-Level Collection Types……Page 59
Nested Table Storage Tables……Page 60
Varray Storage……Page 61
Creating a VARRAY or Nested Table……Page 62
Querying Collections……Page 63
Unnesting Results of Collection Queries……Page 64
Unnesting Queries Containing Table Expression Subqueries……Page 66
Performing DML Operations on Collections……Page 67
Performing DML on Multi-Level Collections……Page 68
Type Inheritance……Page 69
Types and Subtypes……Page 70
Creating Subtypes……Page 72
NOT INSTANTIABLE Types and Methods……Page 73
Overloading Methods……Page 74
Overriding Methods……Page 75
Dynamic Method Dispatch……Page 76
Substituting Types in a Type Hierarchy……Page 77
Attribute Substitutability……Page 78
Column and Row Substitutability……Page 79
Subtypes Having Supertype Attributes……Page 80
Turning Off Substitutability……Page 81
Objects and REFs to Objects……Page 82
Comparing Object Instances……Page 84
VALUE……Page 85
DEREF……Page 86
TREAT……Page 87
IS OF type……Page 88
SYS_TYPEID……Page 90
3 Object Support in Oracle Programmatic Environments……Page 93
Oracle Call Interface (OCI)……Page 94
Associative Access in OCI Programs……Page 95
Object Cache……Page 96
Building an OCI Program that Manipulates Objects……Page 97
OCCI Associative Relational and Object Interfaces……Page 98
Pro*C/C++……Page 99
Converting Between Oracle Types and C Types……Page 100
Oracle Objects For OLE (OO4O)……Page 101
Representing Objects in Visual Basic (OraObject)……Page 102
Java: JDBC, Oracle SQLJ, JPublisher, and SQLJ Object Types……Page 103
SQLJ Access to Oracle Object Data……Page 104
What JPublisher Produces……Page 105
Java Object Storage……Page 106
Representing SQLJ Types to the Server……Page 107
Sample SQLJ Object Type Mapping……Page 108
Evolving SQLJ Types……Page 114
Querying SQLJ Objects……Page 115
Updating SQLJ Objects……Page 116
4 Managing Oracle Objects……Page 117
Schema Object Privileges……Page 118
Example……Page 119
Privileges on Type Access and Object Access……Page 120
Dependencies and Incomplete Types……Page 121
Type Dependencies of Substitutable Tables and Columns……Page 123
JDeveloper……Page 124
JPublisher……Page 125
SQL*Loader……Page 126
5 Applying an Object Model to Relational Data……Page 129
Why to Use Object Views……Page 130
Defining Object Views……Page 131
Nesting Objects in Object Views……Page 132
Single-Level Collections in Object Views……Page 134
Multi-Level Collections in Object Views……Page 136
Specifying Object Identifiers for Object Views……Page 137
Creating References to View Objects……Page 139
Modelling Inverse Relationships with Object Views……Page 140
Updating Object Views……Page 141
Using INSTEAD OF Triggers to Control Mutating and Validation……Page 142
Applying the Object Model to Remote Tables……Page 143
Defining Complex Relationships in Object Views……Page 144
Tables and Types to Demonstrate Circular View References……Page 145
Creating Object Views with Circular References……Page 146
Object View Hierarchies……Page 149
Creating an Object View Hierarchy……Page 150
The Flat Model……Page 151
The Horizontal Model……Page 153
The Vertical Model……Page 155
Querying a View in a Hierarchy……Page 156
Privileges for Operations on View Hierarchies……Page 157
6 Advanced Topics for Oracle Objects……Page 159
Hidden Columns for Tables with Column Objects……Page 160
Hidden Columns for Substitutable Columns and Tables……Page 161
Internal Layout of Nested Tables……Page 162
Indexing a Type Discriminant Column……Page 163
Indexing Subtype Attributes of a Substitutable Column……Page 164
Object Identifiers……Page 165
Type Evolution……Page 166
Changes Required by a Change to a Type……Page 168
Steps to Change a Type……Page 169
Validating a Type……Page 170
If a Type Change Validation Fails……Page 173
ALTER TYPE Options for Type Evolution……Page 174
ALTER TABLE Option for Type Evolution……Page 177
Initializing an OCI Program in Object Mode……Page 178
Creating a New Object……Page 179
Retrieving Objects into the Client Cache (Pinning)……Page 180
Specifying which Version of an Object to Retrieve……Page 181
Specifying Whether to Lock the Object on the Server……Page 182
Pre-Fetching Related Objects (Complex Object Retrieval)……Page 183
Demonstration of OCI and Oracle Objects……Page 185
Using the OCI Object Cache with View Objects……Page 186
Transient and Generic Types……Page 188
User-Defined Aggregate Functions……Page 189
Partitioning Tables that Contain Oracle Objects……Page 190
Parallel Query with Object Views……Page 191
How Locators Improve the Performance of Nested Tables……Page 192
7 Frequently Asked Questions About Using Oracle Objects……Page 193
What is structured data?……Page 194
How is object data stored and managed in Oracle9i?……Page 195
How do I decide between using PL/SQL and Java for my object methods?……Page 196
What is an object reference?……Page 197
What is a scoped REF and when should I use it?……Page 198
What is a collection locator?……Page 199
Are object views updateable?……Page 200
Does the object cache support object locking?……Page 201
What is a user-defined operator?……Page 202
Why are user-defined operators useful?……Page 203
8 Design Considerations for Oracle Objects……Page 205
Column Object Storage……Page 207
Row Object Storage in Object Tables……Page 211
Performance of Object Comparisons……Page 212
Storage Considerations for Object Identifiers (OIDs)……Page 213
Performance and Storage Considerations for Scoped REFs……Page 214
Indexing Scoped REFs……Page 215
Viewing Object Data in Relational Form with Unnesting Queries……Page 216
Using Procedures and Functions in Unnesting Queries……Page 218
Performance of Varrays Versus Nested Tables……Page 219
Nested Table Storage……Page 220
Nested Table in an Index-Organized Table (IOT)……Page 221
Nested Table Indexes……Page 223
Nested Table Locators……Page 224
DML Operations on Nested Tables……Page 225
Multi-Level Collections……Page 227
Choosing a Language for Method Functions……Page 233
Static Methods……Page 234
Writing Reusable Code using Invoker Rights……Page 235
Function-Based Indexes on the Return Values of Type Methods……Page 237
Converting to the Current Object Format……Page 238
Replicating Object Tables……Page 239
Constraints on Objects……Page 240
Changing Default Constructors……Page 241
Performance Tuning……Page 242
Deciding Whether to Evolve a Type or Create a Subtype Instead……Page 243
Polymorphic Views: An Alternative to an Object View Hierarchy……Page 244
What is the intended use of SQLJ Object Type?……Page 245
When would you use Custom Object Type?……Page 246
What are the differences between the SQLJ and Custom Object Types through JDBC?……Page 247
PL/SQL and TREAT and IS OF……Page 248
9 A Sample Application Using Object-Relational Features……Page 249
Introduction……Page 250
A Purchase Order Example……Page 251
Implementing the Application Under The Relational Model……Page 252
Creating Tables Under the Relational Model……Page 253
Customer_reltab……Page 254
LineItems_reltab……Page 255
Inserting Values Under the Relational Model……Page 256
Querying Data Under The Relational Model……Page 257
Implementing the Application Under The Object-Relational Model……Page 258
Defining Types……Page 260
The sumLineItems Method……Page 267
The compareCustOrders Method……Page 268
The Object Table Customer_objtab……Page 269
Object Datatypes as a Template for Object Tables……Page 271
Object Tables with Embedded Objects……Page 272
The Object Table PurchaseOrder_objtab……Page 273
Inserting Values……Page 280
Querying……Page 283
Average Discount across all Line Items of all Purchase Orders……Page 284
Using oracle.sql.* Classes (Weak Typing)……Page 285
Generating Wrapper Classes with JPublisher……Page 287
How to Use the Wrapper Classes……Page 288
Sample Program Using the SQLData Interface……Page 289
Manipulating Objects with Oracle Objects for OLE……Page 290
Selecting Data……Page 291
Inserting Data……Page 292
Updating Data……Page 294
Calling a Method Function……Page 295
Index……Page 299