Oracle Call Interface Programmer's Guide, Volumes 1 & 2 Release 8.0 A58234-01 |
Library | Product | Contents | Index

# 8
OCI Object-Relational Programming

This chapter introduces the OCI's facility for working with objects in an Oracle8 server. It also discusses the OCI's object navigational function calls.

This chapter includes the following sections:

- Chapter Overview
- OCI Object Overview
- Working with Objects in the OCI
- Developing an OCI Object Application

  Note: The functionality described in this chapter is only available if you have purchased the Oracle8 Enterprise Edition with the Objects Option.

## Chapter Overview

This chapter is divided into three sections covering the basic concepts involved in writing OCI applications to manipulate Oracle8 objects. The chapter also covers the OCI navigational function calls.

The following specific sections are included:

- OCI Object Overview presents a brief introduction to the OCI facilities for working with objects.
- Working with Objects in the OCI describes the basic structure of an OCI object application and the different types of objects with which the OCI works. This section provides a foundation upon which the rest of the chapter builds.
- Developing an OCI Object Application discusses each of the main elements of an OCI object application in more detail. Simple examples illustrate the most important points.

The next four chapters contain additional information about using the OCI to work with objects:

- Chapter 9, "Object-Relational Datatypes", discusses the datatypes used by OCI object-relational applications. This information supplements that found in Chapter 3, "Datatypes". This chapter also includes a discussion of the OCI datatype mapping and manipulation functions.
- Chapter 10, "Binding and Defining in Object Applications", discusses information about bind and define operations specific to object-relational datatypes. This information supplements that in Chapter 2, "OCI Programming Basics", and Chapter 5, "Binding and Defining".
- Chapter 11, "Object Cache and Object Navigation", discusses the object cache and object navigation. This chapter includes a discussion of the OCI navigational functions.
- Chapter 12, "Using the Object Type Translator" discusses the Object Type Translator.

Complete descriptions of all of the OCI object-relational functions are contained in Chapter 14, "OCI Navigation and Type Functions", and Chapter 15, "OCI Datatype Mapping and Manipulation Functions". Additionally, some object functionality is included in those functions contained in Chapter 13, "OCI Relational Functions".

## OCI Object Overview

The Oracle Call Interface (OCI) provides functions for managing database access and processing SQL statements. These functions are described in detail in Part 1 of this book. The SQL capabilities of the OCI relational interface allow an application to access objects from an Oracle8 server through SQL statements.

  Note: The Oracle8 OCI libraries are supported only for C.

The OCI allows applications to access any of the datatypes found in the Oracle8 server, including scalar values, collections, and instances of any object type. This includes all of the following:

- objects
- variable-length arrays (VARRAYs)
- nested tables (multisets)
- references (REFs)
- LOBs

In order to take full advantage of Oracle8 server object capabilities, most applications need to do more than just access objects. Once the object has been retrieved, the application must navigate through references from that object to other objects. The OCI provides the capability to do this.

Through the OCI's object navigational calls, an application can perform any of the following functions on Oracle8 objects:

- creating, accessing, locking, deleting, copying, and flushing objects
- getting references to the objects and their meta-objects
- dynamically getting and setting values of objects' attributes

The OCI navigational calls are discussed in more detail later in this chapter.

The OCI also provides the ability to access type information stored in an Oracle8 database. The OCIDescribeAny() function enables an application to access most information relating to types stored in the database, including information about methods, attributes, and type meta-data.

OCIDescribeAny() is discussed in Chapter 6, "Describing Schema Metadata".

Applications interacting with Oracle8 objects need a way to represent those objects in a host language format. Oracle8 provides a utility called the Object Type Translator (OTT), which can convert type definitions in the database to C struct declarations. The declarations are stored in a header file that can be included in an OCI application.

When type definitions are represented in C, the types of attributes are mapped to special C variable types that are new to Oracle8. The OCI includes a set of datatype mapping and manipulation functions that enable an application to manipulate these datatypes, and thus manipulate the attributes of objects. These functions are discussed in more detail in Chapter 9, "Object-Relational Datatypes".

The terminology for objects can occasionally become confusing. In the remainder of this chapter, the terms object and instance both refer to an object that is either stored in the database or is present in the object cache.

## Working with Objects in the OCI

Many of the programming principles that govern a relational OCI application (as discussed in Chapters 2 through 6) are the same for an object-relational application. An object-relational application uses the standard OCI calls to establish database connections and process SQL statements. The difference is that the SQL statements issued retrieve object references (or objects by value), which can then be manipulated with the OCI's object functions.

### Basic Object Program Structure

The basic structure of an OCI application that uses objects is essentially the same as that for a relational OCI application, as described in the section "OCI Program Structure" on page 2-3. That paradigm is reproduced here, with extra information covering basic object functionality.

1. Initialize the OCI programming environment.

   Note: You must initialize the environment in object mode.

   Your application will most likely also need to include C struct representations of database objects in a header file. These structs can be created by the programmer, or, more easily, they can be generated by the Object Type Translator (OTT), as described in Chapter 12, "Using the Object Type Translator".

2. Allocate necessary handles, and establish a connection to a server.
3. Prepare a SQL statement for execution. This is a local (client-side) step, which may include binding placeholders and defining output variables. In an object-relational application, this SQL statement should return a reference (REF) to an object.

   Note: It is also possible to fetch an entire object, rather than just a reference (REF). If you SELECT a referenceable object, rather than pinning it, you get that object "by value". Alternately, you can select a non-referenceable object, as described in "Fetching Embedded Objects" on page 8-15

4. Associate the prepared statement with a database server, and execute the statement.
5. Fetch returned results.

   In an object-relational application, this step entails retrieving the REF, and then pinning the object to which it refers. Once the object is pinned, your application will do some or all of the following:

   - Manipulate the attributes of the object and mark it as "dirty"
   - Follow a REF to another object or series of objects
   - Access type and attribute information
   - Navigate a complex object retrieval graph
   - Flush modified objects to the server
6. Commit the transaction. This step implicitly flushes all modified objects to the server and commits the changes.
7. Free statements and handles not to be reused or reexecute prepared statements again.

All of these steps are discussed in more detail in the remainder of this chapter.

  See Also: For information about using the OCI to connect to a server, process SQL statements, and allocate handles, see Chapter 2 and the description of the OCI relational functions in Chapter 13.

  For information about the OTT, refer to the section "Representing Objects in C Applications" on page 8-8, and Chapter 12, "Using the Object Type Translator".

### Persistent Objects, Transient Objects, and Values

Instances of an Oracle8 type are categorized into persistent objects and transient objects based on their lifetime. Instances of persistent objects can be further divided into standalone objects and embedded objects depending on whether or not they are referenceable by way of an object identifier.

  Note: The terms object and instance are used interchangeably in this manual.

  See Also: For more information about objects, refer to the Oracle8 Concepts manual.

#### Persistent Objects

A persistent object is an object which is stored in an Oracle8 database. It may be fetched into the object cache and modified by an OCI application. The lifetime of a persistent object can exceed that of the application which is accessing it. Once it is created, it remains in the database until it is explicitly deleted. There are two types of persistent objects:

- Standalone instances are stored in rows of a object table, and each one has a unique object identifier. An OCI application can retrieve a REF to a standalone instance, pin the object and navigate from the pinned object to other related objects.

  Standalone object may also be referred to as referenceable objects.

  It is also possible to SELECT a referenceable object, in which case you fetch the object "by value" instead of fetching its REF.

- Embedded instances are not stored as rows in a object table. They are embedded within other structures. Examples of embedded objects are objects which are attributes of another object, or instances which exist in an object column of a database table. Embedded instances do not have object identifiers, and OCI applications cannot get REFs to embedded instances.

  Embedded objects may also be referred to as non-referenceable objects or value instances. You may sometimes see them referred to as values, which is not to be confused with scalar data values. The context should make the meaning clear.

The following SQL examples demonstrate the difference between these two types of persistent objects.

##### Example 1, Standalone Objects

    CREATE TYPE person_t AS OBJECT
       (name      varchar2(30),
        age       number(3));
    CREATE TABLE person_tab OF person_t;

Objects which are stored in the object table `person_tab` are standalone instances. They have object identifiers and are referenceable. They can be pinned in an OCI application.

##### Example 2, Embedded Objects

    CREATE TABLE department
       (deptno     number,
        deptname   varchar2(30),
        manager    person_t);

Objects which are stored in the `manager` column of the `department` table are embedded objects. They do not have object identifiers, and they are not referenceable. This means they cannot be pinned in an OCI application, and they also never need to be unpinned. They are always retrieved into the object cache "by value".

#### Transient Objects

A transient object is an instance of an object type. It may have an object identifier, and it has a lifetime which is determined by the application when the instance is created. The application can also delete a transient object at any time.

Transient objects are often created by the application using the OCIObjectNew() function to store temporary values for computation.

Transient objects cannot be converted to persistent objects. Their role is fixed at the time they are instantiated.

  See Also: See the section "Creating, Freeing, and Copying Objects" on page 8-31 for more information about using OCIObjectNew().

#### Values

In the context of this manual, a value refers to either:

- a scalar value which is stored in a non-object column of a database table. An OCI application can fetch values from a database by issuing SQL statements.
- an embedded or non-referenceable object.

The context should make it clear which meaning is intended.

  Note: It is possible to SELECT a referenceable object into the object cache, rather than pinning it, in which case you fetch the object "by value" instead of fetching its REF.

## Developing an OCI Object Application

This section discusses the steps involved in developing a basic OCI object application. Each step mentioned in the section "Basic Object Program Structure" on page 8-4 is described here in more detail.

The following figure shows a simple program logic flow for how an application might work with objects. For simplicity, some required steps are omitted. Each step in this diagram is discussed in the following sections.

#### Figure 8-1 Basic Object Operational Flow

### Representing Objects in C Applications

Before an OCI application can work with object types, those types must exist in the database. Typically, you create types with SQL DDL statements (e.g., CREATE TYPE).

When the Oracle8 server processes the type definition DDL commands, it stores the type definitions in the data dictionary as type descriptor objects (TDOs).

When your application retrieves instances of object types from the database, it needs to have a client-side representation of the objects. In a C program, the representation of an object type is a `struct`. In an OCI object application, you may also include a null indicator structure corresponding to each object type structure.

Oracle8 provides a utility called the Object Type Translator (OTT), which generates C struct representations of database object types for you. For example, if you have a type in your database declared as

    CREATE TYPE emp_t AS OBJECT
    ( name       VARCHAR2(30),
      empno      NUMBER,
      deptno     NUMBER,
      hiredate   DATE,
      salary     NUMBER);

the OTT produces the following C struct and corresponding null indicator struct:

    struct emp_t
    {
      OCIString    * name;
      OCINumber    empno;
      OCINumber    deptno;
      OCIDate      hiredate;
      OCINumber    salary;
    };
    typedef struct emp_t emp_t

    struct emp_t_ind
    {
      OCIInd     _atomic;
      OCIInd     name;
      OCIInd     empno;
      OCIInd     deptno;
      OCIInd     hiredate;
      OCIInd     salary;
    };
    typedef struct emp_t_ind emp_t_ind;

The variable types used in the struct declarations are special types employed by the OCI object calls. A subset of OCI functions manipulate data of these types. These functions are mentioned later in this chapter, and are discussed in more detail in Chapter 9, "Object-Relational Datatypes".

These struct declarations are automatically written to a .h file whose name is determined by the OTT input parameters. You can include this header file in the code files for an application to provide access to objects.

  See Also: For more information about the OTT, see Chapter 12, "Using the Object Type Translator".

  For more information on the use of the NULL indicator struct, see the section "Nullness" on page 8-28.

### Initializing Environment and Object Cache

If your OCI application will be accessing and manipulating objects, it is essential that you specify a value of OCI_OBJECT for the mode parameter of the OCIInitialize() call, which is the first OCI call in any OCI application. Specifying this value for mode indicates to the OCI libraries that your application will be working with objects. This notification has the following important effects:

- it establishes the object run-time environment
- it sets up the object cache

If the mode parameter of OCIInitialize() is not set to OCI_OBJECT, any attempt to use an object-related function will result in an error.

The client-side object cache is allocated in the program's process space. This cache is the memory for objects that have been retrieved from the server and are available to your application.

  Note: If you initialize the OCI environment in object mode, your application allocates memory for the object cache, whether or not the application actually uses object calls.

  See Also: The object cache is mentioned throughout this chapter. For a detailed explanation of the object cache, see Chapter 11, "Object Cache and Object Navigation".

### Making Database Connections

Once the OCI environment has been properly initialized, the application can connect to a server. This is accomplished through the standard OCI connect calls described in "OCI Programming Steps" on page 2-16. When using these calls, no additional considerations need to be made because this application will be accessing objects.

There is only one object cache allocated per OCI environment. All objects retrieved or created via different connections within the environment use the same physical object cache.

### Retrieving an Object Reference from the Server

In order to work with objects, your application must first retrieve one or more objects from the server. You accomplish this by issuing a SQL statement that returns REFs to one or more objects.

  Note: It is also possible for a SQL statement to fetch embedded objects, rather than REFs, from a database. See the section "Fetching Embedded Objects" on page 8-15 for more information.

In the following example, the application declares a text block that stores a SQL statement designed to retrieve a REF to a single employee object from a object table of employees (`emp_tab`) in the database, given a particular employee number which is passed as an input variable (`:emp_num`) at run time:

    text *selemp = (text *) "SELECT REF(e)
                              FROM emp_tab e
                              WHERE empno = :emp_num";

Your application should prepare and process this statement in the same way that it would handle any relational SQL statement, as described in Chapter 2:

- Prepare an application request, using OCIStmtPrepare().
- Bind the host input variable using the appropriate bind call(s).
- Declare and prepare an output variable to receive the employee object reference. Here you would use an employee object reference, like the one declared in "Representing Objects in C Applications" on page 8-8:

      OCIRef  *emp1_ref = (OCIRef *) 0;
                               /* reference to an employee object */

  When defining the output variable, set the dty datatype parameter for the define call to SQLT_REF, the datatype constant for REF.

- Execute the statement with OCIStmtExecute().
- Fetch the resulting REF into `emp1_ref`, using OCIStmtFetch().

At this point, you could use the object reference to access and manipulate an object or objects from the database.

  See Also: For general information about preparing and executing SQL statements, see the section "OCI Programming Steps" on page 2-16. For specific information about binding and defining REF variables, refer to the sections "Advanced Bind Operations" on page 5-9 and "Advanced Define Operations" on page 5-16.

  For a code example showing REF retrieval and pinning, see "Example 7, REF Pinning and Navigation" on page D-118.

### Pinning an Object

Upon completion of the fetch step, your application has a REF, or pointer, to an object. The actual object is not currently available to work with. Before you can manipulate an object, it must be pinned. Pinning an object loads the object instance into the object cache, and enables you to access and modify the instance's attributes and follow references from that object to other objects, if necessary. Your application also controls when modified objects are written back to the server.

  Note: This section deals with a simple pin operation involving a single object at a time. For information about retrieving multiple objects through complex object retrieval, see the section "Complex Object Retrieval" on page 8-21.

An application pins an object by calling the function OCIObjectPin(). The parameters for this function allow you to specify the pin option, pin duration, and lock option for the object.

The following sample code illustrates a pin operation for the employee reference we retrieved in the previous section:

    if (OCIObjectPin(env, err, &emp1_ref, (OCIComplexObject *) 0,
         OCI_PIN_ANY,
         OCI_DURATION_TRANS,
         OCI_LOCK_X,  &emp1) != OCI_SUCCESS)
         process_error(err);

In this example, process_error() represents an error-handling function. If the call to OCIObjectPin() returns anything but OCI_SUCCESS, the error-handling function is called. The parameters of the OCIObjectPin() function are as follows:

- `env` is the OCI environment handle.
- `err` is the OCI error handle.
- `emp1_ref` is the reference that was retrieved through SQL.
- `(OCIComplexObject *) 0` indicates that this pin operation is not utilizing complex object retrieval.
- `OCI_PIN_ANY` is the pin option. See "Pinning an Object Copy" on page 11-6 for more information.
- `OCI_DURATION_TRANS` is the pin duration. See "Object Duration" on page 11-13 for more information.
- `OCI_LOCK_X` is the lock option. See "Locking Objects For Update" on page 11-12 for more information.
- `emp1` is an out parameter, which returns a pointer to the pinned object.

Now that the object has been pinned, the OCI application can modify that object. In this simple example, the object contains no references to other objects. For an example of navigation from one instance to another, see the section "Simple Object Navigation" on page 11-16.

#### Array Pin

Given an array of references, an OCI application can pin an array of objects by calling OCIObjectArrayPin(). The references may point to objects of different types.

### Manipulating Object Attributes

Once an object has been pinned, an OCI application can modify its attributes. The OCI provides a set of function for working with datatypes of object type structs, known as the OCI datatype mapping and manipulation functions.

  Note: Changes made to objects pinned in the object cache affect only those object copies (instances), and not the original object in the database. In order for changes made by the application to reach the database, those changes must be flushed/committed to the server. See "Marking Objects and Flushing Changes" on page 8-14 for more information.

For example, assume that the employee object in the previous section was pinned so that the employee's salary could be increased. Assume also that at this company, yearly salary increases are prorated for employees who have been at the company for less than 180 days.

So for this example we will need to access the employee's hire date and check whether it is more or less than 180 days prior to the current date. Based on that calculation, the employee's salary is increased by either $5000 (for more than 180 days) or $3000 (for less than 180 days). The sample code on the following page demonstrates this process.

Note that the datatype mapping and manipulation functions work with a specific set of datatypes; you must convert other types, like int, to the appropriate OCI types before using them in calculations.

    /* assume that sysdate has been fetched into sys_date, a string. */
    /* emp1 and emp1_ref are the same as in previous sections. */
    /* err is the OCI error handle. */
    /* NOTE: error handling code is not included in this example. */

    sb4 num_days;        /* the number of days between today and hiredate */
    OCIDate curr_date;          /* holds the current date for calculations */
    int raise;   /* holds the employee's raise amount before calculations */
    OCINumber raise_num;       /* holds employee's raise for calculations */
    OCINumber new_sal;                 /* holds the employee's new salary */

    /* convert date string to an OCIDate */
    OCIDateFromText(err, (text *) sys_date, (ub4) strlen(sys_date), (text *)
              NULL, (ub1) 0, (text *) NULL, (ub4) 0, &curr_date);

      /* get number of days between hire date and today */
    OCIDateDaysBetween(err, &curr_date, &emp1->hiredate, &num_days);

    /* calculate raise based on number of days since hiredate */
    if num_days > 180
        raise = 5000
    else
        raise = 3000;

    /* convert raise value to an OCINumber */
    OCINumberFromInt(err, (dvoid *)&raise, (uword)sizeof(raise),
                     OCI_NUMBER_SIGNED, &raise_num);

    /* add raise amount to salary */
    OCINumberAdd(err, &raise_num, &emp1->salary, &new_sal);
    OCINumberAssign(err, &new_sal, &emp1->salary);

This example points out how values must be converted to OCI datatypes (e.g., OCIDate, OCINumber) before being passed as parameters to the OCI datatype mapping and manipulation functions.

  See Also: For more information about the OCI datatypes and the datatype mapping and manipulation functions, refer to Chapter 9, "Object-Relational Datatypes".

### Marking Objects and Flushing Changes

In the example in the previous section, an attribute of an object instance was changed. At this point, however, that change exists only in the client-side object cache. The application must take specific steps to insure that the change is written in the database.

The first step is to indicate that the object has been modified. This is done with the OCIObjectMarkUpdate() function. This function marks the object as dirty (modified).

Objects that have had their dirty flag set must be flushed to the server for the changes to be recorded in the database. You can do this in three ways:

- Flush a single dirty object by calling OCIObjectFlush().
- Flush the entire cache using OCICacheFlush(). In this case the OCI traverses the dirty list maintained by the cache and flushes the dirty objects to the server.
- Call OCITransCommit() to commit a transaction. Doing so also traverses the dirty list and flushes objects to the server.

The flush operations work only on persistent objects in the cache. Transient objects are never flushed to the server.

Flushing an object to the server can activate triggers in the database. In fact, on some occasions an application may want to explicitly flush objects just to fire triggers on the server side.

  See Also: For more information about OCITransCommit() see the section "Transactions" on page 7-3.

  For information about transient and persistent objects, see the section "Creating, Freeing, and Copying Objects" on page 8-31.

  For information about seeing and checking object meta-attributes (like "dirty"), see the section "Object Meta-Attributes" on page 8-17.

### Fetching Embedded Objects

If your application needs to fetch an embedded object instance-an object stored in a column of a regular table, rather than an object table-you cannot use the REF retrieval mechanism described in the section "Retrieving an Object Reference from the Server" on page 8-11. Embedded instances do not have object identifiers, so it is not possible to get a REF to them. This means that they cannot serve as the basis for object navigation. There are still many situations, however, in which an application will want to fetch embedded instances.

For example, assume that an `address` type has been created.

    CREATE TYPE address AS OBJECT
    ( street1             varchar2(50),
      street2             varchar2(50),
      city                varchar2(30),
      state               char(2),
      zip                 number(5))

You could then use that type as the datatype of a column in another table:

    CREATE TABLE clients
    ( name          varchar2(40),
      addr          address)

Your OCI application could then issue the following SQL statement:

    SELECT addr FROM clients
    WHERE name='BEAR BYTE DATA MANAGEMENT'

This statement would return an embedded `address` object from the `clients` table. The application could then use the values in the attributes of this object for other processing.

Your application should prepare and process this statement in the same way that it would handle any relational SQL statement, as described in Chapter 2:

- Prepare an application request, using OCIStmtPrepare().
- Bind the input variable using the appropriate bind call(s).
- Define an output variable to receive the `address` instance. You use a C struct representation of the object type that was generated by the OTT, as described in the section "Representing Objects in C Applications" on page 8-8:

      addr1      *address; /* variable of the address struct type */

  When defining the output variable, set the dty datatype parameter for the define call to SQLT_NTY, the datatype constant for named data types.

- Execute the statement with OCIStmtExecute()
- Fetch the resulting instance into `addr1`, using OCIStmtFetch().

Following this, you can access the attributes of the instance, as described in the section "Manipulating Object Attributes" on page 8-13, or pass the instance as an input parameter for another SQL statement.

  Note: Changes made to an embedded instance can be made persistent only by executing a SQL UPDATE statement.

  See Also: For more information about preparing and executing SQL statements, see the section "OCI Programming Steps" on page 2-16.

### Object Meta-Attributes

An object's meta-attributes serve as flags which can provide information to an application, or to the object cache, about the status of an object. For example, one of the meta-attributes of an object indicates whether or not it has been flushed to the server. These can help an application control the behavior of instances.

Persistent and transient object instances have different sets of meta-attributes. The meta-attributes for persistent objects are further broken down into persistent meta-attributes and transient meta-attributes. Transient meta-attributes exist only when an instance is in memory. Persistent meta-attributes also apply to objects stored in the server.

#### Persistent Object Meta-Attributes

The following table shows the meta-attributes for standalone persistent objects.

Persistent Meta-Attributes | Meaning
--- | ---
existent | does the object exist?
nullness | null information of the instance
locked | has the object been locked?
dirty | has the object been marked as "dirtied"?
Transient Meta-Attributes |
pinned | is the object pinned?
allocation duration | see "Object Duration" on page 11-13
pin duration | see "Object Duration" on page 11-13

  Note: Embedded persistent objects only have the nullness and allocation duration attributes, which are transient.

The OCI provides the OCIObjectGetProperty() function, which allows an application to check the status of a variety of attributes of an object.

The syntax of the function is:

    sword OCIObjectGetProperty ( OCIEnv              *envh,
                                 OCIError            *errh,
                                 CONST dvoid         *obj,
                                 OCIObjectPropId     propertyId,
                                 dvoid               *property,
                                 ub4                 *size );

The propertyId and property parameters are used to retrieve information about any of a variety of properties or attributes

The different property ids and the corresponding type of property argument are given below. For more information, see OCIObjectGetProperty() on page 14-30.

##### OCI_OBJECTPROP_LIFETIME

This identifies whether the given object is a persistent object or a transient object or a value instance. The property argument must be a pointer to a variable of type OCIObjectLifetime. Possible values include:

- OCI_OBJECT_PERSISTENT
- OCI_OBJECT_TRANSIENT
- OCI_OBJECT_VALUE

##### OCI_OBJECTPROP_SCHEMA

This returns the schema name of the table in which the object exists. An error is returned if the given object points to a transient instance or a value. If the input buffer is not big enough to hold the schema name an error is returned, the error message will communicate the required size. Upon success, the size of the returned schema name in bytes is returned via size. The property argument must be an array of type text and size should be set to size of array in bytes by the caller.

##### OCI_OBJECTPROP_TABLE

This returns the table name in which the object exists. An error is returned if the given object points to a transient instance or a value. If the input buffer is not big enough to hold the table name an error is returned, the error message will communicate the required size. Upon success, the size of the returned table name in bytes is returned via size. The property argument must be an array of type text and size should be set to size of array in bytes by the caller.

##### OCI_OBJECTPROP_PIN_DURATION

This returns the pin duration of the object. An error is returned if the given object points to a value instance. The property argument must be a pointer to a variable of type OCIDuration. Valid values include:

- OCI_DURATION_SESSION
- OCI_DURATION_TRANS

For more information about durations, see "Object Duration" on page 11-13.

##### OCI_OBJECTPROP_ALLOC_DURATION

This returns the allocation duration of the object. The property argument must be a pointer to a variable of type OCIDuration. Valid values include:

- OCI_DURATION_SESSION
- OCI_DURATION_TRANS

For more information about durations, see "Object Duration" on page 11-13.

##### OCI_OBJECTPROP_LOCK

This returns the lock status of the object. The possible lock status is enumerated by OCILockOpt. An error is returned if the given object points to a transient or value instance. The property argument must be a pointer to a variable of type OCILockOpt. Note, the lock status of an object can also be retrieved by calling OCIObjectIsLocked().

##### OCI_OBJECTPROP_MARKSTATUS

This returns the dirty status and indicates whether the object is a new object, updated object or deleted object. An error is returned if the given object points to a transient or value instance. The property argument must be of type OCIObjectMarkStatus. Valid values include:

- OCI_OBJECT_NEW
- OCI_OBJECT_DELETED
- OCI_OBJECT_UPDATED

The following macros are available to test the object mark status:

- OCI_OBJECT_IS_UPDATED(flag)
- OCI_OBJECT_IS_DELETED(flag)
- OCI_OBJECT_IS_NEW(flag)
- OCI_OBJECT_IS_DIRTY(flag)

##### OCI_OBJECTPROP_VIEW

This identifies whether the specified object is a view object or not. If the property value returned is TRUE, it indicates the object is a view otherwise it is not. An error is returned if the given object points to a transient or value instance. The property argument must be of type boolean.

#### Additional Attribute Functions

The OCI also provides routines which allow an application to set or check some of these attributes directly or indirectly, as shown in the following table:

Meta-Attribute | Set With | Check With
--- | --- | ---
nullness | <none> | OCIObjectGetInd()
existence | <none> | OCIObjectExists()
locked | OCIObjectLock() | OCIObjectIsLocked()
dirty | OCIObjectMark() | OCIObjectIsDirty()

#### Transient Object Meta-Attributes

Transient objects have no persistent attributes, and the following transient attributes:

Transient Meta-Attributes | Meaning
existent | does the object exist?
pinned | is the object being accessed by the application?
dirty | has the object been marked as "dirtied"?
nullness | null information of the instance
allocation duration | see "Object Duration" on page 11-13
pin duration | see "Object Duration" on page 11-13

### Complex Object Retrieval

In the examples earlier in this chapter, only a single instance at a time was fetched or pinned. In these cases, each pin operation involved a separate server round trip to retrieve the object.

Object-oriented applications often model their problems as a set of interrelated objects that form graphs of objects. The applications process objects by starting at some initial set of objects, and then using the references in these initial objects to traverse the remaining objects. In a client-server setting, each of these traversals could result in costly network roundtrips to fetch objects.

Application performance when dealing with objects may be increased through the use of complex object retrieval (COR). This is a prefetching mechanism in which an application specifies a criteria for retrieving a set of linked objects in a single operation.

  Note: As described below, this does not mean that these prefetched objects are all pinned. They are fetched into the object cache, so that subsequent pin calls are local operations.

A complex object is a set of logically related objects consisting of a root object, and a set of objects each of which is prefetched based on a given depth level. The root object is explicitly fetched or pinned. The depth level is the shortest number of references that need to be traversed from the root object to a given prefetched object in a complex object.

An application specifies a complex object by describing its content and boundary. The fetching of complex objects is constrained by an environment's prefetch limit, the amount of memory in the object cache that is available for prefetching objects.

The use of COR does not add functionality; it only improves performance. Therefore, its use is optional.

As an example for this discussion, consider the following type declaration:

    CREATE TYPE customer(...);
    CREATE TYPE line_item(...);
    CREATE TYPE line_item_varray as VARRAY(100) of REF line_item;
    CREATE TYPE purchase_order AS OBJECT
    ( po_number         NUMBER,
      cust              REF customer,
      related_orders    REF purchase_order,
      line_items        line_item_varray)

The `purchase_order` type contains a scalar value for `po_number`, a VARRAY of line items, and two references. The first is to a `customer` type, and the second is to a `purchase_order` type, indicating that this type may be implemented as a linked list.

When fetching a complex object, an application must specify the following:

1. a REF to the desired root object.
2. one or more pairs of type and depth information to specify the boundaries of the complex object. The type information indicates which REF attributes should be followed for COR, and the depth level indicates how many levels deep those links should be followed.

In the case of the purchase order object above, the application must specify the following:

1. the REF to the root purchase order object
2. one or more pairs of type and depth information for `cust`, `related_orders`, or `line_items`

An application fetching a purchase order will very likely need access to the customer information for that order. Using simple navigation, this would require two server accesses to retrieve the two objects. Through complex object retrieval, the customer can be prefetched when the application pins the purchase order. In this case, the complex object would consist of the purchase order object and the customer object it references.

In the above example, the application would specify the `purchase_order` REF, and would indicate that the `cust` REF attribute should be followed to a depth level of 1:

1. REF(PO object)
2. {(customer, 1)}

If the application wanted to prefetch the `purchase_order` object and all objects in the object graph it contains, the application would specify that both the `cust` and `related_orders` should be followed to the maximum depth level possible.

1. REF(PO object)
2. {(customer, 1), (purchase_order, UB4MAXVAL)}

where `UB4MAXVAL` specifies that all objects of the specified type reachable through references from the root object should be prefetched.

If an application wanted to fetch a PO and all the associated line items, it would specify:

1. REF(PO object)
2. {(line_item, 1)}

The application can also choose to fetch all objects reachable from the root object by way of REFs (transitive closure) to a certain depth. To do so, set the level parameter to the depth desired. For the above two examples, the application could also specify `(PO object REF, UB4MAXVAL)` and `(PO object REF, 1)` respectively to prefetch required objects. Doing so results in many extraneous fetches but is quite simple to specify, and requires only one server round trip.

#### Prefetching Objects

After specifying and fetching a complex object, subsequent fetches of objects contained in the complex object do not incur the cost of a network round trip, because these objects have already been prefetched and are in the object cache. Keep in mind that excessive prefetching of objects can lead to a flooding of the object cache. This flooding, in turn, may force out other objects that the application had already pinned leading to a performance degradation instead of performance improvement.

  Note: If there is insufficient memory in the cache to hold all prefetched objects, some objects may not be prefetched. The application will then incur a network round-trip when those objects are accessed later.

The SELECT privilege is needed for all prefetched objects. Objects in the complex object for which the application does not have SELECT privilege will not be prefetched.

#### Implementing Complex Object Retrieval in the OCI

Complex Object Retrieval (COR) allows an application to prefetch a complex object while fetching the root object. The complex object specifications are passed to the same OCIObjectPin() function used for simple objects.

An application specifies the parameters for complex object retrieval using a complex object retrieval handle. This handle is of type OCIComplexObject and is allocated in the same way as other OCI handles.

The complex object retrieval handle contains a list of complex object retrieval descriptors. The descriptors are of type OCIComplexObjectComp, and are allocated in the same way as other OCI descriptors.

Each COR descriptor contains a type REF and a depth level. The type REF specifies a type of reference to be followed while constructing the complex object. The depth level indicates how far a particular type of reference should be followed. Specify an integer value, or the constant `UB4MAXVAL` for the maximum possible depth level.

The application can also specify the depth level in the COR handle without creating COR descriptors for type and depth parameters. In this case, all REFs are followed to the depth specified in the COR handle. The COR handle can also be used to specify whether a collection attribute should be fetched separately on demand (out-of-line) as opposed to the default case of fetching it along with the containing object (inline).

The application uses OCIAttrSet() to set the attributes of a COR handle. The attributes are:

  OCI_ATTR_COMPLEXOBJECT_LEVEL - the depth level

  OCI_ATTR_COMPLEXOBJECT_COLL_OUTOFLINE - fetch collection attribute in an object type out-of-line

The application allocates the COR descriptor using OCIDescriptorAlloc() and then can set the following attributes:

  OCI_ATTR_COMPLEXOBJECTCOMP_TYPE - the type REF

  OCI_ATTR_COMPLEXOBJECTCOMP_LEVEL - the depth level for references of the above type

Once these attributes are set, the application calls OCIParamSet() to put the descriptor into a complex object retrieval handle. The handle has an `OCI_ATTR_PARAM_COUNT` attribute which specifies the number of descriptors on the handle. This attribute can be read with OCIAttrGet().

Once the handle has been populated, it can be passed to the OCIObjectPin() call to pin the root object and prefetch the remainder of the complex object.

The complex object retrieval handles and descriptors must be freed explicitly when they are no longer needed.

  See Also: For more information about handles and descriptors, see "Handles" on page 2-6 and "Descriptors and Locators" on page 2-12.

### COR Prefetching

The application specifies a complex object while fetching the root object. The prefetched objects are obtained by doing a breadth-first traversal of the graph(s) of objects rooted at a given root object(s). The traversal stops when all required objects have been prefetched, or when the total size of all the prefetched objects exceeds the prefetch limit.

#### COR interface

The interface for fetching complex objects is the OCI pin interface. The application can pass an initialized COR handle to OCIObjectPin() (or an array of handles to OCIObjectArrayPin()) to fetch the root object and the prefetched objects specified in the COR handle.

    sword OCIObjectPin ( OCIEnv              *env,
                         OCIError            *err,
                         OCIRef              *object_ref,
                         OCIComplexObject    *corhdl,
                         OCIPinOpt           pin_option,
                         OCIDuration         pin_duration,
                         OCILockOpt          lock_option,
                         dvoid               **object );

    sword OCIObjectArrayPin ( OCIEnv            *env,
                              OCIError          *err,
                              OCIRef            **ref_array,
                              ub4               array_size,
                              OCIComplexObject  **cor_array,
                              ub4               cor_array_size,
                              OCIPinOpt         pin_option,
                              OCIDuration       pin_duration,
                              OCILockOpt        lock,
                              dvoid             **obj_array,
                              ub4               *pos );

Keep the following points in mind when using COR:

1. A null COR handle argument defaults to pinning just the root object.
2. A COR handle with type of the root object and a depth level of 0 fetches only the root object and is thus equivalent to a null COR handle.
3. The lock options apply only to the root object.

   Note: In order to specify lock options for prefetched objects, the application can visit all the objects in a complex object, create an array of REFs, and lock the entire complex object in another round trip using the array interface (OCIObjectArrayPin()).

#### Example of COR

The following example illustrates how an application program can be modified to use complex object retrieval.

Consider an application that displays a purchase order and the line items associated with it. The code in boldface accomplishes this. The rest of the code uses complex object retrieval for prefetching and thus enhances the application's performance.

    OCIEnv *envhp;
    OCIError *errhp;
    OCIRef *liref;
    OCIRef *poref;
    OCIIter *itr;
    boolean  eoc;
    purchase_order *po = (purchase_order *)0;
    line_item *li = (line_item *)0;
    OCISvcCtx *svchp;
    OCIComplexObject *corhp;
    OCIComplexObjectComp *cordp;
    OCIType *litdo;
    ub4 level = 0;

    /* get COR Handle */
    OCIHandleAlloc((dvoid *) envhp, (dvoid **) &corhp, (ub4)
                    OCI_HTYPE_COMPLEXOBJECT, 0, (dvoid **)0);

    /* get COR descriptor for type line_item */
    OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &cordp, (ub4)
                    OCI_DTYPE_COMPLEXOBJECTCOMP, 0, (dvoid **) 0);

    /* get type of line_item to set in COR descriptor */
    OCITypeByName(envhp, errhp, svchp, (const text *) 0, (ub4) 0,
                    const text *) "LINE_ITEM", (ub4) strlen((const char *)
                    "LINE_ITEM"), OCI_DURATION_SESSION, &litdo);

    /* set line_item type in COR descriptor */
    OCIAttrSet( (dvoid *) cordp, (ub4) OCI_DTYPE_COMPLEXOBJECTCOMP,
                    dvoid *) litdo, (ub4) sizeof(dvoid *), (ub4)
                   OCI_ATTR_COMPLEXOBJECTCOMP_TYPE, (OCIError *) errhp);
    level = 1;

    /* set depth level for line_item_varray in COR descriptor */
    OCIAttrSet( (dvoid *) cordp, (ub4) OCI_DTYPE_COMPLEXOBJECTCOMP,
                 (dvoid *) &level, (ub4) sizeof(ub4), (ub4)
                OCI_ATTR_COMPLEXOBJECTCOMP_TYPE_LEVEL, (OCIError *) errhp);

    /* put COR descriptor in COR handle */
    OCIParamSet(corhp, OCI_HTYPE_COMPLEXOBJECT, &errhp, cordp,
                    OCI_DTYPE_COMPLEXOBJECTCOMP, 1);

    /* pin the purchase order */
    OCIObjectPin(envhp, errhp, poref, corhp, OCI_PIN_LATEST,
                      OCI_REFRESH_LOADED, OCI_DURATION_SESSION,
                      OCI_LOCK_NONE, (ub2) 1, (dvoid **)&po)

    /* free COR descriptor and COR handle */
    OCIDescriptorFree((dvoid *) cordp, (ub4) OCI_DTYPE_COMPLEXOBJECTCOMP);
    OCIHandleFree((dvoid *) corhp, (ub4) OCI_HTYPE_COMPLEXOBJECT);

    /* iterate and print line items for this purchase order */
    OCIIterCreate(envhp, errhp, po.line_items, &itr);

    /* get first line item */
    OCIIterNext(envhp, errhp, itr, &liref, (dvoid **)0, &eoc);
    while (!eoc)        /* not end of collection */
    {
    /* pin line item */
     OCIObjectPin(envhp, errhp, liref, (dvoid *)0, OCI_PIN_RECENT,
                         OCI_REFRESH_LOADED, OCI_DURATION_SESSION,
                        OCI_LOCK_NONE, (ub2) 1, (dvoid **)&li);
      display_line_item(li);

    /* get next line item */
    OCIIterNext(envhp, errhp, itr, &liref, (dvoid **)0, &eoc);
    }

### Pin Count and Unpinning

Each object in the object cache has a pin count associated with it. The pin count essentially indicates the number of code modules that are concurrently accessing the object. The pin count is set to 1 when an object is pinned into the cache for the first time. Objects prefetched with complex object retrieval enter the object cache with a pin count of zero.

It is possible to pin an already-pinned object. Doing so increases the pin count by one. When a process finishes using an object, it should unpin it, using OCIObjectUnpin(). This call decrements the pin count by one.

When the pin count of an object reaches zero, that object is eligible to be aged out of the cache if necessary, freeing up the memory space occupied by the object.

The pin count of an object can be set to zero explicitly by calling OCIObjectPinCountReset().

An application can unpin all objects in the cache related to a specific connection, by calling OCICacheUnpin().

  See Also: See the section "Freeing an Object Copy" on page 11-8 for more information about the conditions under which objects with zero pin count are removed from the cache.

  For information about explicitly flushing an object or the entire cache, see the section "Marking Objects and Flushing Changes" on page 8-14.

  See the section "Freeing an Object Copy" on page 11-8 for more information about objects being aged out of the cache.

### Nullness

If a column in a row of a database table has no value, then that column is said to be NULL, or to contain a NULL. Two different types of nulls can apply to objects:

- Any attribute of an object can have a null value. This indicates that the value of that attribute of the object is not known.
- An object instance may be atomically null. This means that the value of the entire object is unknown.

Atomic nullness is not the same thing as nonexistence. An atomically null instance still exists, its value is just not known. It may be thought of as an existing object with no data.

When working with objects in the OCI, an application can define a null indicator structure for each object type used by the application. In most cases, doing so simply requires including the null indicator structure generated by the OTT along with the struct declaration. When the OTT output header file is included, the null indicator struct becomes available to your application.

For each type, the null indicator structure includes an atomic null indicator (whose type is OCIInd), and a null indicator for each attribute of the instance. If the type has an object attribute, the null indicator structure includes that attribute's null indicator structure. The following example shows the C representations of types with their corresponding null indicator structures.

    struct address
    {
       OCINumber    no;
       OCIString    *street;
       OCIString    *state;
       OCIString    *zip;
    };
    typedef struct address address;

    struct address_ind
    {
      OCIInd    _atomic;
      OCIInd    no;
      OCIInd    street;
      OCIInd    state;
      OCIInd    zip;
    };
    typedef struct address_ind address_ind;

    struct person
    {
       OCIString      *fname;
       OCIString      *lname;
       OCINumber      age;
       OCIDate        birthday;
       OCIArray       *dependentsAge;
       OCITable       *prevAddr;
       OCIRaw         *comment1;
       OCILobLocator  *comment2;
       address        addr;
       OCIRef         *spouse;
    };
    typedef struct person person;

    struct person_ind
    {
      OCIInd        _atomic;
      OCIInd        fname;
      OCIInd        lname;
      OCIInd        age;
      OCIInd        birthday;
      OCIInd        dependentsAge;
      OCIInd        prevAddr;
      OCIInd        comment1;
      OCIInd        comment2;
      address_ind   addr;
      OCIInd        spouse;
    };
    typedef struct person_ind person_ind;

    Note: The dependentsAge field of person_ind indicates whether the entire varray (dependentsAge field of person) is atomically null or not. Null information of individual elements of dependentsAge can be retrieved through the elemind parameter of a call to OCICollGetElem(). Similarly, the prevAddr field of person_ind indicates whether the entire nested table (prevAddr field of person) is atomically null or not. Null information of individual elements of prevAddr can be retrieved through the elemind parameter of a call to OCICollGetElem().

For an object type instance, the first field of the null-indicator structure is the atomic null indicator, and the remaining fields are the attribute null indicators whose layout resembles the layout of the object type instance's attributes.

Checking the value of the atomic null indicator allows an application to test whether an instance is atomically NULL. Checking any of the others allows an application to test the NULL status of that attribute, as in the following code sample:

    person_ind *my_person_ind
    if ( my_person_ind -> _atomic = OCI_IND_NULL)
    {
        /* instance is atomically null */
    }
    if ( my_person_ind -> fname = OCI_IND_NULL)
    {
        /* fname attribute is NULL */
    }

In the above example, the value of the atomic null indicator, or one of the attribute null indicators, is compared to the predefined value `OCI_IND_NULL` to test its nullness. The following predefined values are available for such a comparison:

- OCI_IND_NOTNULL, indicating that the value is not NULL
- OCI_IND_NULL, indicating that the value is NULL
- OCI_IND_BADNULL, indicates that an enclosing object (or parent object) is NULL. This is used by PL/SQL, and may also be referred to as an INVALID_NULL. For example if a type instance is NULL, then its attributes are INVALID_NULLs.

Use the OCIObjectGetInd() function to allocate storage for and retrieve the null indicator structure of an object.

  See Also: For more information about OTT-generated null indicator structs, refer to Chapter 12.

### Creating, Freeing, and Copying Objects

An OCI application can create any object using OCIObjectNew(). To create a persistent object, the application must specify the object table where the new object will reside. This value can be retrieved by calling OCIObjectPinTable(), and it is passed in the table parameter. To create a transient object, the application needs to pass only the type descriptor object (retrieved by calling OCITypeByName()) for the type of object being created.

OCIObjectNew() can also be used to create instances of scalars (e.g., REF, LOB, string, raw, number, and date) and collections (e.g., varray and nested table) by passing the appropriate value for the typecode parameter.

Use OCIObjectFree() to free memory allocated through OCIObjectNew(). Freeing an object deallocates all the memory allocated for the object, including the associated null indicator structure. This procedure deletes an object before its lifetime expires. An application can also use OCIObjectMarkDelete() to delete a persistent object.

An application can copy one instance to another instance of the same type using OCIObjectCopy().

  See Also: See the descriptions of these functions in Chapter 14 for more information.

### Object Reference and Type Reference

The object extensions to the OCI provide the application with the flexibility to access the contents of objects using their pointers or their references. The OCI provides the function OCIObjectGetObjectRef() to return a reference to an object given the object's pointer.

For applications that also want to access the type information of objects, the OCI provides the function OCIObjectGetProperty() to return a reference to an object's type descriptor object (TDO), given a pointer to the object.

### Error Handling in Object Applications

Error handling in OCI applications is the same, whether or not the application uses objects. For more information about function return codes and error messages, see the section "Error Handling" on page 2-25.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index