10 More OCI Advanced Topics

Using Query Result Set Notifications

To record QOS (quality of service flags) specific to continuous query (CQ) notifications, set the attribute OCI_ATTR_SUBSCR_CQ_QOSFLAGS on the subscription handle OCI_HTYPE_SUBSCR. To request that the registration is at query granularity, as opposed to object granularity, set the OCI_SUBSCR_CQ_QOS_QUERY flag bit on the attribute OCI_ATTR_SUBSCR_CQ_QOSFLAGS.

The pseudocolumn CQ_NOTIFICATION_QUERY_ID can be optionally specified to retrieve the query ID of a registered query. Note that this does not automatically convert the granularity to query level. The value of the pseudocolumn on return is set to the unique query ID assigned to the query. The query ID pseudocolumn can be omitted for OCI-based registrations, in which case the query ID is returned as a READ attribute of the statement handle. (This attribute is called OCI_ATTR_CQ_QUERYID).

During notifications, the client-specified callback is invoked and the top-level notification descriptor is passed as an argument.

Information about the query IDs of the changed queries is conveyed through a special descriptor type called OCI_DTYPE_CQDES. A collection (OCIColl) of query descriptors is embedded inside the top-level notification descriptor. Each descriptor is of type OCI_DTYPE_CQDES. The query descriptor has the following attributes:

• OCI_ATTR_CQDES_OPERATION - can be one of OCI_EVENT_QUERYCHANGE or OCI_EVENT_DEREG.

• OCI_ATTR_CQDES_QUERYID - query ID of the changed query.

• OCI_ATTR_CQDES_TABLE_CHANGES - array of table descriptors describing DML operations on tables that led to the query result set change. Each table descriptor is of the type OCI_DTYPE_TABLE_CHDES.

Registering for Continuous Query Notification

The calling session must have the CHANGE NOTIFICATION system privilege and SELECT privileges on all objects that it attempts to register. A registration is a persistent entity that is recorded in the database, and is visible to all instances of Oracle RAC. If the registration was at query granularity, transactions that cause the query result set to change and commit in any instance of Oracle RAC generate notification.If the registration was at object granularity, transactions that modify registered objects in any instance of Oracle RAC generate notification.

Queries involving materialized views or nonmaterialized views are not supported.

The registration interface employs a callback to respond to changes in underlying objects of a query and uses a namespace extension (DBCHANGE) to AQ.

The steps in writing the registration are as follows:

1. Create the environment in OCI_EVENTS and OCI_OBJECT mode.

2. Set the subscription handle attribute OCI_ATTR_SUBSCR_NAMESPACE to namespace OCI_SUBSCR_NAMESPACE_DBCHANGE.

3. Set the subscription handle attribute OCI_ATTR_SUBSCR_CALLBACK to store the OCI callback associated with the query handle. The callback has the following prototype:

   void notification_callback (void *ctx, OCISubscription *subscrhp, 
                               void *payload, ub4 paylen, void *desc, ub4 mode);
   

   The parameters are described in "Notification Callback in OCI".

4. Optionally associate a client-specific context using OCI_ATTR_SUBSCR_CTX attribute.

5. Set the OCI_ATTR_SUBSCR_TIMEOUT attribute to specify a ub4 timeout interval in seconds. If it is not set, there is no timeout.

6. Set the OCI_ATTR_SUBSCR_QOSFLAGS attribute, the QOS (quality of service) levels, with the following values:

   • The OCI_SUBSCR_QOS_PURGE_ON_NTFN flag allows the registration to be purged on the first notification.

   • The OCI_SUBSCR_QOS_RELIABLE flag allows notifications to be persistent. You can use surviving instances of Oracle RAC to send and retrieve continuous query notification messages, even after a node failure, because invalidations associated with this registration are queued persistently into the database. If FALSE, then invalidations are enqueued into a fast in-memory queue. Note that this option describes the persistence of notifications and not the persistence of registrations. Registrations are automatically persistent by default.

7. Call OCISubscriptionRegister() to create a new registration in the DBCHANGE namespace.

8. Associate multiple query statements with the subscription handle by setting the attribute OCI_ATTR_CHNF_REGHANDLE of the statement handle, OCI_HTYPE_STMT. The registration is completed when the query is executed.

   See Also:

   "OCI_ATTR_CHNF_REGHANDLE"

9. Optionally unregister a subscription. The client can call the OCISubscriptionUnRegister() function with the subscription handle as a parameter.

A binding of a statement handle to a subscription handle is only valid for the first execution of a query. If the application must use the same OCI statement handle for subsequent executions, it must repopulate the registration handle attribute of the statement handle. A binding of a subscription handle to a statement handle is only permitted when the statement is a query (determined at execute time). If a DML statement is executed as part of the execution, then an exception is issued.

Subscription Handle Attributes for Continuous Query Notification

The subscription handle attributes for continuous query notification can be divided into generic attributes (common to all subscriptions) and namespace-specific attributes (particular to continuous query notification).

The WRITE attributes on the statement handle can only be modified before the registration is created.

Generic Attributes - Common to All Subscriptions

OCI_ATTR_SUBSCR_NAMESPACE (WRITE) - Set this attribute to OCI_SUBSCR_NAMESPACE_DBCHANGE for subscription handles.

OCI_ATTR_SUBSCR_CALLBACK (WRITE) - Use this attribute to store the callback associated with the subscription handle. The callback is executed when a notification is received.

When a new continuous query notification message becomes available, the callback is invoked in the listener thread with desc pointing to a descriptor of type OCI_DTYPE_CHDES that contains detailed information about the invalidation.

OCI_ATTR_SUBSCR_QOSFLAGS - This attribute is a generic flag with the following values:

#define OCI_SUBSCR_QOS_RELIABLE             0x01                 /* reliable */
#define OCI_SUBSCR_QOS_PURGE_ON_NTFN        0x10      /* purge on first ntfn */

• OCI_SUBSCR_QOS_RELIABLE - Set this bit to allow notifications to be persistent. Therefore, you can use surviving instances of an Oracle RAC cluster to send and retrieve invalidation messages, even after a node failure, because invalidations associated with this registration ID are queued persistently into the database. If this bit is FALSE, then invalidations are enqueued in to a fast in-memory queue. Note that this option describes the persistence of notifications and not the persistence of registrations. Registrations are automatically persistent by default.

• OCI_SUBSCR_QOS_PURGE_ON_NTFN - Set this bit to allow the registration to be purged on the first notification.

A parallel example is presented in "Publish-Subscribe Registration Functions in OCI".

OCI_ATTR_SUBSCR_CQ_QOSFLAGS - This attribute describes the continuous query notification-specific QOS flags (mode is WRITE, data type is ub4), which are:

• 0x1 OCI_SUBSCR_CQ_QOS_QUERY - Set this flag to indicate that query-level granularity is required. Notification should be only generated if the query result set changes. By default, this level of QOS has no false positives.

• 0x2 OCI_SUBSCR_CQ_QOS_BEST_EFFORT - Set this flag to indicate that best effort filtering is acceptable. It may be used by caching applications. The database may use heuristics based on cost of evaluation and avoid full pruning in some cases.

OCI_ATTR_SUBSCR_TIMEOUT - Use this attribute to specify a ub4 timeout value defined in seconds. If the timeout value is 0 or not specified, then the registration is active until explicitly unregistered.

Namespace- Specific or Feature-Specific Attributes

The following attributes are namespace-specific or feature-specific to the continuous query notification feature.

OCI_ATTR_CHNF_TABLENAMES (data type is (OCIColl *)) - These attributes are provided to retrieve the list of table names that were registered. These attributes are available from the subscription handle, after the query is executed.

OCI_ATTR_CHNF_ROWIDS - A Boolean attribute (default FALSE). If set to TRUE, then the continuous query notification message includes row-level details such as operation type and ROWID.

OCI_ATTR_CHNF_OPERATIONS - Use this ub4 flag to selectively filter notifications based on operation type. This option is ignored if the registration is of query-level granularity. Flags stored are as follows:

• OCI_OPCODE_ALL - All operations

• OCI_OPCODE_INSERT - Insert operations on the table

• OCI_OPCODE_UPDATE - Update operations on the table

• OCI_OPCODE_DELETE - Delete operations on the table

OCI_ATTR_CHNF_CHANGELAG - The client can use this ub4 value to specify the number of transactions by which the client is willing to lag behind. The client can also use this option as a throttling mechanism for continuous query notification messages. When you choose this option, ROWID-level granularity of information is not available in the notifications, even if OCI_ATTR_CHNF_ROWIDS was set to TRUE. This option is ignored if the registration is of query-level granularity.

Once the OCISubscriptionRegister() call is invoked, none of the preceding attributes (generic, name-specific, or feature-specific) can be modified on the registration already created. Any attempt to modify those attributes is not reflected on the registration already created, but it does take effect on newly created registrations that use the same registration handle.

Notifications can be spaced out by using the grouping NTFN option. The relevant generic notification attributes are:

OCI_ATTR_SUBSCR_NTFN_GROUPING_VALUE
OCI_ATTR_SUBSCR_NTFN_GROUPING_TYPE
OCI_ATTR_SUBSCR_NTFN_GROUPING_START_TIME
OCI_ATTR_SUBSCR_NTFN_GROUPING_REPEAT_COUNT

Using OCI_ATTR_CQ_QUERYID Attribute

The attribute OCI_ATTR_CQ_QUERYID on the statement handle, OCI_HTYPE_STMT, obtains the query ID of a registered query after registration is made by the call to OCIStmtExecute().

Continuous Query Notification Descriptors

The continuous query notification descriptor is passed into the desc parameter of the notification callback specified by the application. The following attributes are specific to continuous query notification. The OCI type constant of the continuous query notification descriptor is OCI_DTYPE_CHDES.

The notification callback receives the top-level notification descriptor, OCI_DTYPE_CHDES, as an argument. This descriptor in turn includes either a collection of OCI_DTYPE_CQDES or OCI_DTYPE_TABLE_CHDES descriptors based on whether the event type was OCI_EVENT_QUERYCHANGE or OCI_EVENT_OBJCHANGE. An array of table continuous query descriptors is embedded inside the continuous query descriptor for notifications of type OCI_EVENT_QUERYCHANGE. If ROWID level granularity of information was requested, each OCI_DTYPE_TABLE_CHDES contains an array of row-level continuous query descriptors (OCI_DTYPE_ROW_CHDES) corresponding to each modified ROWID.

Continuous Query Notification Example

Example 10-1 is a simple OCI program, demoquery.c. See the comments in the listing. The calling session must have the CHANGE NOTIFICATION system privilege and SELECT privileges on all objects that it attempts to register.

/* Copyright (c) 2010, Oracle. All rights reserved.  */
 
#ifndef S_ORACLE
# include <oratypes.h>
#endif
 
/**************************************************************************
 *This is a DEMO program. To test, compile the file to generate the executable
 *demoquery. Then demoquery can be invoked from a command prompt.
 *It will have the following output:
 
Initializing OCI Process
Registering query : select last_name, employees.department_id, department_name 
                     from employees, departments 
                     where employee_id = 200 
                     and employees.department_id = departments.department_id
Query Id 23
Waiting for Notifications

*Then from another session, log in as HR/HR and perform the following
* DML transactions. It will cause two notifications to be generated.
 
update departments set department_name ='Global Admin' where department_id=10;
commit;
update departments set department_name ='Adminstration' where department_id=10;
commit;

*The demoquery program will now show the following output corresponding
*to the notifications received.
 
 
Query 23 is changed
Table changed is HR.DEPARTMENTS table_op 4
Row changed is AAAMBoAABAAAKX2AAA row_op 4
Query 23 is changed
Table changed is HR.DEPARTMENTS table_op 4
Row changed is AAAMBoAABAAAKX2AAA row_op 4
 
 
*The demo program waits for exactly 10 notifications to be received before
*logging off and unregistering the subscription.
 
***************************************************************************/
 
/*---------------------------------------------------------------------------
                     PRIVATE TYPES AND CONSTANTS
  ---------------------------------------------------------------------------*/

/*---------------------------------------------------------------------------
                     STATIC FUNCTION DECLARATIONS 
  ---------------------------------------------------------------------------*/
 
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>
 
#define MAXSTRLENGTH 1024
#define bit(a,b) ((a)&(b))
 
static int notifications_processed = 0;
static OCISubscription *subhandle1 = (OCISubscription *)0;
static OCISubscription *subhandle2 = (OCISubscription *)0;
static void checker(/*_ OCIError *errhp, sword status _*/);
static void registerQuery(/*_ OCISvcCtx *svchp, OCIError *errhp, OCIStmt *stmthp,
                           OCIEnv *envhp _*/);
static void myCallback (/*_  dvoid *ctx, OCISubscription *subscrhp, 
                        dvoid *payload, ub4 *payl, dvoid *descriptor, 
                        ub4 mode _*/);
static int NotificationDriver(/*_ int argc, char *argv[]  _*/);
static sword status;
static boolean logged_on = FALSE;
static void processRowChanges(OCIEnv *envhp, OCIError *errhp, OCIStmt *stmthp, 
                               OCIColl *row_changes);
static void processTableChanges(OCIEnv *envhp, OCIError *errhp,
                 OCIStmt *stmthp, OCIColl *table_changes);
static void processQueryChanges(OCIEnv *envhp, OCIError *errhp, OCIStmt *stmthp,
                 OCIColl *query_changes);
static int nonractests2(/*_ int argc, char *argv[] _*/);
 
 
int main(int argc, char **argv)
{
 
  NotificationDriver(argc, argv);
  return 0;
}
 
 
int NotificationDriver(argc, argv)
int argc;
char *argv[];
{
  OCIEnv *envhp;
  OCISvcCtx *svchp, *svchp2;
  OCIError *errhp, *errhp2;
  OCISession *authp, *authp2;
  OCIStmt *stmthp, *stmthp2;
  OCIDuration dur, dur2;
  int i;
  dvoid *tmp;
  OCISession *usrhp;
  OCIServer *srvhp;
 
  printf("Initializing OCI Process\n");
/* Initialize the environment. The environment must be initialized
     with OCI_EVENTS and OCI_OBJECT to create a continuous query notification
     registration and receive notifications.
  */
  OCIEnvCreate( (OCIEnv **) &envhp, OCI_EVENTS|OCI_OBJECT, (dvoid *)0,
                    (dvoid * (*)(dvoid *, size_t)) 0,
                    (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                    (void (*)(dvoid *, dvoid *)) 0,
                    (size_t) 0, (dvoid **) 0 );
 
  OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR,
                         (size_t) 0, (dvoid **) 0);
   /* server contexts */
  OCIHandleAlloc((dvoid *) envhp, (dvoid **) &srvhp, OCI_HTYPE_SERVER, 
                 (size_t) 0, (dvoid **) 0);
  OCIHandleAlloc((dvoid *) envhp, (dvoid **) &svchp, OCI_HTYPE_SVCCTX,
                 (size_t) 0, (dvoid **) 0);
   checker(errhp,OCIServerAttach(srvhp, errhp, (text *) 0, (sb4) 0, 
                                 (ub4) OCI_DEFAULT));
  /* set attribute server context in the service context */
  OCIAttrSet( (dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *)srvhp,
              (ub4) 0, (ub4) OCI_ATTR_SERVER, (OCIError *) errhp);
 
   /* allocate a user context handle */
  OCIHandleAlloc((dvoid *)envhp, (dvoid **)&usrhp, (ub4) OCI_HTYPE_SESSION,
                               (size_t) 0, (dvoid **) 0);
 
  OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION,
             (dvoid *)((text *)"HR"), (ub4)strlen((char *)"HR"), 
              OCI_ATTR_USERNAME, errhp);
 
 OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION,
            (dvoid *)((text *)"HR"), (ub4)strlen((char *)"HR"),  
             OCI_ATTR_PASSWORD, errhp);
   checker(errhp,OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS,
           OCI_DEFAULT));
   /* Allocate a statement handle */
  OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp,
                                (ub4) OCI_HTYPE_STMT, 52, (dvoid **) &tmp);
 
  OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX, (dvoid *)usrhp, (ub4)0,
                       OCI_ATTR_SESSION, errhp);
 
  registerQuery(svchp, errhp, stmthp, envhp);
  printf("Waiting for Notifications\n");
  while (notifications_processed !=10)
  {
    sleep(1);
  }
  printf ("Going to unregister HR\n");
  fflush(stdout);
  /* Unregister HR */
  checker(errhp,
           OCISubscriptionUnRegister(svchp, subhandle1, errhp, OCI_DEFAULT));
  checker(errhp, OCISessionEnd(svchp, errhp, usrhp, (ub4) 0));
   printf("HR Logged off.\n");
 
  if (subhandle1)
     OCIHandleFree((dvoid *)subhandle1, OCI_HTYPE_SUBSCRIPTION);
  if (stmthp)
     OCIHandleFree((dvoid *)stmthp, OCI_HTYPE_STMT);
  if (srvhp)
     OCIHandleFree((dvoid *) srvhp, (ub4) OCI_HTYPE_SERVER);
  if (svchp)
     OCIHandleFree((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX);
  if (authp)
     OCIHandleFree((dvoid *) usrhp, (ub4) OCI_HTYPE_SESSION);
  if (errhp)
     OCIHandleFree((dvoid *) errhp, (ub4) OCI_HTYPE_ERROR);
  if (envhp)
     OCIHandleFree((dvoid *) envhp, (ub4) OCI_HTYPE_ENV);
 
 
  return 0;
 
}
 
void checker(errhp, status)
OCIError *errhp;
sword status;
{
  text errbuf[512];
  sb4 errcode = 0;
  int retval = 1;
 
  switch (status)
  {
  case OCI_SUCCESS:
    retval = 0;
    break;
  case OCI_SUCCESS_WITH_INFO:
    (void) printf("Error - OCI_SUCCESS_WITH_INFO\n");
    break;
 case OCI_NEED_DATA:
    (void) printf("Error - OCI_NEED_DATA\n");
    break;
  case OCI_NO_DATA:
    (void) printf("Error - OCI_NODATA\n");
    break;
  case OCI_ERROR:
    (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
                        errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
    (void) printf("Error - %.*s\n", 512, errbuf);
    break;
  case OCI_INVALID_HANDLE:
    (void) printf("Error - OCI_INVALID_HANDLE\n");
    break;
  case OCI_STILL_EXECUTING:
    (void) printf("Error - OCI_STILL_EXECUTE\n");
    break; 
  case OCI_CONTINUE:
    (void) printf("Error - OCI_CONTINUE\n");
    break; 
  default:
    break;
 }
  if (retval)
  {
    exit(1);
  }
}
 
 
void processRowChanges(OCIEnv *envhp, OCIError *errhp, OCIStmt *stmthp,
                         OCIColl *row_changes)
{
  dvoid **row_descp;
  dvoid *row_desc;
  boolean exist; 
  ub2 i, j;
  dvoid *elemind = (dvoid *)0;
  oratext *row_id;
  ub4 row_op;
 
 
   sb4 num_rows;
   if (!row_changes) return;
    checker(errhp, OCICollSize(envhp, errhp,
                    (CONST OCIColl *) row_changes, &num_rows));
    for (i=0; i<num_rows; i++)
    {
      checker(errhp, OCICollGetElem(envhp,
                     errhp, (OCIColl *) row_changes,
                     i, &exist, &row_descp, &elemind));
 
      row_desc = *row_descp;
      checker(errhp, OCIAttrGet (row_desc, 
                  OCI_DTYPE_ROW_CHDES, (dvoid *)&row_id,
                  NULL, OCI_ATTR_CHDES_ROW_ROWID, errhp));
      checker(errhp, OCIAttrGet (row_desc, 
                  OCI_DTYPE_ROW_CHDES, (dvoid *)&row_op, 
                  NULL, OCI_ATTR_CHDES_ROW_OPFLAGS, errhp));
 
      printf ("Row changed is %s row_op %d\n", row_id, row_op);
      fflush(stdout); 
    }  
}
 
void processTableChanges(OCIEnv *envhp, OCIError *errhp, OCIStmt *stmthp,
                         OCIColl *table_changes)
{
  dvoid **table_descp;
  dvoid *table_desc;
  dvoid **row_descp;
  dvoid *row_desc;
  OCIColl *row_changes = (OCIColl *)0;
  boolean exist; 
  ub2 i, j;
  dvoid *elemind = (dvoid *)0;
  oratext *table_name;
  ub4 table_op;
 
 
   sb4 num_tables;
   if (!table_changes) return;
    checker(errhp, OCICollSize(envhp, errhp,
                    (CONST OCIColl *) table_changes, &num_tables));
    for (i=0; i<num_tables; i++)
    {
      checker(errhp, OCICollGetElem(envhp,
                     errhp, (OCIColl *) table_changes,
                     i, &exist, &table_descp, &elemind));
 
      table_desc = *table_descp;
      checker(errhp, OCIAttrGet (table_desc, 
                  OCI_DTYPE_TABLE_CHDES, (dvoid *)&table_name,
                  NULL, OCI_ATTR_CHDES_TABLE_NAME, errhp));
      checker(errhp, OCIAttrGet (table_desc, 
                  OCI_DTYPE_TABLE_CHDES, (dvoid *)&table_op, 
                  NULL, OCI_ATTR_CHDES_TABLE_OPFLAGS, errhp));
      checker(errhp, OCIAttrGet (table_desc, 
                  OCI_DTYPE_TABLE_CHDES, (dvoid *)&row_changes, 
                  NULL, OCI_ATTR_CHDES_TABLE_ROW_CHANGES, errhp));
 
      printf ("Table changed is %s table_op %d\n", table_name,table_op);
      fflush(stdout); 
     if (!bit(table_op, OCI_OPCODE_ALLROWS))
       processRowChanges(envhp, errhp, stmthp, row_changes);
    }  
}
 
void processQueryChanges(OCIEnv *envhp, OCIError *errhp, OCIStmt *stmthp,
                         OCIColl *query_changes)
{
  sb4 num_queries;
  ub8 queryid;
  OCINumber qidnum;
  ub4 queryop;
  dvoid *elemind = (dvoid *)0;
  dvoid *query_desc;
  dvoid **query_descp;
  ub2 i;
  boolean exist;
  OCIColl *table_changes = (OCIColl *)0;
  
  if (!query_changes) return;
  checker(errhp, OCICollSize(envhp, errhp,
                     (CONST OCIColl *) query_changes, &num_queries));
  for (i=0; i < num_queries; i++)
  {
    checker(errhp, OCICollGetElem(envhp,
                     errhp, (OCIColl *) query_changes,
                     i, &exist, &query_descp, &elemind));
 
    query_desc = *query_descp;
    checker(errhp, OCIAttrGet (query_desc,
                  OCI_DTYPE_CQDES, (dvoid *)&queryid,
                  NULL, OCI_ATTR_CQDES_QUERYID, errhp));
    checker(errhp, OCIAttrGet (query_desc,
                  OCI_DTYPE_CQDES, (dvoid *)&queryop,
                  NULL, OCI_ATTR_CQDES_OPERATION, errhp));
    printf(" Query %d is changed\n", queryid);
    if (queryop == OCI_EVENT_DEREG)
      printf("Query Deregistered\n");
      checker(errhp, OCIAttrGet (query_desc,
                  OCI_DTYPE_CQDES, (dvoid *)&table_changes,
                  NULL, OCI_ATTR_CQDES_TABLE_CHANGES, errhp));
      processTableChanges(envhp, errhp, stmthp, table_changes);
 
 
   }
}
 
   
void myCallback (ctx, subscrhp, payload, payl, descriptor, mode)
dvoid *ctx;
OCISubscription *subscrhp;
dvoid *payload;
ub4 *payl; 
dvoid *descriptor;
ub4 mode;
{
  OCIColl *table_changes = (OCIColl *)0;
  OCIColl *row_changes = (OCIColl *)0;
  dvoid *change_descriptor = descriptor;
  ub4 notify_type;
  ub2 i, j;
  OCIEnv *envhp;
  OCIError *errhp;
  OCIColl *query_changes = (OCIColl *)0;
  OCIServer *srvhp;
  OCISvcCtx *svchp;
  OCISession *usrhp;
  dvoid     *tmp; 
  OCIStmt *stmthp;
 
 (void)OCIEnvInit( (OCIEnv **) &envhp, OCI_DEFAULT, (size_t)0, (dvoid **)0 );
  
  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR,
                   (size_t) 0, (dvoid **) 0);
   /* server contexts */
  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp, OCI_HTYPE_SERVER,
                   (size_t) 0, (dvoid **) 0);
 
  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, OCI_HTYPE_SVCCTX,
                   (size_t) 0, (dvoid **) 0);
 
  OCIAttrGet (change_descriptor, OCI_DTYPE_CHDES, (dvoid *) &notify_type,
              NULL, OCI_ATTR_CHDES_NFYTYPE, errhp);
  fflush(stdout);
  if (notify_type == OCI_EVENT_SHUTDOWN ||
      notify_type == OCI_EVENT_SHUTDOWN_ANY)
  {
     printf("SHUTDOWN NOTIFICATION RECEIVED\n");
     fflush(stdout);
     notifications_processed++;
     return;
  }
 if (notify_type == OCI_EVENT_STARTUP)
  {
     printf("STARTUP NOTIFICATION RECEIVED\n");
     fflush(stdout);
     notifications_processed++; 
     return;
  }
  
  notifications_processed++;
  checker(errhp, OCIServerAttach( srvhp, errhp, (text *) 0, (sb4) 0,
                                  (ub4) OCI_DEFAULT));
 
  OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, (ub4) OCI_HTYPE_SVCCTX,
                  52, (dvoid **) &tmp);
  /* set attribute server context in the service context */
  OCIAttrSet( (dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *)srvhp,
              (ub4) 0, (ub4) OCI_ATTR_SERVER, (OCIError *) errhp);
 
  /* allocate a user context handle */
  OCIHandleAlloc((dvoid *)envhp, (dvoid **)&usrhp, (ub4) OCI_HTYPE_SESSION,
           (size_t) 0, (dvoid **) 0);
 
  OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION,
           (dvoid *)"HR", (ub4)strlen("HR"), OCI_ATTR_USERNAME, errhp);
 
  OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION,
           (dvoid *)"HR", (ub4)strlen("HR"),
           OCI_ATTR_PASSWORD, errhp);
 
  checker(errhp, OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS,
                                   OCI_DEFAULT));
 
  OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX,
           (dvoid *)usrhp, (ub4)0, OCI_ATTR_SESSION, errhp);
 
  /* Allocate a statement handle */
  OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp,
                  (ub4) OCI_HTYPE_STMT, 52, (dvoid **) &tmp);
 
  if (notify_type == OCI_EVENT_OBJCHANGE)
  {
    checker(errhp, OCIAttrGet (change_descriptor,
                OCI_DTYPE_CHDES, &table_changes, NULL,
                OCI_ATTR_CHDES_TABLE_CHANGES, errhp));
    processTableChanges(envhp, errhp, stmthp, table_changes);
  }
  else if (notify_type == OCI_EVENT_QUERYCHANGE)
  {
     checker(errhp, OCIAttrGet (change_descriptor,
                OCI_DTYPE_CHDES, &query_changes, NULL,
                OCI_ATTR_CHDES_QUERIES, errhp));
      processQueryChanges(envhp, errhp, stmthp, query_changes);
  }
   checker(errhp, OCISessionEnd(svchp, errhp, usrhp, OCI_DEFAULT));
  checker(errhp, OCIServerDetach(srvhp, errhp, OCI_DEFAULT));
 if (stmthp)
    OCIHandleFree((dvoid *)stmthp, OCI_HTYPE_STMT);
  if (errhp)
    OCIHandleFree((dvoid *)errhp, OCI_HTYPE_ERROR);
  if (srvhp)
    OCIHandleFree((dvoid *)srvhp, OCI_HTYPE_SERVER);
  if (svchp)
    OCIHandleFree((dvoid *)svchp, OCI_HTYPE_SVCCTX);
  if (usrhp)
    OCIHandleFree((dvoid *)usrhp, OCI_HTYPE_SESSION);
  if (envhp)
    OCIHandleFree((dvoid *)envhp, OCI_HTYPE_ENV);
 
}
 
void registerQuery(svchp, errhp, stmthp, envhp)
OCISvcCtx *svchp; 
OCIError *errhp; 
OCIStmt *stmthp;
OCIEnv *envhp;
{
  OCISubscription *subscrhp;
  ub4 namespace = OCI_SUBSCR_NAMESPACE_DBCHANGE;
  ub4 timeout = 60;
  OCIDefine *defnp1 = (OCIDefine *)0;
  OCIDefine *defnp2 = (OCIDefine *)0;
  OCIDefine *defnp3 = (OCIDefine *)0;
  OCIDefine *defnp4 = (OCIDefine *)0;
  OCIDefine *defnp5 = (OCIDefine *)0;
  int mgr_id =0;
text query_text1[] = "select last_name, employees.department_id, department_name \
 from employees,departments where employee_id = 200 and employees.department_id =\
  departments.department_id";
 
  ub4 num_prefetch_rows = 0;
  ub4 num_reg_tables;
  OCIColl *table_names;
  ub2 i;
  boolean rowids = TRUE;
  ub4 qosflags = OCI_SUBSCR_CQ_QOS_QUERY  ;
  int empno=0;
  OCINumber qidnum;
  ub8 qid;
  char outstr[MAXSTRLENGTH], dname[MAXSTRLENGTH];
  int q3out;
 
    fflush(stdout);
  /* allocate subscription handle */
  OCIHandleAlloc ((dvoid *) envhp, (dvoid **) &subscrhp,
                  OCI_HTYPE_SUBSCRIPTION, (size_t) 0, (dvoid **) 0);
  
  /* set the namespace to DBCHANGE */
  checker(errhp, OCIAttrSet (subscrhp, OCI_HTYPE_SUBSCRIPTION,
                  (dvoid *) &namespace, sizeof(ub4),
                  OCI_ATTR_SUBSCR_NAMESPACE, errhp));
  
  /* Associate a notification callback with the subscription */
  checker(errhp, OCIAttrSet (subscrhp, OCI_HTYPE_SUBSCRIPTION,
                  (void *)myCallback, 0, OCI_ATTR_SUBSCR_CALLBACK, errhp));
 /* Allow extraction of rowid information */
  checker(errhp, OCIAttrSet (subscrhp, OCI_HTYPE_SUBSCRIPTION,
                  (dvoid *)&rowids, sizeof(ub4), 
                  OCI_ATTR_CHNF_ROWIDS, errhp));
   
     checker(errhp, OCIAttrSet (subscrhp, OCI_HTYPE_SUBSCRIPTION,
                  (dvoid *)&qosflags, sizeof(ub4),
                  OCI_ATTR_SUBSCR_CQ_QOSFLAGS, errhp));
 
  /* Create a new registration in the DBCHANGE namespace */
  checker(errhp,
           OCISubscriptionRegister(svchp, &subscrhp, 1, errhp, OCI_DEFAULT));
 
  /* Multiple queries can now be associated with the subscription */
 
    subhandle1 = subscrhp;
 
 
    printf("Registering query : %s\n", (const signed char *)query_text1);
    /* Prepare the statement */
    checker(errhp, OCIStmtPrepare (stmthp, errhp, query_text1, 
            (ub4)strlen((const signed char *)query_text1), OCI_V7_SYNTAX,
            OCI_DEFAULT));
 
    checker(errhp,
           OCIDefineByPos(stmthp, &defnp1,
                  errhp, 1, (dvoid *)outstr, MAXSTRLENGTH * sizeof(char),
                  SQLT_STR, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT));
    checker(errhp,
           OCIDefineByPos(stmthp, &defnp2,
                     errhp, 2, (dvoid *)&empno, sizeof(empno),
                     SQLT_INT, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT));
    checker(errhp,
           OCIDefineByPos(stmthp, &defnp3,
                      errhp, 3, (dvoid *)&dname, sizeof(dname),
                     SQLT_STR, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT));
 
    /* Associate the statement with the subscription handle */
    OCIAttrSet (stmthp, OCI_HTYPE_STMT, subscrhp, 0,
              OCI_ATTR_CHNF_REGHANDLE, errhp);
 
    /* Execute the statement, the execution performs object registration */
    checker(errhp, OCIStmtExecute (svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                 (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL ,
                 OCI_DEFAULT));
    fflush(stdout);
 
    OCIAttrGet(stmthp, OCI_HTYPE_STMT, &qid, (ub4 *)0,
                OCI_ATTR_CQ_QUERYID, errhp);
    printf("Query Id %d\n", qid);
 
  /* commit */
  checker(errhp, OCITransCommit(svchp, errhp, (ub4) 0));
 
}
 
static void cleanup(envhp, svchp, srvhp, errhp, usrhp)
OCIEnv *envhp;
OCISvcCtx *svchp;
OCIServer *srvhp;
OCIError *errhp;
OCISession *usrhp;
{
  /* detach from the server */
  checker(errhp, OCISessionEnd(svchp, errhp, usrhp, OCI_DEFAULT));
  checker(errhp, OCIServerDetach(srvhp, errhp, (ub4)OCI_DEFAULT));
 
  if (usrhp)
    (void) OCIHandleFree((dvoid *) usrhp, (ub4) OCI_HTYPE_SESSION);
  if (svchp)
    (void) OCIHandleFree((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX);
  if (srvhp)
    (void) OCIHandleFree((dvoid *) srvhp, (ub4) OCI_HTYPE_SERVER);
  if (errhp)
    (void) OCIHandleFree((dvoid *) errhp, (ub4) OCI_HTYPE_ERROR);
  if (envhp)
    (void) OCIHandleFree((dvoid *) envhp, (ub4) OCI_HTYPE_ENV);
 
}

Examples of Startup and Shutdown in OCI

To perform a startup, you must be connected to the database as SYSOPER or SYSDBA in OCI_PRELIM_AUTH mode. You cannot be connected to a shared server through a dispatcher. To use a client-side parameter file (pfile), the attribute OCI_ATTR_ADMIN_PFILE must be set in the administration handle using OCIAttrSet(); otherwise, a server-side parameter file (spfile) is used. In the latter case, pass (OCIAdmin *)0. A call to OCIDBStartup() starts one instance on the server.

Example 10-2 shows sample code that uses a client-side parameter file (pfile) that is set in the administration handle and performs a database startup operation.

...

/*  Example 0 - Startup:  */
OCIAdmin *admhp;
text *mount_stmt = (text *)"ALTER DATABASE MOUNT";
text *open_stmt = (text *)"ALTER DATABASE OPEN";
text *pfile = (text *)"/ade/viewname/oracle/work/t_init1.ora";

/* Start the authentication session */
checkerr(errhp, OCISessionBegin (svchp, errhp, usrhp,
         OCI_CRED_RDBMS, OCI_SYSDBA|OCI_PRELIM_AUTH));

/* Allocate admin handle for OCIDBStartup */
checkerr(errhp, OCIHandleAlloc((void *) envhp, (void **) &admhp,
         (ub4) OCI_HTYPE_ADMIN, (size_t) 0, (void **) 0));

/* Set attribute pfile in the admin handle 
(do not do this if you want to use the spfile) */
checkerr (errhp, OCIAttrSet( (void *) admhp, (ub4) OCI_HTYPE_ADMIN,
          (void *) pfile, (ub4) strlen(pfile),
          (ub4) OCI_ATTR_ADMIN_PFILE, (OCIError *) errhp));

/* Start up in NOMOUNT mode */
  checkerr(errhp, OCIDBStartup(svchp, errhp, admhp, OCI_DEFAULT, 0));
   checkerr(errhp, OCIHandleFree((void *) admhp, (ub4) OCI_HTYPE_ADMIN));

/* End the authentication session */
OCISessionEnd(svchp, errhp, usrhp, (ub4)OCI_DEFAULT);

/* Start the sysdba session */
checkerr(errhp, OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS,
         OCI_SYSDBA));

/* Mount the database */
checkerr(errhp, OCIStmtPrepare2(svchp, &stmthp, errhp, mount_stmt, (ub4)
         strlen((char*) mount_stmt),
         (CONST OraText *) 0, (ub4) 0, (ub4) OCI_NTV_SYNTAX, (ub4)
         OCI_DEFAULT));
checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4)0,
         (OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT));
checkerr(errhp, OCIStmtRelease(stmthp, errhp, (OraText *)0, 0, OCI_DEFAULT));

/* Open the database */
checkerr(errhp, OCIStmtPrepare2(svchp, &stmthp, errhp, open_stmt, (ub4)
         strlen((char*) open_stmt),
         (CONST OraText *)0, (ub4)0, (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4)0,
         (OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT));
checkerr(errhp, OCIStmtRelease(stmthp, errhp, (OraText *)0, 0, OCI_DEFAULT));

/* End the sysdba session */
OCISessionEnd(svchp, errhp, usrhp, (ub4)OCI_DEFAULT);
...

To perform a shutdown, you must be connected to the database as SYSOPER or SYSDBA. You cannot be connected to a shared server through a dispatcher. When shutting down in any mode other than OCI_DBSHUTDOWN_ABORT, use the following procedure:

1. Call OCIDBShutdown() in OCI_DEFAULT, OCI_DBSHUTDOWN_TRANSACTIONAL, OCI_DBSHUTDOWN_TRANSACTIONAL_LOCAL, or OCI_DBSHUTDOWN_IMMEDIATE mode to prohibit further connections.

2. Use the necessary ALTER DATABASE commands to close and dismount the database.

3. Call OCIDBShutdown() in OCI_DBSHUTDOWN_FINAL mode to shut down the instance.

Example 10-3 shows sample code that uses a client-side parameter file (pfile) that is set in the administration handle that performs an orderly database shutdown operation.

/*  Example 1 - Orderly shutdown:  */
...
text *close_stmt = (text *)"ALTER DATABASE CLOSE NORMAL";
text *dismount_stmt = (text *)"ALTER DATABASE DISMOUNT";

/* Start the sysdba session */
checkerr(errhp, OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS,
         OCI_SYSDBA));

/* Shutdown in the default mode (transactional, transactional-local,
  immediate would be fine too) */
checkerr(errhp, OCIDBShutdown(svchp, errhp, (OCIAdmin *)0, OCI_DEFAULT));

/* Close the database */
checkerr(errhp, OCIStmtPrepare2(svchp, &stmthp, errhp, close_stmt, (ub4)
         strlen((char*) close_stmt),
         (CONST OraText *)0, (ub4)0, (ub4) OCI_NTV_SYNTAX,
         (ub4) OCI_DEFAULT));
checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4)0,
        (OCISnapshot *) NULL,
        (OCISnapshot *) NULL, OCI_DEFAULT));
checkerr(errhp, OCIStmtRelease(stmthp, errhp, (OraText *)0, 0, OCI_DEFAULT));

/* Dismount the database */
checkerr(errhp, OCIStmtPrepare2(svchp, &stmthp, errhp, dismount_stmt,
         (ub4) strlen((char*) dismount_stmt), (CONST OraText *)0, (ub4)0,
         (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4)0,
         (OCISnapshot *) NULL,
         (OCISnapshot *) NULL, OCI_DEFAULT));
checkerr(errhp, OCIStmtRelease(stmthp, errhp, (OraText *)0, 0, OCI_DEFAULT));

/* Final shutdown */
checkerr(errhp, OCIDBShutdown(svchp, errhp, (OCIAdmin *)0,
         OCI_DBSHUTDOWN_FINAL));

/* End the sysdba session */
checkerr(errhp, OCISessionEnd(svchp, errhp, usrhp, (ub4)OCI_DEFAULT));
...

Example 10-4 shows a shutdown example that uses OCI_DBSHUTDOWN_ABORT mode.

/*  Example 2 - Shutdown using abort:  */
...
/* Start the sysdba session */
...
checkerr(errhp, OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS,
         OCI_SYSDBA));

/* Shutdown in the abort mode */
checkerr(errhp, OCIDBShutdown(svchp, errhp, (OCIAdmin *)0,
         OCI_DBSHUTDOWN_ABORT));

/* End the sysdba session */
checkerr(errhp, OCISessionEnd(svchp, errhp, usrhp, (ub4)OCI_DEFAULT));
...

Example of Implicit Fetching of ROWIDs

Use the fragment of a C program in Example 10-5 to build upon.

#include <oci.h>
 
int main()
{
 ...
 text *mySql = (text *) "SELECT emp_name FROM emp FOR UPDATE";
 text rowid[100][15] = {0};
 text empName[100][15] = {0};
 ...
 
 /* Set up the environment, error handle, etc. */
 ...
 
 /* Prepare the statement -  select ... for update. */
 
 
  if (OCIStmtPrepare (select_p, errhp,
                      mySql, strlen(mySql), OCI_NTV_SYNTAX, OCI_DEFAULT))
  {
    printf ("Prepare failed \n");
    return (OCI_ERROR);
  }
 
 /* Set attribute for implicit fetching of ROWIDs on the statement handle. */
 if (OCIAttrSet(select_p, OCI_HTYPE_STMT, 0, 0, OCI_ATTR_FETCH_ROWID, errhp))
 {
   printf ("Unable to set the attribute - OCI_ATTR_FETCH_ROWID \n");
   return OCI_ERROR;
 }
  /* 
   * Define the positions: 0 for getting ROWIDs and other positions 
   * to fetch other columns.
   * Also, get the define conversion done implicitly by fetching 
   * the ROWIDs in the string format. 
   */
 
  if (OCIDefineByPos ( select_p,
                       &defnp0,
                       errhp,
                       0,
                       rowid[0],
                       15,
                       SQLT_STR,
                       (void *) ind,
                       (void *) 0,
                       (void *) 0,
                       OCI_DEFAULT) ||
       OCIDefineByPos(select_p,
                       &defnp1,
                       errhp,
                       1,
                       empName[0],
                       15,
                       SQLT_STR,
                       (void *) 0,
                       (void *) 0,
                       (void *) 0,
                       OCI_DEFAULT)
                       )
  {
    printf ("Failed to define\n");
    return (OCI_ERROR);
  }
 
 
  /* Execute the statement. */
 
 if (errr = OCIStmtExecute(svchp,
                            select_p,
                            errhp,
                            (ub4) 5,
                            (ub4) 0,
                            (OCISnapshot *) NULL,
                            (OCISnapshot *) NULL,
                            (ub4) OCI_DEFAULT))
  {
    if (errr != OCI_NO_DATA) 
       return errr;
  }
 
  printf ("Column 0  \t Column 1\n");
  printf ("_________ \t ________\n");
 
  for (i =0 ;i<5 i++)
   {
     printf("%s \t %s \n", rowid[i], empName[i]);
   }
 
 return OCI_SUCCESS;  
}

Benefits of Client Result Cache

The benefits of OCI client query result cache are as follows:

• Because the result cache is on the client side, a cache hit causes OCIStmtExecute() and OCIStmtFetch2() calls to be processed locally, instead of making server round-trips. This can result in huge performance savings for server resources, for example, server CPU and server I/O.

• The OCI client-side query result set cache is a transparent and consistent cache.

• The result cache on OCI client is per-process, so multiple client sessions can simultaneously use matching cached result sets.

• It minimizes the need for each OCI application to have its own custom result set cache.

• It transparently manages the caching aspects of the cached result sets, that is: concurrent access by multiple threads, multiple statements, multiple sessions, invalidation, refreshing of result sets in the cache, and cache memory management.

• It transparently invalidates the cached result sets on any database changes that may affect the result sets, when an OCI process makes round-trips to the server.

• This consistent cache is automatically available to all OCI applications and drivers (such as JDBC OCI, ODP.Net, OCCI, Pro*C/C++, Pro*COBOL, ODBC, and so on) built using OCI.

• The cache uses OCI client memory that may be less expensive than server memory.

• A local cache on the client has better locality of reference for queries executed by that client.

Guidelines for Using Client Result Cache

You can enable client result caching in several ways for your application and establish an order of precedence in its usage based on the methods selected. See "Cache Example Use Cases" for more usage information.

• SQL Hints - Annotate a query with a SQL hint /*+ result_cache */ to indicate that results are to be stored in the query result cache. Using SQL hints is the highest order of precedence; it takes precedence over table annotations and session parameters. It is applicable to a single query. This method requires application-level changes.

• Table Annotation - Annotate a table during deployment using result cache hints in the ALTER TABLE and CREATE TABLE statements. Using table annotation is the next highest order of precedence below SQL hints and above session parameters when using MODE FORCE. It is applicable to all queries for that table. This method requires no application-level changes.

• Session Parameters - Works across all tables for all queries; use this method when possible. You can either set the RESULT_CACHE_MODE initialization parameter in the server parameter file (init.ora) or use RESULT_CACHE_MODE clause in the ALTER SESSION and the ALTER SYSTEM statements. Using session parameters is the lowest order of precedence; both SQL hints and table annotations take precedence over session parameters usage. It is the most widely effective usage being applicable to all tables. This method requires no application-level changes.

Oracle recommends that applications annotate tables and queries with result cache hints for read-only or read-mostly database objects. If the result caching happens for queries with large results, these results can use a large amount of cache memory.

As each set of bind values specified by the application creates a different cached result set (for the same SQL text), these result sets together can use a large amount of client result cache memory.

When client result caching is enabled, the query result set can be cached on the client or on the server or both. The client result caching can be enabled even if the server result cache (that is enabled by default) is disabled.

The first OCIStmtExecute() call of every OCI statement handle call always goes to the server even if there might be a valid cached result set. It is necessary that an OCIStmtExecute() call be made for each statement handle to be able to match a cached result set. Oracle recommends that applications have their own statement caching for OCI statement handles, or use OCI statement caching so that OCIStmtPrepare2() can return an OCI statement handle that has been executed once. Multiple OCI statement handles, from the same or different sessions, can simultaneously fetch data from the same cached result set.

For a result set to be cached, the OCIStmtExecute() or OCIStmtFetch2() calls that transparently create this cached result set must fetch rows until an ORA-01403 "No Data Found" error is returned. Subsequent OCIStmtExecute() or OCIStmtFetch2() calls that match a locally cached result set need not fetch to completion.

CREATE|ALTER TABLE [<schema>.]<table> ... [RESULT_CACHE (MODE {FORCE|DEFAULT})]

CREATE TABLE foo (a NUMBER, b VARCHAR2(20)) RESULT_CACHE (MODE FORCE); 

ALTER TABLE foo RESULT_CACHE (MODE DEFAULT);

TABLE_NAME    RESULT_CACHE
----------    ------------
   EMP           FORCE
   FOO           DEFAULT

ALTER TABLE foo RESULT_CACHE (MODE DEFAULT);

Client Cache Consistency

The client cache transparently keeps the result set consistent with any session state or database changes that can affect its cached result sets.

When a transaction modifies the data or metadata of any of the database objects used to construct that cached result, invalidation is sent to the OCI client on its subsequent round-trip to the server. If the OCI application does no database calls for a period of time, then the client cache lag setting forces the next OCIStmtExecute() call to make a database call to check for such invalidations.

The cached result sets relevant to database invalidations are immediately invalidated, and no subsequent OCIStmtExecute() calls can match such result sets. The OCI statement handles currently fetching from these cached result sets, at the time such invalidations are received, can continue fetching from this (invalidated) cached result set.

The next OCIStmtExecute() call by the process may cache the new result set if there is space available in the cache. The OCI client result cache periodically reclaims unused memory.

If a session has a transaction open, OCI ensures that its queries that reference database objects changed in this transaction go to the server instead of the client cache.

This consistency mechanism ensures that the OCI cache is always close to committed database changes. If the OCI application has relatively frequent calls involving database round-trips due to queries that cannot be cached, (such as DMLs, OCILob calls, and so on) then these calls transparently keep the client cache up-to-date with database changes.

Note that sometimes when a table is modified, a trigger can cause another table to be modified. OCI client result cache is sensitive to all such changes.

When the session state is altered, for example, if NLS session parameters are modified, this can cause the query results to be different. The OCI result cache is sensitive to such changes and on subsequent query executions, returns the correct query result set. The current cached result sets are kept (and not invalidated) for any other session in the process to match; otherwise, these result sets get "Ruled" after a while. There are new result sets cached corresponding to the new session state.

If the application must keep track of all such database and session changes it can be cumbersome and prone to errors. Hence, OCI result cache transparently keeps the result sets consistent with any database or session changes.

The OCI client result cache does not require thread support in the client.

Deployment Time Settings for Client Result Cache

The client result cache has server initialization parameters and client configuration parameters for its deployment time settings.

These are the server initialization parameters:

• CLIENT_RESULT_CACHE_SIZE

  The default value is zero, implying that the client cache feature is disabled. To enable the client result cache feature, set the size to 32768 bytes (32 Kilobytes (KB)) or greater. This is the minimum size of the client per-process result set cache. All OCI client processes get this minimum size. This can be overridden by the sqlnet.ora configuration parameter OCI_RESULT_CACHE_MAX_SIZE only if this feature is enabled on the server by the CLIENT_RESULT_CACHE_SIZE initialization parameter.

  You can view the current default maximum size by displaying the value of the CLIENT_RESULT_CACHE_SIZE parameter. To increase this maximum size, you can set CLIENT_RESULT_CACHE_SIZE. However, because CLIENT_RESULT_CACHE_SIZE is a static parameter, you must include the SCOPE = SPFILE clause if you use an ALTER SYSTEM statement, and you must restart the database before any changes to this parameter take effect.

  Note that if the client result cache feature is disabled at the server, the client configuration parameter OCI_RESULT_CACHE_MAX_SIZE is ignored and the client result cache cannot be enabled at the client.

  The cache size can be set to the minimum of:

  (available client memory) and

  ((the possible number of result sets to be cached)

  * (the average size of a row in a result set)

  * (the average number of rows in a result set)).

  Note:

  The client result cache has a maximum value of 2 GB; setting it higher causes a truncation to 2 GB.

  Do not set the CLIENT_RESULT_CACHE_SIZE parameter during database creation, because that can cause errors.

• CLIENT_RESULT_CACHE_LAG

  The CLIENT_RESULT_CACHE_LAG initialization parameter enables you to specify the maximum amount of time in milliseconds that the client result cache can lag behind any changes in the database that affect its result sets. The default is 3000 milliseconds.

  You can view the current lag by displaying the value of the CLIENT_RESULT_CACHE_LAG parameter. To change this value, you can set CLIENT_RESULT_CACHE_LAG. However, because CLIENT_RESULT_CACHE_LAG is a static parameter, you must include the SCOPE = SPFILE clause if you use an ALTER SYSTEM statement, and you must restart the database before any changes to this parameter take effect.

• Table annotation. Optional. One can annotate read-only, read-mostly tables for result caching during deployment. No application-level changes are required. Note SQL result cache hints, if specified, override the table annotations. See Oracle Database SQL Language Reference for more information.

• compatible

  Specifies the release with which Oracle Database must maintain compatibility. This parameter must be set to 11.0.0.0 or higher for the client result cache to be enabled. If you want client caching on views, compatible must be set to 11.2.0.0 or higher.

Client Cache Statistics

On existing round-trips from the OCI client, OCI periodically sends statistics related to its client cache to the server. These statistics are stored in the CLIENT_RESULT_CACHE_STATS$ view. Information such as the number of result sets successfully cached, number of cache hits, and number of cached result sets invalidated are stored here. The number of cache misses for queries is at least equal to the number of Create Counts in client result cache statistics. More precisely, the cache miss count equals the number of server executions as seen in server Automatic Workload Repository (AWR) reports.

Validation of the Client Result Cache

The following sections provide some more information about performing validations of the client result cache.

SELECT COUNT(*) FROM emp

OCI Client-Side Result Cache and Server Result Cache

The client-side result cache is a separate feature from the server result cache. The client-side result cache caches results of top-level SQL queries in OCI client memory, whereas the server result cache caches result sets in server SGA memory.

The server result cache may also cache query fragments. The client-side result caching can be enabled independently of the server result cache, though they both share the result cache SQL hints, table annotation, and session parameter RESULT_CACHE_MODE. See Oracle Database Concepts for more information about SQL query result cache. Table 10-3 shows the specific result cache association for client-site result cache or server result cache, or both, with regard to setting specific parameters, running particular PL/SQL packages, and querying specific Oracle database views.

Client Result Cache Demo Files

See the files cdemoqc.sql, cdemoqc.c, and cdemoqc2.c (all are in the demo directory for your operating system) for demonstration files for this feature.

Compatibility with Previous Releases

To use client result cache, applications must be relinked with Oracle Database Release 11.1 or later client libraries and be connected to an Oracle Database Release 11.1 or later database server. This feature is available to all OCI applications including JDBC Type II driver, OCCI, Pro*C/C++, and ODP.NET. The OCI drivers automatically pass the result cache hint to OCIStmtPrepare() and OCIStmtPrepare2() calls, thereby getting the benefits of caching.

ADR Base Location

The location of the ADR base is determined by OCI in the following order:

1. OCI first looks in the file sqlnet.ora (if it exists) for a statement such as (Linux or UNIX):

   ADR_BASE=/foo/adr
   

   adr (the name of a directory) must exist and be writable by all operating system users who execute OCI applications and want to share the same ADR base. foo stands for a path name. The location of sqlnet.ora is given in the directory $TNS_ADMIN (%TNS_ADMIN% on Windows). If there is no $TNS_ADMIN then the current directory is used. If ADR_BASE is set and one sqlnet.ora is shared by all users, then OCI stops searching when directory adr does not exist or is not writable by the user. If ADR_BASE is not set, then OCI continues the search, testing for the existence of certain directories.

   For example, if sqlnet.ora contains the entry ADR_BASE=/home/chuck/test then the ADR base is /home/chuck/test/oradiag_chuck and the ADR home could be something like /home/chuck/test/oradiag_chuck/diag/clients/user_chuck/host_4144260688_11.

2. $ORACLE_BASE (%ORACLE_BASE% on Windows) exists. In this case, the client subdirectory exists because it was created during installation of the database using Oracle Universal Installer.

   For example, if $ORACLE_BASE is /home/chuck/obase then the ADR base is /home/chuck/obase and the ADR home could be similar to /home/chuck/obase/diag/clients/user_chuck/host_4144260688_11.

3. $ORACLE_HOME (%ORACLE_BASE% on Windows) exists. In this case, the client subdirectory exists because it was created during installation of the database using Oracle Universal Installer.

   For example, if $ORACLE_HOME is /ade/chuck_l1/oracle then the ADR base is /ade/chuck_l1/oracle/log and the ADR home could be similar to /ade/chuck_l1/oracle/log/diag/clients/user_chuck/host_4144260688_11.

4. Operating system home directory: $HOME on Linux or UNIX, or %USERPROFILE% on Windows. On Linux or UNIX the location could be something like this for user chuck: /home/chuck/oradiag_chuck. On Windows, a folder named Oracle is created under C:\Documents and Settings\chuck.

   For example, in an Instant Client, if $HOME is /home/chuck then the ADR base is /home/chuck/oradiag_chuck and the ADR home could be /home/chuck/oradiag_chuck/diag/clients/user_chuck/host_4144260688_11.

   See Also:

   "OCI Instant Client"

5. On Windows, if the application is run as a service, the home directory option is skipped.

6. Temporary directory in the Linux or UNIX operating system: /var/tmp.

   For example, in an Instant Client, if $HOME is not writable, then the ADR base is /var/tmp/oradiag_chuck and the ADR home could be /var/tmp/oradiag_chuck/diag/clients/user_chuck/host_4144260688_11.

   Temporary directories in the Windows operating system, searched in this order:

   1. %TMP%

   2. %TEMP%

   3. %USERPROFILE%

   4. Windows system directory

If none of these directory choices are available and writable, or the ADR base is not created and there are no diagnostics.

Using ADRCI

ADRCI is a command-line tool that enables you to view diagnostic data within the ADR and to package incident and problem information into a zip file for Oracle Support to use. You can use ADRCI interactively and from a script. A problem is a critical error in OCI or the client. Each problem has a problem key. An incident is a single occurrence of a problem and is identified by a unique numeric incident ID. Each incident has a problem key that is a set of attributes: the ORA error number, error parameter values, and other information. Two incidents have the same root cause if their problem keys match.

What follows is a launch of ADRCI in a Linux system, a use of the HELP command for the SHOW BASE command, and then the use of the SHOW BASE command with the option -PRODUCT CLIENT, which is necessary for OCI applications. The ADRCI commands are case-insensitive. User input is shown in bold.

% adrci
 
ADRCI: Release 11.1.0.5.0 - Beta on Wed May 2 15:53:06 2007
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
adrci> help show base
 
  Usage: SHOW BASE [-product <product_name>]
 
  Purpose: Show the current ADR base setting.
 
  Options:
    [-product <product_name>]: This option allows users to show the
    given product's ADR Base location. The current registered products are
    "CLIENT" and "ADRCI".
 
  Examples: 
    show base -product client
    show base
 
adrci> show base -product client
ADR base is "/ade/chuck_l3/oracle/log/oradiag_chuck"

Next, the SET BASE command is described:

adrci> help set base
 
  Usage:  SET BASE <base_str>
 
  Purpose: Set the ADR base to use in the current ADRCI session.
           If there are valid ADR homes under the base, all homes will
           will be added to the current ADRCI session.
 
  Arguments:
    <base_str>: It is the ADR base directory, which is a system-dependent
    directory path string.
 
  Notes:
    On platforms that use "." to signify current working directory,
    it can be used as base_str.
 
  Example: 
    set base /net/sttttd1/scratch/someone/view_storage/someone_v1/log
    set base .
 
adrci> quit

When ADRCI is started, the default ADR base is for the rdbms server. $ORACLE_HOME is set to /ade/chuck_l3/oracle:

% adrci
 
ADRCI: Release 11.1.0.5.0 - Beta on Wed May 2 16:16:55 2007
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
ADR base = "/ade/chuck_l3/oracle/log"

For OCI application incidents you must check and set the base:

adrci> show base -product client
ADR base is "/ade/chuck_l3/oracle/log"
adrci> set base /ade/chuck_l3/oracle/log

For Instant Client there is no $ORACLE_HOME, so the default base is the user's home directory:

adrci> show base -product client
ADR base is "/home/chuck/oradiag_chuck"
adrci> set base /home/chuck/oradiag_chuck
adrci> show incidents
 
ADR Home = /home/chuck/oradiag_chuck/diag/clients/user_chuck/host_4144260688_11:
*************************************************************************
INCIDENT_ID    PROBLEM_KEY           CREATE_TIME
-------------------------------------------------------------------------
1                     oci 24550 [6]              2007-05-01 17:20:02.803697 -07:00                      
1 rows fetched
 
adrci>

Controlling ADR Creation and Disabling Fault Diagnosability Using sqlnet.ora

To disable diagnosability, turn off diagnostics by setting the following parameters in sqlnet.ora (the default is TRUE):

DIAG_ADR_ENABLED=FALSE
DIAG_DDE_ENABLED=FALSE

To turn off the OCI signal handler and reenable standard operating system failure processing, place the following parameter setting in sqlnet.ora:

DIAG_SIGHANDLER_ENABLED=FALSE

As noted previously, ADR_BASE is used in sqlnet.ora to set the location of the ADR base.

Oracle Database client contains advanced features for diagnosing issues, including the ability to dump diagnostic information when important errors are detected. By default, these dumps are restricted to a small subset of available information, to ensure that application data is not dumped. However, in many installations, secure locations for dump files may be configured, ensuring the privacy of such logs. In such cases, it is recommended to turn on full dumps; this can greatly speed resolution of issues. Full dumps can be enabled by adding the following line to the sqlnet.ora file used by your Oracle Database client installation:

DIAG_RESTRICTED=FALSE

To verify that diagnosability features are working correctly:

1. Upgrade your application to use the latest client libraries.

2. Start your application.

3. Check the file sqlnet.log in your application's TNS_ADMIN directory for error messages indicating that diagnosability could not be started (normally this is due to invalid directory names or permissions).