This chapter contains these topics:
Continuous Query Notification enables client applications to register queries with the database and receive notifications in response to DML or DDL changes on the objects or in response to result set changes associated with the queries. The notifications are published by the database when the DML or DDL transaction commits.
During registration, the application specifies a notification handler and associates a set of interesting queries with the notification handler. A notification handler can be either a server side PL/SQL procedure or a client side C callback. Registrations are created at either the object level or query level. If registration is at the object level, then whenever a transaction changes any of the registered objects and commits, the notification handler is invoked. If registration is at the query level, then whenever a transaction commits changes such that the result set of the query is modified, the notification handler is invoked, but if the changes do not affect the result set of the query, the notification handler will not be invoked.
See Also:
Oracle Database Advanced Application Developer's Guide, chapter 13, "Using Continuous Query Notification" for a complete discussion of the concepts of this feature.One use of this feature is in middle-tier applications that need to have cached data and keep the cache as recent as possible with respect to the back-end database.
The contents of the notification includes the following information:
Query IDs of queries whose result sets have changed. This is if the registration was at query granularity.
Names of the modified objects or changed rows.
Operation type (INSERT, UPDATE, DELETE, ALTER TABLE, DROP TABLE).
ROWIDs of the changed rows and the associated DML operation (INSERT, UPDATE, DELETE).
Global database events (STARTUP, SHUTDOWN). In a Real Application Cluster (Oracle RAC) the database delivers a notification when the first instance starts or the last instance shuts down.
See Also:
"Publish-Subscribe Notification in OCI"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 pseudo column can be omitted for OCI-based registrations, in which case the query ID is communicated back 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 which led to the query result set change. Each table descriptor is of the type OCI_DTYPE_TABLE_CHDES.
See Also:
"OCI_DTYPE_CQDES"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 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 the Oracle RAC generate notification.
Queries involving materialized views or non-materialized 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 to AQ, DBCHANGE.
The steps in writing the registration are:
The environment must be created in OCI_EVENTS and OCI_OBJECT mode.
The subscription handle attribute OCI_ATTR_SUBSCR_NAMESPACE must be set to namespace OCI_SUBSCR_NAMESPACE_DBCHANGE.
The subscription handle attribute OCI_ATTR_SUBSCR_CALLBACK is used 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".
You can optionally associate a client-specific context using OCI_ATTR_SUBSCR_CTX.
OCI_ATTR_SUBSCR_TIMEOUT can be set to specify a ub4 timeout interval in seconds. If not set, there is no timeout.
If OCI_SUBSCR_QOS_PURGE_ON_NTFN is set, the registration is purged on the first notification.
If OCI_SUBSCR_QOS_RELIABLE is set, notifications are persistent. Surviving instances of Oracle RAC can be used 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.
Call OCISubscriptionRegister() to create a new registration in the DBCHANGE namespace.
Multiple query statements can be associated 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"Optionally, to 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 needs to 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.
The subscription handle attributes for continuous query notification are described next. The attributes can be divided into generic (which are 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.
OCI_ATTR_SUBSCR_NAMESPACE (WRITE) - This must be set to OCI_SUBSCR_NAMESPACE_DBCHANGE for subscription handles.
OCI_ATTR_SUBSCR_CALLBACK (WRITE) - Use 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 which contains detailed information about the invalidation.
For setting OCI_ATTR_SUBSCR_QOSFLAGS, a generic flag, with values
#define OCI_SUBSCR_QOS_RELIABLE 0x01 /* reliable */ #define OCI_SUBSCR_QOS_PURGE_ON_NTFN 0x10 /* purge on first ntfn */
If OCI_SUBSCR_QOS_RELIABLE is set, then notifications are persistent. Therefore, surviving instances of Oracle RAC cluster can be used to send and retrieve invalidation messages, even after a node crash, because invalidations associated with this registration ID are queued persistently into the database. If 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.
If OCI_SUBSCR_QOS_PURGE_ON_NTFN bit is set, it means that the registration is purged on the first notification.
A parallel example is presented here:
OCI_ATTR_SUBSCR_CQ_QOSFLAGS. This attribute describes the continuous query notification-specific QOS flags (mode is WRITE, datatype is ub4) which are:
0x1 OCI_SUBSCR_CQ_QOS_QUERY - If set, it indicates that query level granularity is required. Notification should be only generated if the query result set changes. By default this level of QOS will have no false positives.
0x2 OCI_SUBSCR_CQ_QOS_BEST_EFFORT - If set, it indicates that best effort filtering is acceptable. Maybe 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 - This attribute can be used to specify a ub4 timeout value defined in seconds. If the timeout value is 0, or not specified, then the registration lives until explicitly unregistered.The rest of the attributes are namespace or feature-specific to the continuous query notification feature.
OCI_ATTR_CHNF_TABLENAMES (datatype is (OCIColl *)) attributes 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 is 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 - This is a ub4 flag that can be used to selectively filter notifications based on operation type. This option is ignored if the registration is of query level granularity. Flags stored are:
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 - This is a ub4 value that can be used by the client to specify the number of transactions by which the client is willing to lag behind. This option can be used by the client as a throttling mechanism for continuous query notification messages. When this option is chosen, 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 above attributes can be subsequently 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
See Also:
"Publish-Subscribe Register Directly to the Database" for more details about these attributes.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().
See Also:
"OCI_ATTR_CQ_QUERYID"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.
This is the top-level continuous query notification descriptor type.
OCI_ATTR_CHDES_DBNAME (oratext *) - Name of the database (source of the continuous query notification).
OCI_ATTR_CHDES_XID (RAW(8)) - Message id of the message.
OCI_ATTR_CHDES_NFYTYPE - Flags describing the notification type:
0x0 OCI_EVENT_NONE - No further information about the continuous query notification.
0x1 OCI_EVENT_STARTUP - Instance startup.
0x2 OCI_EVENT_SHUTDOWN - Instance shutdown.
0x3 OCI_EVENT_SHUTDOWN_ANY - Any instance shutdown - Real Application Clusters (Oracle RAC).
ox5 OCI_EVENT_DEREG - unregistered or timed out.
0x6 OCI_EVENT_OBJCHANGE - Object change notification.
0x7 OCI_EVENT_QUERYCHANGE - Query change notification.
OCI_ATTR_CHDES_TABLE_CHANGES - A collection type describing operations on tables of datatype (OCIColl *). This attribute is only present if the OCI_ATTR_CHDES_NFTYPE attribute was of type OCI_EVENT_OBJCHANGE, else it is NULL. Each element of the collection is a table of continuous query descriptors of type OCI_DTYPE_TABLE_CHDES:
OCI_ATTR_CHDES_QUERIES: A collection type describing the queries which were invalidated. Each member of the collection is of type OCI_DTYPE_CQDES. This attribute is only present if the attribute OCI_ATTR_CHDES_NFTYPE was OCI_EVENT_QUERYCHANGE, else it is NULL.
This notification descriptor describes a query which was invalidated, usually in response to the commit of a DML or a DDL transaction. It has the following attributes:
OCI_ATTR_CQDES_OPERATION (ub4, READ) - Operation which occurred on the query. It can be one of the two values:
OCI_EVENT_QUERYCHANGE - Query result set change.
OCI_EVENT_DEREG - Query unregistered.
OCI_ATTR_CQDES_TABLE_CHANGES (OCIColl *, READ) - A collection of table continuous query descriptors describing DML or DDL operations on tables which caused the query result set change. Each element of the collection is of type OCI_DTYPE_TABLE_CHDES.
OCI_ATTR_CQDES_QUERYID (ub8, READ) - Query ID of the query which was invalidated.
This notification descriptor conveys information about changes to a table involved in a registered query.
OCI_ATTR_CHDES_TABLE_NAME (oratext *) - Schema annotated table name.
OCI_ATTR_CHDES_TABLE_OPFLAGS (ub4) - Flag field describing the operations on the table. Each of the following flag fields is in a separate bit position in the attribute:
0x1 OCI_OPCODE_ALLROWS - The table is completely invalidated.
0x2 OCI_OPCODE_INSERT - Insert operations on the table.
0x4 OCI_OPCODE_UPDATE - Update operations on the table.
0x8 OCI_OPCODE_DELETE - Delete operations on the table.
0x10 OCI_OPCODE_ALTER - Table altered (schema change). This includes DDL statements and internal operations that cause row migration.
0x20 OCI_OPCODE_DROP - Table dropped.
OCI_ATTR_CHDES_TABLE_ROW_CHANGES - This is an embedded collection describing the changes to the rows within the table. Each element of the collection is a row continuous query descriptor of type OCI_DTYPE_ROW_CHDES which has the following attributes:
The following is a simple OCI program, demoquery.c. See the comments in the listing. The calling session must already have the CHANGE NOTIFICATION system privilege and SELECT privileges on all objects that it attempts to register.
/* Copyright (c) 2006, 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 has to be initialized
with OCI_EVENTS and OCI_OBJECTS 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 *) ¬ify_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);
}
The OCI functions, OCIDBStartup() and OCIDBShutdown(), provide the minimal interface needed to start up and shut down an Oracle database. Before calling OCIDBStartup(), the C program must connect to the server and start a SYSDBA or SYSOPER session in the preliminary authentication mode. This mode is the only one permitted when the instance is not up and it is used only to bring up the instance. A call to OCIDBStartup() starts up one server instance without mounting or opening the database. To mount and open the database, end the preliminary authentication session and start a regular SYSDBA or SYSOPER session to execute the appropriate ALTER DATABASE statements.
An active SYSDBA or SYSOPER session is needed to shut down the database. For all modes other than OCI_DBSHUTDOWN_ABORT, make two calls to OCIDBShutdown(): one to initiate shutdown by prohibiting further connections to the database, followed by the appropriate ALTER DATABASE commands to dismount and close it; and the other call to finish shutdown by bringing the instance down. In special circumstances, to shut down the database as fast as possible, just call OCIDBShutdown() in the OCI_DBSHUTDOWN_ABORT mode, which is equivalent to SHUTDOWN ABORT in SQL*Plus.
Both of these functions require a dedicated connection to the server. ORA-106 is signaled if an attempt is made to start up or shut down the database when connected to a shared server through a dispatcher.
The OCIAdmin administration handle datatype is used to make the interface extensible. OCIAdmin is associated with the handle type OCI_HTYPE_ADMIN. Passing a value for the OCIAdmin parameter, admhp, is optional for OCIDBStartup() and is not needed by OCIDBShutdown().
See Also:
To do 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 up one instance on the server.
The following sample code uses a client-side parameter file (pfile) that is set in the administration handle:
...
/* 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));
/* Startup 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 do 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, the following procedure should be followed:
Call OCIDBShutdown() in OCI_DEFAULT, OCI_DBSHUTDOWN_TRANSACTIONAL, OCI_DBSHUTDOWN_TRANSACTIONAL_LOCAL, or OCI_DBSHUTDOWN_IMMEDIATE mode to prohibit further connects.
Use the necessary ALTER DATABASE commands to close and dismount the database.
Call OCIDBShutdown() in OCI_DBSHUTDOWN_FINAL mode to shut down the instance.
/* 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));
...
The next shutdown example 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_SHUTDOWN_ABORT));
/* End the sysdba session */
checkerr(errhp, OCISessionEnd(svchp, errhp, usrhp, (ub4)OCI_DEFAULT));
...
ROWID is a globally unique identifier for a row in a database. It is created at the time the row is inserted into the table, and destroyed when it is removed. ROWID values have several important uses. They are unique identifiers for rows in a table. They are the fastest way to access a single row and can show how the rows in the table are stored.
Implicit fetching of ROWIDs in SELECT ... FOR UPDATE statements means that the ROWID is retrieved at the client side, even if it is not one of the columns named in the select statement. The position parameter of OCIDefineByPos() is set to zero (0). These host variables can be specified for retrieving the ROWID pseudocolumn values:
SQLT_CHR (VARCHAR2)
SQLT_VCS (VARCHAR)
SQLT_STR (NULL-terminated string)
SQLT_LVC (LONG VARCHAR)
SLQT_AFC (CHAR)
SQLT_AVC (CHARZ)
SQLT_VST (OCI String)
SQLT_RDD (ROWID descriptor)
The SELECT ... FOR UPDATE statement identifies the rows that will be updated and then locks each row in the result set. This is useful when you want to base an update on the existing values in a row. In that case, you must make sure that another user does not change the row.
Note that when specifying character buffers for storing the values of the ROWIDs (for example, if getting it in SQLT_STR format), allocate enough memory for storing ROWIDs. Distinction should be made between ROWID datatype and UROWID datatype. ROWID datatype can only store physical ROWIDs, but UROWID is the type that can store logical ROWIDs (identifiers for the rows of Index-Organized Tables) as well. The maximum internal length for the ROWID type is 10 Bytes, but is 3950 bytes for the UROWID datatype.
Dynamic define is equivalent to calling OCIDefineByPos() with mode set as OCI_DYNAMIC_FETCH. Dynamic defines enable us to set up additional attributes for a particular define handle. It specifies a callback function, which is invoked at runtime to get a pointer to the buffer into which the fetched data or a piece of it will be retrieved.
The attribute OCI_ATTR_FETCH_ROWID must be set on the statement handle before implicit fetching of ROWIDs can be used, in this way:
OCIAttrSet(stmthp, OCI_HTYPE_STMT, 0, 0 , OCI_ATTR_FETCH_ROWID, errhp);
Dynamic define is not compatible with implicit fetching of ROWIDs. In normal scenarios this mode allows the application to provide buffers for a column, for each row; that is, a callback is invoked every time a column value is fetched.
This feature, using OCIDefineByPos() for position 0, is for fetching an array of data at one time into the user buffers and getting their respective ROWIDs at the same time. It allows for fetching of ROWIDs in the case of SELECT....FOR UPDATE statements even when ROWID is not one of the columns in the SELECT query. When fetching the data one by one into the user buffers, the existing attribute OCI_ATTR_ROWID can be used.
If you use this feature to fetch the ROWIDs, the attribute OCI_ATTR_ROWID on the statement handle cannot be used simultaneously to get the ROWIDs. Only one of them can be used at a time for a particular statement handle.
See Also:
"OCI_ATTR_FETCH_ROWID"Use this fragment of a C program 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, getting 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;
}
OCI applications can use client memory to take advantage of the OCI result cache to improve response times of repetitive queries.
This feature enables client-side caching of SQL query result sets in client memory. The OCI result cache is completely transparent to OCI applications, and its cache of result set data is kept consistent with any session or database changes that affect its result set.
Applications employing this feature see improved performance for queries that have a cache hit. OCI can transparently use cached results for future executions of these queries. Because retrieving results locally from an OCI client process is faster than making a database call and rerunning a query, frequently run queries experience a significant performance improvement when their results are cached.
The OCI cache also reduces the server CPU that would have been consumed for processing the query, thereby improving server scalability. OCI statements from multiple sessions can match the same cached result set in the OCI process memory, if they have similar schema, SQL text, bind values, and session settings, or else the query execution will be on the server.
You must enable OCI statement caching or cache statements at the application level when using the client result cache.
See Also:
"Statement Caching in OCI"The benefits of OCI client query result cache are:
Since the result cache is on the client-side, a cache hit causes OCIStmtExecute() and OCIStmtFetch() calls to be processed locally, instead of 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 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 cheaper than server memory.
A local cache on the client will have better locality of reference for queries executed by that client.
You need to annotate a query with a /*+ result_cache */ hint to indicate that results are to be stored in the query result cache. It is recommended that applications annotate queries with result cache hints for read-only or read-mostly database objects. If the result cache hint is for queries whose results are large, these results can use a large amount of client result 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.
You can set the RESULT_CACHE_MODE initialization parameter to control whether the SQL query client result cache is used for all queries (when possible), or only for queries that are annotated with the result cache hint.
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 will always go 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 applications to 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(), OCIStmtFetch(), or OCIStmtFetch2() calls that transparently create this cached result set must fetch rows until it gets an ORA-1403 "No Data Found" error. Subsequent OCIStmtExecute() or OCIStmtFetch() calls that match a locally cached result set need not fetch to completion.
Unless the RESULT_CACHE_MODE server initialization parameter is set to FORCE, it is necessary to explicitly specify the queries to be cached via SQL hints. The SQL /*+ result_cache */ or /*+ no_result_cache */ hint needs to be set in SQL text passed to OCIStmtPrepare(), and OCIStmtPrepare2() calls.
See Also:
Oracle Database SQL Language Reference, for more information about these SQL hints
Oracle Database Reference for more information about RESULT_CACHE_MODE
To use this feature, applications must be re-linked with release 11.1 or higher client libraries and be connected to a release 11.1 or higher 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 will automatically pass the result cache hint to OCIStmtPrepare(), and OCIStmtPrepare2() calls, thereby getting the benefits of caching.
There are queries that are not cached on the OCI client even if the result cache hint is specified. Such queries may be cached on the database if the server result cache feature is enabled (see Oracle Database Concepts, "SQL Query Result Cache" for more information). If a SQL query includes any of the following, then the result set of that query is not cached in the OCI client result cache:
Views
Remote objects
Complex types in the select list
Snapshot-based or flashback queries
Queries executed in a serializable, read-only transaction, or inside a flashback session
Queries that have PL/SQL functions in them
Queries that have VPD policies enabled on the tables
Note:
OCI Client Result Cache is not supported in Release 11.1 with database resident connection pooling (DRCP).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 will be 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 will force the next OCIStmtExecute() call to make a database call to check for such invalidations.
The cached result sets relevant to database invalidations will be immediately invalidated and no subsequent OCIStmtExecute() calls will 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 un-used 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 will always be 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 will transparently keep the client cache up-to-date with database changes.
The OCI client result cache does not require thread support in the client.
The client result cache has server initialization parameters and client configuration parameters, for its deployment time settings.
There are two 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 (32KBytes) 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 means of 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. If you want 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 hence 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)).
CLIENT_RESULT_CACHE_LAG
The CLIENT_RESULT_CACHE_LAG initialization parameter enables you to specify the maximum amount of time in milliseconds that 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. If you want 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.
A client configuration file is optional and overrides the cache parameters set in the server init.ora initialization file. These parameters are part of a sqlnet.ora file. The following optional parameters are available for client configuration:
OCI_RESULT_CACHE_MAX_SIZE (optional) - Maximum size in bytes for the per-process query cache. Specifying a size less than 32768 in the client sqlnet.ora file will disable the client result cache feature for client processes reading this sqlnet.ora file.
OCI_RESULT_CACHE_MAX_RSET_SIZE (optional) - Maximum size of any result set in bytes in the per-process query cache
OCI_RESULT_CACHE_MAX_RSET_ROWS (optional) - Maximum size of any result set in rows in the per-process query cache
Note that the cache lag cannot be set on the client.
OCI will periodically send, on existing round trips from the OCI client, statistics related to its client cache to the server and they will be stored in CLIENT_RESULT_CACHE_STATS$. Information such as the number of result sets successfully cached, number of cache hits, and number of cached result sets invalidated will be stored here. The number of cache misses for queries is at least equal to the number of Create Counts in client result cache statistics. For more precise cache miss count, it equals the number of server executions as seen in server Automatic Workload Repository (AWR) reports.
The client-side result cache is a separate feature from the server result cache. The client result cache caches results of top-level SQL queries in OCI client memory, while the server result cache caches result sets in server SGA memory.
The server result cache may also cache query fragments. The client result caching can be enabled independently of the server result cache, though they both share the SQL result cache hints and some of the parameter settings. (See Oracle Database Concepts "SQL Query Result Cache" for details).
Table 10-1 Setting Client and Server Result Caches
| Parameters, PL/SQL Package, and Database Views | Result Cache Association |
|---|---|
|
client_result_cache_* parameters client_result_cache_size, client_result_cache_lag |
client result cache |
|
SQL hints /*+ result_cache */, /*+ no_result_cache */ |
client result cache, server result cache |
|
sqlnet.ora OCI_RESULT_CACHE* parameters: OCI_RESULT_CACHE_MAX_SIZE OCI_RESULT_CACHE_MAX_RSET_SIZE OCI_RESULT_CACHE_MAX_RSET_ROWS |
client result cache |
|
Statistics view: client_result_cache_stats$ |
client result cache |
|
result_cache_mode parameter |
client result cache, server result cache |
|
All other result_cache* parameters, for example, result_cache_max_size |
server result cache |
|
Package DBMS_RESULT_CACHE |
server result cache |
|
Statistics views v$result_cache_*, gv$result_cache_*. For example, v$result_cache_statistics, gv$result_cache_memory |
server result cache |
Fault Diagnosability was introduced into OCI in 11g Release 1 (11.1). An incident (an occurrence of a problem) on the OCI client is captured without user intervention in the form of diagnostic data: dump files or core dump files. The diagnostic data is stored in an Automatic Diagnostic Repository (ADR) subdirectory created for the incident. For example, if a Linux or UNIX application fails with a null pointer reference then the core file is written in the ADR home directory (if it exists) instead of the operating system directory. The ADR subdirectory structure and a utility to deal with the output, ADR Command Interpreter (ADRCI), will be discussed in the following sections.
An ADR home is the root directory for all diagnostic data for an instance of a particular product such as OCI and a particular operating system user. ADR homes are grouped under the same root directory, the ADR base.
Fault diagnosability and the ADR structure for the Oracle Database are described in detail in the following documentation:
See Also:
Oracle Database Administrator's Guide, "Managing Diagnostic Data"The location of the ADR base is determined by OCI in the following order:
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 already 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
$ORACLE_BASE (%ORACLE_BASE% on Windows) exists. In this case, the client subdirectory exists because it was created during installation of the database using the Oracle Universal Installer.
For example, if $ORACLE_BASE is /home/chuck/obase then ADR base is /home/chuck/obase and ADR home could be similar to /home/chuck/obase/diag/clients/user_chuck/host_4144260688_11
$ORACLE_HOME (%ORACLE_BASE% on Windows) exists. In this case, the client subdirectory exists because it was created during installation of the database using the Oracle Universal Installer.
For example, if $ORACLE_HOME is /ade/chuck_l1/oracle then ADR base is /ade/chuck_l1/oracle/log and ADR home could be similar to /ade/chuck_l1/oracle/log/diag/clients/user_chuck/host_4144260688_11
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 ADR base is /home/chuck/oradiag_chuck and ADR home could be /home/chuck/oradiag_chuck/diag/clients/user_chuck/host_4144260688_11
See Also:
"OCI Instant Client"On Windows, if the application is run as a service, the home directory option will be skipped.
Temporary directory in the Linux or UNIX operating system: /var/tmp.
For example, in an Instant Client, if $HOME is not writable, then ADR base is /var/tmp/oradiag_chuck and 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:
%TMP%
%TEMP%
%USERPROFILE%
Windows system directory
If none of these directory choices are available and writable, ADR will not be created and there are no diagnostics.
See Also:
Oracle Database Net Services ReferenceADRCI 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. ADRCI can be used 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 which 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.
See Also:
Oracle Database Utilities for an introduction to the ADRCIHere is a launch of ADRCI in a Linux system, then 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, then 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>
See Also:
"OCI Instant Client"To remove diagnosability, diagnostics can be turned off 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 re-enable 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.
See Also:
Oracle Database Net Services Administrator's Guide for more information about the structure of the Automatic Diagnostic Repository