11 OCI Object-Relational Programming

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 "Overview of OCI Program Programming". That paradigm is reproduced here, with extra information covering basic object functionality.

1. Initialize the OCI programming environment. You must initialize the environment in object mode.

   Your application must 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 15.

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. You can also select a nonreferenceable object. "Fetching Embedded Objects" describes fetching the entire object.

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 can do some or all of the following:

   • Manipulate the attributes of the object and mark it as dirty (modified)

   • 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.

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

Persistent Objects, Transient Objects, and Values

Instances of an Oracle 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 they are referenceable by way of an object identifier.

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

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

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, such as CREATE TYPE.

When the Oracle database 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 must 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.

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

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

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 12.

These struct declarations are automatically written to a header 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.

Initializing the Environment and the Object Cache

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

• It establishes the object runtime environment.

• It sets up the object cache.

Memory for the object cache is allocated on demand when objects are loaded into the cache.

If the mode parameter of OCIEnvCreate() or OCIEnvNlsCreate() is not set to OCI_OBJECT, any attempt to use an object-related function results 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.

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". When you use these calls, no additional considerations must be made because this application is accessing objects.

Only one object cache is allocated for each OCI environment. All objects retrieved or created through different connections within the environment use the same physical object cache. Each connection has its own logical object cache.

Retrieving an Object Reference from the Server

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.

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 an object table of employees (emp_tab) in the database, when given a particular employee number that is passed as an input variable (:emp_num) at runtime:

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

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

1. Prepare an application request, using OCIStmtPrepare().

2. Bind the host input variable using one or more appropriate bind calls.

3. 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":

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

   When you define the output variable, set the dty data type parameter for the define call to SQLT_REF, the data type constant for REF.

4. Execute the statement with OCIStmtExecute().

5. Fetch the resulting REF into emp1_ref, using OCIStmtFetch2().

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

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.

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.

Example 11-3 shows sample code that illustrates a pin operation for the employee reference your application retrieved in the previous section, "Retrieving an Object Reference from the Server".

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" for more information.

• OCI_DURATION_TRANS is the pin duration. See "Object Duration" for more information.

• OCI_LOCK_X is the lock option. See "Locking Objects for Update" for more information.

• emp1 is an out parameter that 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.

Manipulating Object Attributes

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

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.

For this example, you must access the employee's hire date and check whether it is more or less than 180 days before 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 in Example 11-4 demonstrates this process.

Note that the data type mapping and manipulation functions work with a specific set of data types; 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, (void  *)&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);

Example 11-4 points out how values must be converted to OCI data types (for example, OCIDate, OCINumber) before being passed as parameters to the OCI data type mapping and manipulation functions.

Marking Objects and Flushing Changes

In Example 11-4, 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 ensure 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 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 the dirty 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.

Fetching Embedded Objects

If your application must 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 "Retrieving an Object Reference from the Server". Embedded instances do not have object identifiers, so it is not possible to get a REF to them; they cannot serve as the basis for object navigation. Many situations exist, however, in which an application must 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 data type 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 OCIStmtPrepare2().

• Bind the input variable using one or more appropriate bind calls.

• Define an output variable to receive the address instance. You use a C struct representation of the object type that was generated by OTT, as described in "Representing Objects in C Applications".

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

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

• Execute the statement with OCIStmtExecute().

• Fetch the resulting instance into addr1, using OCIStmtFetch2().

Following this operation, you can access the attributes of the instance, as described in "Manipulating Object Attributes", or pass the instance as an input parameter for another SQL statement.

Object Meta-Attributes

An object's meta-attributes serve as flags that 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 it has been flushed to the server. Object meta-attributes 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 subdivided 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.

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

Complex Object Retrieval

In Example 11-3 and Example 11-4, 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 these 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 round-trips to fetch objects.

Application performance with objects can be improved with complex object retrieval (COR). This is a prefetching mechanism in which an application specifies the criteria for retrieving a set of linked objects in a single operation.

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 must 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.

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:

• A REF to the desired root object.

• 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 preceding purchase order object, the application must specify the following:

• The REF to the root purchase order object

• One or more pairs of type and depth information for cust, related_orders, or line_items

An application fetching a purchase order may 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 that it references.

In the previous 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, as follows:

• REF(PO object)

• {(customer, 1)}

For the application 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.

• REF(PO object)

• {(customer, UB4MAXVAL), (purchase_order, UB4MAXVAL)}

(In this example, UB4MAXVAL specifies that all objects of the specified type reachable through references from the root object should be prefetched.)

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

• REF(PO object)

• {(line_item, 1)}

The application can also fetch all objects reachable from the root object by way of REFs (transitive closure) by setting the level parameter to the depth desired. For the preceding two examples, the application could also specify (PO object REF, UB4MAXVAL) and (PO object REF, 1) respectively, to prefetch required objects. Although, doing so results in many extraneous fetches, quite simple to specify and requires only one server round-trip.

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 graphs of objects rooted at a given root object. The traversal stops when all required objects have been prefetched, or when the total size of all the prefetched objects exceeds the prefetch limit.

sword OCIObjectPin ( OCIEnv              *env, 
                     OCIError            *err, 
                     OCIRef              *object_ref,
                     OCIComplexObject    *corhdl,
                     OCIPinOpt           pin_option, 
                     OCIDuration         pin_duration, 
                     OCILockOpt          lock_option,
                     void                **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, 
                          void              **obj_array,
                          ub4               *pos );

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((void  *) envhp, (void  **) &corhp, (ub4)
                OCI_HTYPE_COMPLEXOBJECT, 0, (void  **)0);

/* get COR descriptor for type line_item */
OCIDescriptorAlloc((void  *) envhp, (void  **) &cordp, (ub4)
                OCI_DTYPE_COMPLEXOBJECTCOMP, 0, (void  **) 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"), (text *) 0,
              (ub4) 0, OCI_DURATION_SESSION,
              OCI_TYPEGET_HEADER, &litdo);

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

/* set depth level for line_item_varray in COR descriptor */
OCIAttrSet( (void  *) cordp, (ub4) OCI_DTYPE_COMPLEXOBJECTCOMP,
            (void  *) &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_DURATION_SESSION, OCI_LOCK_NONE, (void  **)&po);

/* free COR descriptor and COR handle */
OCIDescriptorFree((void  *) cordp, (ub4) OCI_DTYPE_COMPLEXOBJECTCOMP);
OCIHandleFree((void  *) 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, (void  **)&liref, (void  **)0, &eoc);
while (!eoc)        /* not end of collection */
{
/* pin line item */
   OCIObjectPin(envhp, errhp, *liref, (void  *)0, OCI_PIN_RECENT,
       OCI_DURATION_SESSION,
       OCI_LOCK_NONE, (void  **)&li));
  display_line_item(li);

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

OCI Versus SQL Access to Objects

If an application must manipulate a graph of objects (interrelated by object references), then it is more effective to use the OCI interface rather than the SQL interface for accessing objects. Retrieving a graph of objects using the SQL interface may require executing multiple SELECT statements, requiring multiple network round-trips. Using the complex object retrieval capability provided by OCI, the application can retrieve the graph of objects in one OCIObjectPin() call.

Consider the update case where the application retrieves a graph of objects, and modifies it based upon user interaction, and then wants to make the modifications persistent in the database. Using the SQL interface, the application would have to execute multiple UPDATE statements to update the graph of objects. If the modifications involved creation of new objects and deletion of existing objects, then execution of corresponding INSERT and DELETE statements would also be required. In addition, the application would have to do more bookkeeping, such as keeping track of table names, because this information is required for executing the INSERT, UPDATE, and DELETE statements.

Using the OCICacheFlush() function, the application can flush all modifications (insertion, deletion, and update of objects) in a single operation. OCI does all the bookkeeping, thereby requiring less coding in the application. For manipulating a graph of objects OCI is not only efficient, but also provides an easy-to-use interface.

Consider a different case in which the application must fetch an object when given its REF. In OCI, this is achieved by pinning the object using the OCIObjectPin() call. In the SQL interface, this can be achieved by dereferencing the REF in a SELECT statement (for example, SELECT DEREF(ref) from tbl;). Consider situations where the same REF (reference to the same object) is being dereferenced multiple times in a transaction. By calling OCIObjectPin() with the OCI_PIN_RECENT option, the object is fetched from the server only once for the transaction, and repeated pins on the same REF return a pointer to the pinned object in the cache. In the SQL interface, each execution of the SELECT DEREF... statement would result in fetching the object from the server. This would result in multiple round-trips to the server and multiple copies of the same object.

Finally, consider the case in which the application must fetch a nonreferenceable object, as in the following example:

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

The employee_t instances stored in the manager column are nonreferenceable. You can only use the SQL interface to fetch manager column instances. But if employee_t has any REF attributes, OCI calls can then be used to navigate the REF.

Pin Count and Unpinning

Each object in the object cache has a pin count associated with it. The pin count 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 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().

NULL Indicator Structure

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, meaning that the value of the entire object is unknown.

Atomic nullity 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 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 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. Example 11-6 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;

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)
   printf ("instance is atomically NULL\n");
else
if( my_person_ind -> fname == OCI_IND_NULL)
   printf ("fname attribute is NULL\n");

In the preceding 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 if it is NULL. 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 function "OCIObjectGetInd()" to retrieve the NULL indicator structure of an object.

If you update an attribute in its C structure, you must also set the NULL indicator for that attribute:

obj->attr1 = string1;
OCIObjectGetInd(envhp, errhp, obj, &ind);
ind->attr1 = OCI_IND_NOTNULL;

Creating 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 resides. This value can be retrieved by calling OCIObjectPinTable(), and it is passed in the table parameter. To create a transient object, the application must pass only the type descriptor object (retrieved by calling OCIDescribeAny()) for the type of object being created.

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

REF

DATE

ANSI DATE

TIMESTAMP

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE

INTERVAL YEAR TO MONTH

INTERVAL DAY TO SECOND

FLOAT

NUMBER

DECIMAL

RAW

VARCHAR2, NVARCHAR2

CHAR, NCHAR

VARCHAR

VARRAY

NESTED TABLE

CLOB, NCLOB

BLOB

BFILE

Freeing and Copying Objects

Use OCIObjectFree() to free memory allocated by OCIObjectNew(). An object instance can have attributes that are pointers to additional memory (secondary memory chunks).

Freeing an object deallocates all the memory allocated for the object, including the associated NULL indicator structure and any secondary memory chunks. You must neither explicitly free the secondary memory chunks nor reassign the pointers. Doing so can result in memory leaks and memory corruption. This procedure deletes a transient, but not a persistent, object before its lifetime expires. An application should use OCIObjectMarkDelete() to delete a persistent object.

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

Object Reference and Type Reference

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

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

When a persistent object based on an object table with system-generated object identifiers (OIDs) is created, a reference to this object may be immediately obtained by using OCIObjectGetObjectRef(). But when a persistent object is based on an object view or on an object table with primary-key-based OIDs, all attributes belonging to the primary key must first be set before a reference can be obtained.

Create Objects Based on Object Views and Object Tables with Primary-Key-Based OIDs

Applications can use the OCIObjectNew() call to create objects, which are based on object views, or on object tables with primary-key-based object identifiers (OIDs). Because object identifiers of such views and tables are based on attribute values, applications must then use OCIObjectSetAttr() to set all attributes belonging to the primary key. Once the attribute values have been set, applications can obtain an object reference based on the attribute value by calling OCIObjectGetObjectRef().

This process involves the following steps:

1. Pin the object view or object table on which the new object is to be based.

2. Create a new object using OCIObjectNew(), passing in the handle to the table or view obtained by the pin operation in Step 1.

3. Use OCIObjectSetAttr() to fill in the necessary values for the object attributes. These must include those attributes that make up the user-defined object identifier for the object table or object view.

4. Use OCIObjectNew() to allocate an object reference, passing in the handle to the table or view obtained by the pin operation in Step 1.

5. Use OCIObjectGetObjectRef() to obtain the primary-key-based reference to the object, if necessary. If desired, return to Step 2 to create more objects.

6. Flush the newly created objects to the server.

Example 11-7 shows how this process might be implemented to create a new object for the emp_view object view in the HR schema.

void object_view_new ()
{
void     *table;
OCIRef   *pkref;
void     *object;
OCIType  *emptdo;
...
/* Set up the service context, error handle and so on.. */
...
/* Pin the object view */
OCIObjectPinTable(envp,errorp,svctx, "HR", strlen("HR"), "EMP_VIEW",
    strlen("EMP_VIEW"),(void  *) 0, OCI_DURATION_SESSION, (void  **) &table);

/* Create a new object instance  */
OCIObjectNew(envp, errorp, svctx, OCI_TYPECODE_OBJECT,(OCIType *)emptdo, table,
OCI_DURATION_SESSION,FALSE,&object);

/* Populate the attributes of "object" */
OCIObjectSetAttr(...);
...
/* Allocate an object reference */
OCIObjectNew(envp, errorp, svctx, OCI_TYPECODE_REF, (OCIType *)0, (void  *)0,
    OCI_DURATION_SESSION,TRUE,&pkref);

/* Get the reference using OCIObjectGetObjectRef */
OCIObjectGetObjectRef(envp,errorp,object,pkref);
...
/* Flush new objects to server */
...
} /* end function */

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 "Error Handling in OCI".

Substitutability

The benefits of polymorphism derive partially from the property substitutability. Substitutability allows a value of some subtype to be used by code originally written for the supertype, without any specific knowledge of the subtype being needed in advance. The subtype value behaves to the surrounding code, just like a value of the supertype would, even if it perhaps uses different mechanisms within its specializations of methods.

Instance substitutability refers to the ability to use an object value of a subtype in a context declared in terms of a supertype. REF substitutability refers to the ability to use a REF to a subtype in a context declared in terms of a REF to a supertype.

REF type attributes are substitutable; that is, an attribute defined as REF T can hold a REF to an instance of T or any of its subtypes.

Object type attributes are substitutable; an attribute defined to be of (an object) type T can hold an instance of T or any of its subtypes.

Collection element types are substitutable; if you define a collection of elements of type T, it can hold instances of type T and any of its subtypes. Here is an example of object attribute substitutability:

CREATE TYPE Book_t AS OBJECT 
( title VARCHAR2(30),
  author Person_t     /* substitutable */);

Thus, a Book_t instance can be created by specifying a title string and a Person_t (or any subtype of Person_t) instance:

Book_t('My Oracle Experience',
        Employee_t(12345, 'Joe', 'SF', 1111, NULL))

NOT INSTANTIABLE Types and Methods

A type can be declared to be NOT INSTANTIABLE, which means that there is no constructor (default or user-defined) for the type. Thus, it is not possible to construct instances of this type. The typical usage would be to define instantiable subtypes for such a type. Here is how this property is used:

CREATE TYPE Address_t AS OBJECT(...) NOT INSTANTIABLE NOT FINAL;
CREATE TYPE USAddress_t UNDER Address_t(...);
CREATE TYPE IntlAddress_t UNDER Address_t(...);

A method of a type can be declared to be NOT INSTANTIABLE. Declaring a method as NOT INSTANTIABLE means that the type is not providing an implementation for that method. Further, a type that contains any NOT INSTANTIABLE methods must necessarily be declared as NOT INSTANTIABLE. For example:

CREATE TYPE T AS OBJECT
(
  x NUMBER,
  NOT INSTANTIABLE MEMBER FUNCTION func1() RETURN NUMBER
) NOT INSTANTIABLE NOT FINAL;

A subtype of a NOT INSTANTIABLE type can override any of the NOT INSTANTIABLE methods of the supertype and provide concrete implementations. If there are any NOT INSTANTIABLE methods remaining, the subtype must also necessarily be declared as NOT INSTANTIABLE.

A NOT INSTANTIABLE subtype can be defined under an instantiable supertype. Declaring a NOT INSTANTIABLE type to be FINAL is not useful and is not allowed.

OCI Support for Type Inheritance

The following calls support type inheritance.

OTT Support for Type Inheritance

The Object Type Translator (OTT) supports type inheritance of objects by declaring first the inherited attributes in an encapsulated struct called "_super", followed by the new declared attributes. This is done because C does not support type inheritance.