You can use OCI to access Oracle TimesTen In-Memory Database and Oracle TimesTen Application-Tier Database Cache. See Oracle TimesTen In-Memory Database C Developer's Guide, for information about Times Ten support for Oracle Call Interface.
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 the 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 is not invoked.
See Also:
Oracle Database Advanced Application Developer's Guide, "Using Continuous Query Notification" for a complete discussion of the concepts of this featureOne use of continuous query notification is in middle-tier applications that must have cached data and keep the cache as recent as possible for the back-end database.
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 Oracle 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 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.
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 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:
Create the environment in OCI_EVENTS and OCI_OBJECT mode.
Set the subscription handle attribute OCI_ATTR_SUBSCR_NAMESPACE to namespace OCI_SUBSCR_NAMESPACE_DBCHANGE.
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".
Optionally associate a client-specific context using OCI_ATTR_SUBSCR_CTX attribute.
Set the OCI_ATTR_SUBSCR_TIMEOUT attribute to specify a ub4 timeout interval in seconds. If it is not set, there is no timeout.
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.
Call OCISubscriptionRegister() to create a new registration in the DBCHANGE namespace.
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"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.
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
See Also:
"Publish-Subscribe Register Directly to the Database" for more details about these attributesThe 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 - Oracle Real Application Clusters (Oracle RAC)
0x5 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 data type (OCIColl *). This attribute is present only if the OCI_ATTR_CHDES_NFTYPE attribute was of type OCI_EVENT_OBJCHANGE; otherwise, 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 that were invalidated. Each member of the collection is of type OCI_DTYPE_CQDES. This attribute is present only if the attribute OCI_ATTR_CHDES_NFTYPE was of type OCI_EVENT_QUERYCHANGE; otherwise, it is NULL.
This notification descriptor describes a query that 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 that occurred on the query. It can be one of these 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 that 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 that was invalidated.
This notification descriptor conveys information about changes to a table involved in a registered query. It has the following attributes:
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 that has the following attributes:
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.
Example 10-1 Program Listing That Demonstrates Continuous Query Notification
/* 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 *) ¬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 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 start the instance. A call to OCIDBStartup() starts 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, 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 or shut down the database when it is connected to a shared server through a dispatcher.
The OCIAdmin administration handle C data type 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 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 10-2 Calling OCIDBStartup() to Perform 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:
Call OCIDBShutdown() in OCI_DEFAULT, OCI_DBSHUTDOWN_TRANSACTIONAL, OCI_DBSHUTDOWN_TRANSACTIONAL_LOCAL, or OCI_DBSHUTDOWN_IMMEDIATE mode to prohibit further connections.
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 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 10-3 Calling OCIDBShutdown() in OCI_DBSHUTDOWN_FINAL Mode
/* 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 10-4 Calling OCIDBShutdown() in 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));
...
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)
SQLT_AFC (CHAR)
SQLT_AVC (CHARZ)
SQLT_VST (OCI String)
SQLT_RDD (ROWID descriptor)
The SELECT ... FOR UPDATE statement identifies the rows that are to 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 ensure that another user does not change the row.
When you specify character buffers for storing the values of the ROWIDs (for example, if getting it in SQLT_STR format), allocate enough memory for storing ROWIDs. Remember the differences between the ROWID data type and the UROWID data type. The ROWID data type can only store physical ROWIDs, but UROWID 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; it is 3950 bytes for the UROWID data type.
Dynamic define is equivalent to calling OCIDefineByPos() with mode set as OCI_DYNAMIC_FETCH. Dynamic defines enable you to set up additional attributes for a particular define handle. It specifies a callback function that is invoked at runtime to get a pointer to the buffer into which the fetched data or a piece of it is to be retrieved.
The attribute OCI_ATTR_FETCH_ROWID must be set on the statement handle before you can use implicit fetching of ROWIDs, 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 simultaneously into the user buffers and getting their respective ROWIDs at the same time. It allows for fetching of ROWIDs with 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, you can use the existing attribute OCI_ATTR_ROWID.
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. You can only use one of them at a time for a particular statement handle.
See Also:
"OCI_ATTR_FETCH_ROWID"Use the fragment of a C program in Example 10-5 to build upon.
Example 10-5 Implicit Fetching of ROWIDs
#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;
}
OCI applications can use client memory to take advantage of the OCI result cache to improve response times of repetitive queries.
The client result cache 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. Otherwise, with any dissimilarity, the query execution is directed to the server.
You must enable OCI statement caching or cache statements at the application level when using the client result cache.
Client result cache works with OCI features such as OCI session pooling, OCI connection pooling, database resident connection pooling, and OCI transparent application failover (TAF).
See Also:
"Statement Caching in OCI"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.
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.
Unless the RESULT_CACHE_MODE server initialization parameter is set to FORCE, you must explicitly specify the queries to be cached using SQL hints. The SQL /*+ result_cache */ or /*+ no_result_cache */ hint must be set in SQL text passed to OCIStmtPrepare() and OCIStmtPrepare2() calls.
The ALTER TABLE and CREATE TABLE statements enable you to annotate tables with result cache mode. There are also session parameters as mentioned in a later section. The matrix of table annotations and session parameters dictates the effective result cache mode for queries on that table. Note that SQL hints override table annotations and session parameters. The syntax is:
CREATE|ALTER TABLE [<schema>.]<table> ... [RESULT_CACHE (MODE {FORCE|DEFAULT})]
Here is an example of CREATE TABLE. It defines the table columns:
CREATE TABLE foo (a NUMBER, b VARCHAR2(20)) RESULT_CACHE (MODE FORCE);
Here is an example of ALTER TABLE:
ALTER TABLE foo RESULT_CACHE (MODE DEFAULT);
This ALTER TABLE statement is used to annotate tables so that results of statements or query blocks (for server result cache) using these tables are stored in the result cache. If a given query has a SQL hint /*+ result_cache / or /*+ no_result_cache */ or if the parameter RESULT_CACHE_MODE is set to FORCE, then the hint or session variable take precedence over the table annotation.
You should annotate all tables you want stored in the result cache. Then all SQL queries, whether single table selects or with joins, for these tables with cache hints, are considered for caching assuming they are cache-worthy.
See Also:
Oracle Database SQL Language Reference for more information about RESULT_CACHE clause, SQL hints, ALTER TABLE, and CREATE TABLE
Oracle Database Reference for more information about RESULT_CACHE_MODE
Table 10-1 summarizes the result cache annotation mode values.
Table 10-1 DDL Table Result Cache Annotation Modes
| Mode Value | Description |
|---|---|
|
|
The default value. Result caching is not determined at the table level. You can use this value to clear any table annotations. |
|
|
If all table names in the query have this setting, then the query is always considered for caching unless the |
The RESULT_CACHE column in the DBA views DBA_TABLES, USER_TABLES, and ALL_TABLES shows the result cache mode annotation for the table. If the table has not been annotated, it shows DEFAULT.
Suppose that table emp is annotated as ALTER TABLE emp RESULT_CACHE (MODE FORCE).
Then execute the following query in the session:
SELECT table_name, result_cache FROM user_tables
The output is as follows:
TABLE_NAME RESULT_CACHE ---------- ------------ EMP FORCE FOO DEFAULT
The output shows that table FOO either has not been annotated or has been annotated using the following statement:
ALTER TABLE foo RESULT_CACHE (MODE DEFAULT);
See Also:
Oracle Database Reference for more information about theRESULT_CACHE column on these DBA viewsThe RESULT_CACHE_MODE parameter enables you to decide result cache mode across tables in your queries. Use this clause in ALTER SESSION and ALTER SYSTEM statements, or inside the server parameter file (init.ora) to determine result caching.
The SQL query level result cache hints take precedence over the session parameter RESULT_CACHE_MODE and result cache table annotations. In addition, table annotation FORCE takes precedence over the session parameter MANUAL as indicated in Table 10-2. Table 10-2 compares modes (MANUAL and FORCE) for the session parameter RESULT_CACHE_MODE versus the comparable table annotation modes and shows the effective result cache mode.
Table 10-2 Effective Result Cache Table Mode
| RESULT_CACHE_MODE Parameter | MANUAL | FORCE |
|---|---|---|
|
Table Annotation = |
|
|
|
Table Annotation = |
|
|
Note that when the effective mode is FORCE, then the actual caching depends on internal restrictions for client and server cache, query cache worthiness (for example, there is no SYSDATE in the query), and space available in the cache. This is similar to the SQL query hint /*+ result_cache */ because it is just a hint. It does not imply that the query is actually cached. Recall that table annotation DEFAULT indicates that result caching is not determined at the table level and session parameter mode MANUAL indicates that the query must be annotated with a SQL hint for the hint to take precedence, so in effect these are equivalent methods for this setting.
The following are some use cases that show when SQL hints take precedence over table annotations and session parameter.
If the emp table is annotated as ALTER TABLE emp RESULT_CACHE (MODE FORCE) and the session parameter is not set, (it has its default value of MANUAL), this implies queries on table emp are considered for query caching.
If in an example, the SQL query is SELECT /*+ no_result_cache */ empno FROM emp, the query is not cached. This is because SQL hints take precedence over table annotations and session parameter.
If the emp table is not annotated or is annotated as ALTER TABLE emp RESULT_CACHE (MODE DEFAULT) and the session parameter is not set (it has a default value of MANUAL), this implies queries are not cached.
If in an example, the SQL query has the hint SELECT /*+ result_cache */ * FROM emp, then this query is considered for query caching.
If there is no table annotation and there is no SQL query hint, but the session or system parameter is set to FORCE, all queries on all tables are considered for query caching.
See Also:
Oracle Database SQL Language Reference for more about cachingThere 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 the discussion of the SQL query result cache in Oracle Database Concepts 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:
Remote objects
Complex types in the select list
Snapshot-based queries 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 virtual private database (VPD) policies enabled on the tables
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.
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)).
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.
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 disables 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.
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.
See Also:
Oracle Database Reference for information about the CLIENT_RESULT_CACHE_STAT$ view
Oracle Database Performance Tuning Guide to find the client process IDs and session IDs for the sessions doing client caching
The following sections provide some more information about performing validations of the client result cache.
First, to determine the performance gain of adding result cache hints to the queries, measure the time taken to run the queries without the /*+ result_cache */ hints. Then add the /*+ result_cache */ hints to the query and measure the time again. The difference in time is your performance gain.
Query the v$mystat view. To query this view, you must be granted permissions. Perform these two queries
Query-1: Measures the "SQL*Net round-trips to and from the client" from v$mystat.
Query-2: Measures the "SQL*Net round-trips to and from the client" without the SQL result cache hint.
The difference between Query-2 and Query-1 queries is the number of round-trips that it usually takes without enabling client result cache.
Note that the Query-1 query itself would make some round-trips (approximately 2) in this calculation.
If you add a result cache hint to the query or add the FORCE table annotation to the query for table emp and perform the query again, the difference between Query-2 and Query-1 is much less.
Query the v$sqlarea view. To query this view, you must be granted permissions.
Run the following SQL statement:
SELECT COUNT(*) FROM emp
Reexecute this preceding SQL statement a few times.
Then query select executions, fetches, parse_calls from v$sqlarea where sql_text like '% from emp';
Next, add the result cache table hint for emp to the query.
Reexecute the query a few times.
With client caching, the values for column1, column2 are less.
Note that the preceding validations can also be performed with result cache table annotations.
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.
Table 10-3 Setting Client-Side Result Cache and Server Result Cache
| 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 |
|
create table annotation |
client result cache, server result cache |
|
alter table annotation |
client result cache, server result cache |
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.
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.
Fault diagnosability was introduced into OCI in Oracle Database 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), are 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 Oracle Database are described in detail in the discussion of managing diagnostic data in Oracle Database Administrator's Guide.
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 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 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.
$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.
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"On Windows, if the application is run as a service, the home directory option is skipped.
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:
%TMP%
%TEMP%
%USERPROFILE%
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.
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. 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.
See Also:
Oracle Database Utilities for an introduction to ADRCIWhat 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>
See Also:
"OCI Instant Client"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:
Upgrade your application to use the latest client libraries.
Start your application.
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).
See Also:
Oracle Database Net Services Reference for the ADR parameter settings in sqlnet.ora
Oracle Database Net Services Administrator's Guide for more information about the structure of ADR
In Oracle Database Release 11.2 (or later) you can use different versions of the time zone file on the client and server; this mode of operation was not supported before Oracle database Release 11.2. Both client and server must be 11.2 or later to operate in such a mixed mode. This section discusses the ramifications of operating in such a mode. To avoid these ramifications use the same time zone file version for client and server.
The following behavior is seen when the client and server use different time zones file versions. Note that the use of different time zone file versions only affects the handling of TIMESTAMP WITH TIMEZONE (TSTZ) data type values.
The OCI Datetime and Interval APIs listed here unconditionally raise an error when the input parameters are of TSTZ type. This is because these operations depend on the local time zone file on the client that is not synchronized with the database. Continuing with the computation in such a configuration can result in inconsistent computations across the client and database tiers.
OCIDateTimeCompare() OCIDateTimeConstruct() OCIDateTimeConvert() OCIDateTimeSubtract() OCIIntervalAdd() OCIIntervalSubtract() OCIIntervalFromTZ() OCIDateTimeGetTimeZoneName() OCIDateTimeGetTimeZoneOffset()Foot 1 OCIDateTimeSysTimeStamp()
There is a performance penalty when you retrieve or modify TSTZ values. The performance penalty arises because of the additional conversions needed to compensate for the client and server using different time zone file versions.
If new time zone regions are defined by the more recent time zone file, you can see an error operating on a TIMESTAMP WITH TIMEZONE value belonging to the new region on a node that has a time zone file version that does not recognize the new time zone region.
Applications that manipulate opaque type or XMLType instances or both containing TSTZ type attributes must use the same time zone file version on client and server to avoid data loss.
See Also:
Oracle Database Globalization Support Guide for information about upgrading the time zone file and timestamp with time zone dataFootnote Legend
Footnote 1: Returns anORA-01805 error when timezone files on the client and server do not match (regions are not synchronized); returns OCI_SUCCESS when region time zone values are the same (represent the same instant in UTC), though the TIME ZONE offsets are different.