9 OCI Programming Advanced Topics

Advantages of OCI Thread Safety

The implementation of thread safety in OCI has the following advantages:

• Multiple threads of execution can make OCI calls with the same result as successive calls made by a single thread.

• When multiple threads make OCI calls, there are no side effects between threads.

• Users who do not write multithreaded programs do not pay a performance penalty for using thread-safe OCI calls.

• Use of multiple threads can improve program performance. Gains may be seen on multiprocessor systems where threads run concurrently on separate processors, and on single processor systems where overlap can occur between slower operations and faster operations.

OCI Thread Safety and Three-Tier Architectures

In addition to client/server applications, where the client can be a multithreaded program, a typical use of multithreaded applications is in three-tier, or client-agent-server, architectures. In this architecture the client is concerned only with presentation services. The agent, or application server, processes the application logic for the client application. Typically, this relationship is a many-to-one relationship, with multiple clients sharing the same application server.

The server tier in this scenario is a database. The applications server, or agent, is very well suited to being a multithreaded application server, with each thread serving a single client application. In an Oracle environment this application server is an OCI or precompiler program.

Implementing Thread Safety

In order to take advantage of thread safety, an application must be running on a thread-safe operating system. The application specifies that it is running in a multithreaded environment by making an OCIEnvNlsCreate() call with OCI_THREADED as the value of the mode parameter.

All subsequent calls to OCIEnvNlsCreate() must also be made with OCI_THREADED.

If an application is single-threaded, whether or not the operating system is thread-safe, the application must pass a value of OCI_DEFAULT to OCIInitialize() or OCIEnvNlsCreate(). Single-threaded applications that run in OCI_THREADED mode may incur lower performance.

If a multithreaded application is running on a thread-safe operating system, the OCI library will manage mutexes for the application for each environment handle. An application can override this feature and maintain its own mutex scheme by specifying a value of OCI_NO_MUTEX in the mode parameter of the OCIEnvCreate() call.

The following scenarios are possible, depending on how many connections exist in each environment handle, and how many threads are spawned in each connection.

1. If an application has multiple environment handles, with a single thread in each, mutexes are not required.

2. If an application running in OCI_THREADED mode maintains one or more environment handles, with multiple connections, it has these options:

   • Pass a value of OCI_NO_MUTEX for the mode of OCIEnvNlsCreate(). The application must mutex OCI calls made on the same environment handle. This has the advantage that the mutex scheme can be optimized to the application design. The programmer must also insure that only one OCI call is in process on the environment handle connection at any given time.

   • Pass a value of OCI_DEFAULT for the mode of OCIEnvNlsCreate(). The OCI library automatically gets a mutex on every OCI call on the same environment handle.

     Note:

     The bulk of processing of an OCI call happens on the server, so if two threads using OCI calls go to the same connection, then one them can be blocked while the other finishes processing at the server.

     Use one error handle for each thread in an application, since OCI errors can be over-written by other threads.

Initialization and Termination

The types and functions described in this section are associated with the initialization and termination of the OCIThread package. OCIThread must be initialized before any of its functionality can be used.

The observed behavior of the initialization and termination functions is the same regardless of whether OCIThread is in single-threaded or multithreaded environment. Table 9-1 lists functions for thread initialization and termination.

Passive Threading Primitives

The passive threading primitives deal with the manipulation of mutex, thread ID's, and thread-specific data. Since the specifications of these primitives do not require the existence of multiple threads, they can be used both in multithreaded and single-threaded operating systems. Table 9-2 lists functions used to implement passive threading.

Active Threading Primitives

The active threading primitives deal with manipulation of actual threads. Because specifications of most of these primitives require multiple threads, they work correctly only in the enabled OCIThread; In the disabled OCIThread, they always return an error. The exception is OCIThreadHandleGet(); it may be called in a single-threaded environment and has no effect.

Active primitives can only be called by code running in a multithreaded environment. You can call OCIThreadIsMulti() to determine whether the environment is multithreaded or single-threaded. Table 9-3 lists functions used to implement active threading.

OCI Connection Pooling Concepts

Oracle has several transaction monitor capabilities such as the fine-grained management of database sessions and connections. This is done by separating the notion of database sessions (user handles) from connections (server handles). Using these OCI calls for session switching and session migration, it is possible for an application server or transaction monitor to multiplex several sessions over fewer physical connections, thus achieving a high degree of scalability by pooling of connections and back-end Oracle server processes.

The connection pool itself is normally configured with a shared pool of physical connections, translating to a back-end server pool containing an identical number of dedicated server processes.

The number of physical connections is less than the number of database sessions in use by the application.The number of physical connections and back-end server processes are also reduced by using connection pooling. Thus many more database sessions can be multiplexed.

Multiple Connection Pools

This advanced concept can be used for different database connections. Multiple connection pools can also be used when different priorities are assigned to users. Different service level guarantees can be implemented using connection pooling.

The following figure illustrates connection pooling:

Figure 9-1 OCI Connection Pooling

Description of "Figure 9-1 OCI Connection Pooling"

OCI Calls for Connection Pooling

The steps in using connection pooling in your application are:

• Allocate the Pool Handle

• Create the Connection Pool

• Logon to the Database

• Deal with SGA Limitations in Connection Pooling

• Logoff from the Database

• Destroy the Connection Pool

• Free the Pool Handle

OCICPool *poolhp;
OCIHandleAlloc((dvoid *) envhp, (dvoid **) &poolhp, OCI_HTYPE_CPOOL, 
                      (size_t) 0, (dvoid **) 0));

OCIConnectionPoolCreate((OCIEnv *)envhp,
                   (OCIError *)errhp, (OCICPool *)poolhp,
                   &poolName, &poolNameLen,
                   (text *)database,strlen(database),
                   (ub4) conMin, (ub4) conMax, (ub4) conIncr,
                   (text *)pooluser,strlen(pooluser),
                   (text *)poolpasswd,strlen(poolpasswd),
                   OCI_DEFAULT));

for (i = 0; i < MAXTHREADS; ++i)
  {
    checkerr(errhp, OCILogoff((dvoid *) svchp[i], errhp));
  }
  checkerr(errhp, OCIConnectionPoolDestroy(poolhp, errhp, OCI_DEFAULT));
  checkerr(errhp, OCIHandleFree((dvoid *)poolhp, OCI_HTYPE_CPOOL));

Examples of OCI Connection Pooling

Examples of connection pooling in tested complete programs can be found in cdemocp.c and cdemocpproxy.c in directory demo.

Functionality of OCI Session Pooling

Session pooling has the following features:

• Create, maintain and manage a pool of stateless sessions transparently.

• Provide an interface for the application to create a pool and specify the minimum, increment and maximum number of sessions in the pool.

• Provide an interface for the user to obtain and release a default or 'tagged' session to the pool. A 'tagged' session is one with certain client-defined properties.

• Allow the application to dynamically change the number of minimum and maximum number of sessions.

• Provide a mechanism to always maintain an optimum number of open sessions, by closing sessions that have been idle for very long, and creating sessions when required.

• Allow for session pooling with authentication.

Homogeneous and Heterogeneous Session Pools

A session pool can be either homogeneous or heterogeneous. Homogeneous session pooling means that sessions in the pool are alike with respect to authentication (have the same user name and password and privileges). Heterogeneous session pooling means that you must provide authentication information because the sessions can have different security attributes and privileges.

Using Tags in Session Pools

The tags provide a way for users to customize sessions in the pool. A client may get a default or untagged session from a pool, set certain attributes on the session (such as NLS settings), and return the session to the pool, labeling it with an appropriate tag in the OCISessionRelease() call.

The user, or some other user, may request a session with the same tags in order to have a session withe the same attributes, and can do so by providing the same tag in the OCISessionGet() call.

OCI Handles for Session Pooling

Two handle types have been added for session pooling:

OCISPool *spoolhp;        
OCIHandleAlloc((dvoid *) envhp, (dvoid **) &spoolhp, OCI_HTYPE_SPOOL, 
                      (size_t) 0, (dvoid **) 0));

OCIAuthInfo *authp;       
OCIHandleAlloc((dvoid *) envhp, (dvoid **) &authp, OCI_HTYPE_AUTHINFO, 
                      (size_t) 0, (dvoid **) 0));

Using OCI Session Pooling

The steps in writing a simple session pooling application which uses a user name and password are:

• Allocate the session pool handle using OCIHandleAlloc() for an OCISPool handle. Multiple session pools can be created for an environment handle.

• Create the session pool using OCISessionPoolCreate() with mode set to OCI_DEFAULT (for a new session pool). Refer to the function for a discussion of the other modes.

• Loop for each thread. Create the thread with a function that does the following:

• Allocate an authentication information handle of type OCIAuthInfo using OCIHandleAlloc().

• Set the user name and password in the authentication information handle using OCIAttrSet().

• Get a pooled session using OCISessionGet() with mode set to OCI_SESSGET_SPOOL.

• Perform the transaction.

• Commit or rollback the transactions.

• Release the session (logoff) with OCISessionRelease().

• Free the authentication information handle with OCIHandleFree().

• End of the loop for each thread.

• Destroy the session pool using OCISessionPoolDestroy().

OCI Calls for Session Pooling

Here are the usages for OCI calls for session pooling.

OCISPool *poolhp; 
OCIHandleAlloc((dvoid *) envhp, (dvoid **) &poolhp, OCI_HTYPE_SPOOL, (size_t) 0,
               (dvoid **) 0));

OCISessionPoolCreate(envhp, errhp, poolhp, (OraText **)&poolName, 
              (ub4 *)&poolNameLen, database, 
              (ub4)strlen((const signed char *)database),
              sessMin, sessMax, sessIncr,
              (OraText *)appusername,
              (ub4)strlen((const signed char *)appusername),
              (OraText *)apppassword,
              (ub4)strlen((const signed char *)apppassword),
              OCI_DEFAULT);

OCISessionPoolDestroy(poolhp, errhp, OCI_DEFAULT);

OCIHandleFree((dvoid *)poolhp, OCI_HTYPE_SPOOL);

Example of OCI Session Pooling

Here is an example of session pooling in a tested, complete program:

Functions for Session Creation

The choices are:

1. OCILogon()

   This is the simplest way to get an OCI Session. The advantage is ease of obtaining an OCI service context. The disadvantage is that you cannot perform any advance OCI operations like session migration, proxy authentication, using a connection pool, or a session pool.

   See Also:

   "Application Initialization, Connection, and Session Creation"

2. OCILogon2()

   This includes the functionality of OCILogon() to get a session. This session may be a new one with a new underlying connection, or one that is started over a virtual connection from an existing connection pool, or one from an existing session pool. The mode parameter value that the function is called with determines its behavior.

   The user cannot modify the attributes (except OCI_ATTR_STMTCACHESIZE) of the service context returned by OCI.

   See Also:

   "OCILogon2()"

3. OCISessionBegin()

   This supports all the various options of an OCI session such as proxy authentication, getting a session from a connection pool or a session pool, external credentials, and migratable sessions. This is the lowest level call where all handles are needed to be explicitly allocated and all attributes set, and OCIServerAttach() is to be called prior to this call.

   See Also:

   "OCISessionBegin()"

4. OCISessionGet()

   This is now the recommended method to get a session. This session may be a new one with a new underlying connection, or one that is started over a virtual connection from an existing connection pool, or one from an existing session pool. The mode parameter value that the function is called with determines its behavior. This works like OCILogon2() but additionally enables you to specify tags for obtaining specific sessions from the pool.

   See Also:

   "OCISessionGet()"

Choosing Between Different Types of OCI Sessions

The choices are:

• Basic OCI Sessions

  This works by using user name and password over a dedicated OCI server handle. This is the no-pool mechanism. See earlier notes of when to use it.

  If authentication is obtained through external credentials, then user name or password is not required.

• Session Pool Sessions

  These sessions are from the session pool cache. Some sessions may be tagged. These are stateless sessions. Each OCISessionGet() and OCISessionRelease() call gets and releases a session from the session cache. This saves the server from creating and destroying sessions.

  See the earlier notes on connection pool sessions versus session pooling sessions versus no-pooling sessions.

• Connection Pool Sessions

  These are sessions created using OCISessionGet() and OCISessionBegin() calls from an OCI Connection Pool. There is no session cache as these are statefull sessions. Each call creates a new session and the user is responsible for terminating these sessions.

  The sessions are automatically migratable between the server handles of the connection pool. Each session can have user name and password or be a proxy session. See the earlier notes on connection pool sessions versus session pooling sessions versus no-pooling sessions.

• Sessions Sharing a Server Handle

  You can multiplex several OCI Sessions over a few physical connections. The application does this manually by having the same server handle for these multiple sessions. It is preferred to have the session multiplexing details be left to OCI by using the OCI Connection Pool APIs.

• Proxy Sessions

  This is useful if the password of the client needs to be protected from the middle-tier. Proxy sessions can also be part of OCI Connection Pool and OCI Session Pool.

  See Also:

  "Middle-Tier Applications in OCI"

• Migratable Sessions

With transaction handles being migratable, there should be no need for applications to use this older feature, in light of OCI Connection Pooling.

Statement Caching without Session Pooling in OCI

Users perform the usual OCI steps to logon. The call to obtain a session will have a mode that specifies whether statement caching is enabled for the session. Initially the statement cache will be empty. Developers will try to find a statement in the cache using the statement text. If the statement exists the API will return a previously prepared statement handle, otherwise it will return an newly prepared statement handle.

The application developer can perform binds and defines and then simply execute and fetch the statement before returning the statement back to the cache. In the latter case, where the statement handle was not found, the developer will need to set different attributes on the handle in addition to the other steps.

OCIStmtPrepare2() will also take a mode which will determine if the developer wants a prepared statement handle or a null statement handle if the statement is not found in the cache.

The pseudo code will look like:

OCISessionBegin( userhp, ... OCI_STMT_CACHE)  ;
OCIAttrset(svchp, userhp, ...);  /* Set the user handle in the service context */
OCIStmtPrepare2(svchp, &stmthp, stmttext, key, ...);
OCIBindByPos(stmthp, ...);
OCIDefineByPos(stmthp, ...);
OCIStmtExecute(svchp, stmthp, ...);
OCIStmtFetch(svchp, ...);
OCIStmtRelease(stmthp, ...);
...

Statement Caching with Session Pooling in OCI

The concepts remain the same, except that the statement cache is enabled at the session pool layer rather than at the session layer.

The attribute OCI_ATTR_SPOOL_STMTCACHESIZE sets the default statement cache size for each of the sessions in the session pool to this value. It is set on the OCI_HTYPE_SPOOL handle. The statement cache size for a particular session in the pool can anytime be overridden by using OCI_ATTR_STMTCACHESIZE on that session. The value of OCI_ATTR_SPOOL_STMTCACHESIZE can be changed at any time. This attribute can be used to enable or disable statement caching at the pool level, after creation, just as attribute OCI_ATTR_STMTCACHESIZE (on the service context) is used to enable or disable statement caching at the session level. This change will be reflected on individual sessions in the pool, when they are handed to a user. Tagged sessions are an exception to this behavior. This is explained later.

Enabling or disabling of statement caching is allowed on individual pooled sessions similar to non-pooled sessions.

A user can enable statement caching on a session retrieved from a non-statement cached pool in an OCISessionGet() or OCILogon2() call by specifying OCI_SESSGET_STMTCACHE or OCI_LOGON2_STMTCACHE, respectively, in the mode argument.

When a user asks for a session from a session pool, the statement cache size for that session will default to that of the pool. This may also mean enabling or disabling statement caching in that session. For example, if a pooled session (session A) has statement caching enabled, and statement caching is turned off in the pool, and a user asks for a session, and session A is returned, then statement caching will be turned off in Session A. As another example, if Session A in a pool does not have statement caching enabled, and statement caching at the pool level is turned on, then before returning session A to a user, statement caching on Session A with size equal to that of the pool is turned on.

This will not hold true if a tagged session is asked for and retrieved. In this case, the size of the statement cache will not be changed. Consequently, it will not be turned on or off. Moreover, if the user specifies mode OCI_SESSGET_STMTCACHE in the OCISessionGet() call, this will be ignored if the session is tagged. In our earlier example, if Session A was tagged, then it is returned as is to the user.

Rules for Statement Caching in OCI

Here are some rules to follow:

• Use the function OCIStmtPrepare2() instead of OCIStmtPrepare(). If you are using OCIStmtPrepare(), you are strongly urged not to use a statement handle across different service contexts. Doing so will raise an error if the statement has been obtained by OCIStmtPrepare2(). Migration of a statement handle to a new service context actually closes the cursor associated with the old session and therefore no sharing is achieved. Client-side sharing is also not obtained, because OCI will free all buffers associated with the old session when the statement handle is migrated.

• You are urged to keep one service context for each session and use statement handles only for that service context. That will be the preferred and recommended model and usage.

• A call to OCIStmtPrepare2(), even if the session does not have a statement cache, will also allocate the statement handle and therefore applications using only OCIStmtPrepare2() must not call OCIHandleAlloc() for the statement handle.

• A call to the OCIStmtPrepare2() must be followed with OCIStmtRelease() after the user is done with the statement handle. If statement caching is used, this will release the statement to the cache. If statement caching is not used, the statement will be deallocated. Do not call OCIHandleFree() to free the memory.

• If the call to OCIStmtPrepare2() is made with the OCI_PREP2_CACHE_SEARCHONLY mode and a NULL statement was returned (statement was not found), the subsequent call to OCIStmtRelease() is not required and must not be performed.

• Do not call OCIStmtRelease() for a statement that was prepared using OCIStmtPrepare().

• The statement cache has a maximum size (number of statements) which can be modified by an attribute on the service context, OCI_ATTR_STMTCACHESIZE. The default value is 20. This attribute can also be used to enable or disable statement caching for the session, pooled or non-pooled. If OCISessionBegin() is called without mode set as OCI_STMT_CACHE, then OCI_ATTR_STMTCACHESIZE can be set on the service context to a nonzero attribute to turn on statement caching. If statement caching is not turned on at the session pool level, OCISessionGet() will return a non-statement cache-enabled session. OCI_ATTR_STMTCACHESIZE can be used to turn the caching on. Similarly the same attribute can be used to turn off statement caching by setting the cache size to zero.

• You can choose to tag a statement at the release time so that the next time you can request a statement of the same tag. The tag will be used to search the cache. An untagged statement (tag is NULL) is a special case of a tagged statement. Two statements are considered different if they only differ in their tags, or if one is untagged and the other is not.

  See Also:

  For information about the functions for statement caching,

  • "Statement Functions"

  • "Service Context Handle Attributes"

  • "Session Pool Handle Attributes"

OCI Statement Caching Code Example

Here is an example of statement caching:

Registering User Callbacks in OCI

An application can register user callback libraries with the OCIUserCallbackRegister() function. Callbacks are registered in the context of the environment handle. An application can retrieve information about callbacks registered with a handle with the OCIUserCallbackGet() function.

A user-defined callback is a subroutine that is registered against an OCI call and an environment handle. It can be specified to be either an entry callback, a replacement callback, or an exit callback.

• If it is an entry callback, it is called when the program enters the OCI function.

• Replacement callbacks are executed after entry callbacks. If the replacement callback returns a value of OCI_CONTINUE, then a subsequent replacement callback or the normal OCI-specific code is executed. If a replacement callback returns anything other than OCI_CONTINUE, subsequent replacement callbacks and the OCI code does not execute.

• After a replacement callback returns something other than OCI_CONTINUE, or an OCI function successfully executes, program control transfers to the exit callback (if one is registered).

If a replacement or exit callback returns anything other than OCI_CONTINUE, then the return code from the callback is returned from the associated OCI call.

A user callback can return OCI_INVALID_HANDLE when either an invalid handle or an invalid context is passed to it.

OCIUserCallbackRegister( hndlp, 
                         OCI_HTYPE_ENV, 
                         errh, 
                         stmtprep_entry_dyncbk_fn, 
                         dynamic_context, 
                         OCI_FNCODE_STMTPREPARE,
                         OCI_UCBTYPE_ENTRY
                         (OCIUcb*) NULL);

typedef sword (*OCIUserCallback)
     (dvoid *ctxp,      /* context for the user callback*/
      dvoid *hndlp,     /* handle for the callback, env handle for now */
      ub4 type,         /* type of handlp, OCI_HTYPE_ENV for this release */
      ub4 fcode,        /* function code of the OCI call */
      ub1 when,         /* type of the callback, entry or exit */
      sword returnCode, /* OCI return code */
      ub4 *errnop,      /* Oracle error number */
      va_list arglist); /* parameters of the oci call */

OCIXyzCall()
{
 Acquire mutexes on handles;
 retCode = OCI_SUCCESS;
 errno = 0;
 for all ENTRY callbacks do
  {
     
     EntryretCode = (*entryCallback)(..., retcode, &errno, ...);
     if (retCode != OCI_CONTINUE)
      {
         set errno in error handle or environment handle;
         retCode = EntryretCode;
       }
   }
  for all REPLACEMENT callbacks do
  {
   retCode = (*replacementCallback) (..., retcode, &errno, ...);
   if (retCode != OCI_CONTINUE)
      {
       set errno in error handle or environment handle
       goto executeEXITCallback;
       }
   }

   retCode = return code for XyzCall; /* normal processing of OCI call */

   errno = error number from error handle or env handle;

 executeExitCallback:
   for all EXIT callbacks do
   {
       exitRetCode = (*exitCallback)(..., retCode, &errno,...);
       if (exitRetCode != OCI_CONTINUE)
       {
           set errno in error handle or environment handle;
           retCode = exitRetCode;
       }
   }
    release mutexes;
    return retCode
}

setenv ORA_OCI_UCBPKG mypkg

setenv ORA_OCI_UCBPKG "mypkg;yourpkg;oraclepkg;sunpkg;msoftpkg"

sword fooInit(metaCtx, libCtx, argfmt, argc, argv)
      dvoid *         metaCtx;         /* The metacontext */
      dvoid *         libCtx;          /* The context for this package. */
      ub4             argfmt;          /* package argument format */
      sword           argc;            /* package arg count*/
      dvoid *         argv[];          /* package arguments */
{
  return  (OCISharedLibInit(metaCtx, libCtx, argfmt, argc, argv,
                            fooEnvCallback));
}

typedef sword (*OCIEnvCallbackType)(OCIEnv *env, ub4 mode,
                                    size_t xtramem_sz, dvoid *usrmemp,
                                    OCIUcb *ucbDesc);

setenv ORA_OCI_UCBPKG "pkg1;pkg2;pkg3;pkg4;pkg5" 
 

pkgNInit(dvoid *metaCtx, dvoid *libCtx, ub4 argfmt, sword argc, dvoid **argv)
{
  return OCISharedLibInit(metaCtx, libCtx, argfmt, argc, argv, pkgNEnvCallback);
}

pkgNEnvCallback(OCIEnv *env, ub4 mode, size_t xtramemsz,
                                dvoid *usrmemp, OCIUcb *ucbDesc)
{
  OCIHandleAlloc((dvoid *)env, (dvoid **)&errh, OCI_HTYPE_ERROR, (size_t) 0,
        (dvoid **)NULL);

  OCIUserCallbackRegister(env, OCI_HTYPE_ENV, errh, pkgN_entry_callback_fn,
        pkgNctx, OCI_FNCODE_STMTPREPARE, OCI_UCBTYPE_ENTRY, ucbDesc);

  OCIUserCallbackRegister(env, OCI_HTYPE_ENV, errh, pkgN_replace_callback_fn,
        pkgNctx, OCI_FNCODE_STMTPREPARE, OCI_UCBTYPE_REPLACE, ucbDesc);

  OCIUserCallbackRegister(env, OCI_HTYPE_ENV, errh, pkgN_exit_callback_fn,
        pkgNctx, OCI_FNCODE_STMTPREPARE, OCI_UCBTYPE_EXIT, ucbDesc);

  return OCI_CONTINUE;
}
 

OCIUserCallbackRegister(env, OCI_HTYPE_ENV, errh, static_entry_callback_fn,
        pkgNctx, OCI_FNCODE_STMTPREPARE, OCI_UCBTYPE_ENTRY, (OCIUcb *)NULL);

  OCIUserCallbackRegister(env, OCI_HTYPE_ENV, errh, static_replace_callback_fn,
        pkgNctx, OCI_FNCODE_STMTPREPARE, OCI_UCBTYPE_REPLACE, (OCIUcb *)NULL);

  OCIUserCallbackRegister(env, OCI_HTYPE_ENV, errh, static_exit_callback_fn,
        pkgNctx, OCI_FNCODE_STMTPREPARE, OCI_UCBTYPE_EXIT, (OCIUcb *)NULL);
 

static_entry_callback_fn() 
pkg1_entry_callback_fn() 
pkg2_entry_callback_fn() 
pkg3_entry_callback_fn() 
pkg4_entry_callback_fn() 
pkg5_entry_callback_fn() 
 
static_replace_callback_fn() 
 pkg1_replace_callback_fn() 
  pkg2_replace_callback_fn() 
   pkg3_replace_callback_fn() 
    pkg4_replace_callback_fn() 
     pkg5_replace_callback_fn() 
 
      OCI code for OCIStmtPrepare call 
 
pkg5_exit_callback_fn() 
pkg4_exit_callback_fn() 
pkg3_exit_callback_fn() 
pkg2_exit_callback_fn() 
pkg1_exit_callback_fn()

static_exit_callback_fn()

OCI Callbacks from External Procedures

There are several OCI functions that can be used as callbacks from external procedures.

Failover Callback Overview

To address the problems described earlier, the application developer can register a failover callback function. In the event of failover, the callback function is invoked several times during the course of reestablishing the user's session.

The first call to the callback function occurs when Oracle first detects an instance connection loss. This callback is intended to allow the application to inform the user of an upcoming delay. If failover is successful, a second call to the callback function occurs when the connection is reestablished and usable.

At this time the client may wish to replay ALTER SESSION commands and inform the user that failover has happened. If failover is unsuccessful, then the callback is called to inform the application that failover will not take place. Additionally, the callback is called each time a user handle besides the primary handle is re-authenticated on the new connection. Since each user handle represents a server-side session, the client may wish to replay ALTER SESSION commands for that session.

An initial attempt at failover may not always successful. The OCI provides a mechanism for retrying failover after an unsuccessful attempt.

Failover Callback Structure and Parameters

The basic structure of a user-defined application failover callback function is as follows:

sb4 appfocallback_fn ( dvoid      * svchp, 
                       dvoid      * envhp, 
                       dvoid      * fo_ctx, 
                       ub4        fo_type, 
                       ub4        fo_event );

An example is provided in the section "Failover Callback Example" for the following parameters:

svchp

The first parameter, svchp, is the service context handle. It is of type dvoid *.

envhp

The second parameter, envhp, is the OCI environment handle. It is of type dvoid *.

fo_ctx

The third parameter, fo_ctx, is a client context. It is a pointer to memory specified by the client. In this area the client can keep any necessary state or context. It is passed as a dvoid *.

fo_type

The fourth parameter, fo_type, is the failover type. This lets the callback know what type of failover the client has requested. The usual values are:

• OCI_FO_SESSION, which indicates that the user has requested only session failover.

• OCI_FO_SELECT, which indicates that the user has requested select failover as well.

fo_event

The last parameter is the failover event. This indicates to the callback why it is being called. It has several possible values:

• OCI_FO_BEGIN indicates that failover has detected a lost connection and failover is starting.

• OCI_FO_END indicates successful completion of failover.

• OCI_FO_ABORT indicates that failover was unsuccessful, and there is no option of retrying.

• OCI_FO_ERROR also indicates that failover was unsuccessful, but it gives the application the opportunity to handle the error and retry failover.

• OCI_FO_REAUTH indicates that you have multiple authentication handles and failover has occurred after the original authentication. It indicates that a user handle has been re-authenticated. To find out which, the application checks the OCI_ATTR_SESSION attribute of the service context handle (which is the first parameter).

Failover Callback Registration

For the failover callback to be used, it must be registered on the server context handle. This registration is done by creating a callback definition structure and setting the OCI_ATTR_FOCBK attribute of the server handle to this structure.

The callback definition structure must be of type OCIFocbkStruct. It has two fields: callback_function, which contains the address of the function to call, and fo_ctx which contains the address of the client context.

An example of callback registration is included as part of the example in the next section.

Failover Callback Example

The following code shows an example of a simple user-defined callback function definition, registration, and unregistration.

sb4  callback_fn(svchp, envhp, fo_ctx, fo_type, fo_event)
dvoid * svchp;
dvoid * envhp;
dvoid *fo_ctx;
ub4 fo_type;
ub4 fo_event;
{
switch (fo_event) 
   {
   case OCI_FO_BEGIN:
   {
     printf(" Failing Over ... Please stand by \n");
     printf(" Failover type was found to be %s \n",
                     ((fo_type==OCI_FO_SESSION) ? "SESSION" 
                     :(fo_type==OCI_FO_SELECT) ? "SELECT"
                     : "UNKNOWN!")); 
     printf(" Failover Context is :%s\n", 
                    (fo_ctx?(char *)fo_ctx:"NULL POINTER!"));
     break;
   }
   case OCI_FO_ABORT:
   {
     printf(" Failover stopped. Failover will not take place.\n");
     break;
   }
   case    OCI_FO_END:
   {
       printf(" Failover ended ...resuming services\n");
     break;
   }
   case OCI_FO_REAUTH:
   {
       printf(" Failed over user. Resuming services\n");
     break;
   }
   default:
   {
     printf("Bad Failover Event: %d.\n",  fo_event);
     break;
   }
   }
   return 0;
}

int register_callback(srvh, errh)
dvoid *srvh; /* the server handle */
OCIError *errh; /* the error handle */
{
  OCIFocbkStruct failover;                 /*  failover callback structure */
  /* allocate memory for context */
  if (!(failover.fo_ctx = (dvoid *)malloc(strlen("my context.")+1)))
     return(1);
  /* initialize the context. */
  strcpy((char *)failover.fo_ctx, "my context.");
  failover.callback_function = &callback_fn;
  /* do the registration */
  if (OCIAttrSet(srvh, (ub4) OCI_HTYPE_SERVER,
                (dvoid *) &failover, (ub4) 0,
                (ub4) OCI_ATTR_FOCBK, errh)  != OCI_SUCCESS)
     return(2);
  /* successful conclusion */
  return (0);
}

OCIFocbkStruct failover;   /*  failover callback structure */
sword status;
 
  /* set the failover context to null */
  failover.fo_ctx = NULL; 
  /* set the failover callback to null */ 
  failover.callback_function = NULL; 
  /* un-register the callback */
  status = OCIAttrSet(srvhp, (ub4) OCI_HTYPE_SERVER,
                      (dvoid *) &failover, (ub4) 0,
                      (ub4) OCI_ATTR_FOCBK, errhp);

Handling OCI_FO_ERROR

A failover attempt is not always successful. If the attempt fails, the callback function receives a value of OCI_FO_ABORT or OCI_FO_ERROR in the fo_event parameter. A value of OCI_FO_ABORT indicates that failover was unsuccessful, and no further failover attempts are possible. OCI_FO_ERROR, on the other hand, provides the callback function with the opportunity to handle the error in some way. For example, the callback may choose to wait a specified period of time and then indicate to the OCI library that it must reattempt failover.

Consider the following timeline of events:

The callback function triggers the new failover attempt by returning a value of OCI_FO_RETRY from the function.

The following example code shows a callback function which can be used to implement the failover strategy similar to the scenario described earlier. In this case the failover callback enters a loop in which it sleeps and then reattempts failover until it is successful:

/*--------------------------------------------------------------------*/
/* the user defined failover callback  */
/*--------------------------------------------------------------------*/
sb4  callback_fn(svchp, envhp, fo_ctx, fo_type, fo_event )
dvoid * svchp;
dvoid * envhp;
dvoid *fo_ctx;
ub4 fo_type;
ub4 fo_event;
{
   OCIError *errhp;
   OCIHandleAlloc(envhp, (dvoid **)&errhp, (ub4) OCI_HTYPE_ERROR,
              (size_t) 0, (dvoid **) 0);
   switch (fo_event) 
   {
   case OCI_FO_BEGIN:
   {
     printf(" Failing Over ... Please stand by \n");
     printf(" Failover type was found to be %s \n",
            ((fo_type==OCI_FO_NONE) ? "NONE"
             :(fo_type==OCI_FO_SESSION) ? "SESSION" 
             :(fo_type==OCI_FO_SELECT) ? "SELECT"
             :(fo_type==OCI_FO_TXNAL) ? "TRANSACTION"
             : "UNKNOWN!")); 
     printf(" Failover Context is :%s\n", 
            (fo_ctx?(char *)fo_ctx:"NULL POINTER!"));
     break;
   }
   case OCI_FO_ABORT:
   {
     printf(" Failover aborted. Failover will not take place.\n");
     break;
   }
   case    OCI_FO_END:
   { 
       printf("\n Failover ended ...resuming services\n");
     break;
   }
   case OCI_FO_REAUTH:
   { 
       printf(" Failed over user. Resuming services\n");
     break;
   }
   case OCI_FO_ERROR:
   {
     /* all invocations of this can only generate one line. The newline
      * will be put at fo_end time.
      */
     printf(" Failover error gotten. Sleeping...");
     sleep(3);
     printf("Retrying. ");
     return (OCI_FO_RETRY);
     break;
   }
   default:
   {
     printf("Bad Failover Event: %d.\n",  fo_event);
     break;
   }
   }
   return 0;
}

OCIEvent Handle

The OCIEvent handle encapsulates the attributes from the event payload. OCI implicitly allocates this handle prior to calling the event callback, which can obtain the read-only attributes of the event by calling OCIAttrGet(). Memory associated with these attributes is only valid for the duration of the event callback.

OCI Failover for Connection and Session Pools

A connection pool in an instance of Real Application Clusters (RAC) consists of a pool of connections connected to different instances of the RAC. Upon receiving the node failure notification, all the connections connected to that particular instance should be cleaned up. For the connections that are in use, OCI has to close the connections: transparent application failover (TAF) occurs immediately and those connections will be reestablished. The connections that are idle and in the free list of the pool have to be purged, so that a bad connection is never returned back to the user from the pool.

To accommodate custom connection pools, OCI will provide a callback function that can be registered on the environment handle. If registered, this callback is invoked when an HA event occurs. Sessions pools are treated the same way as connection pools. Note that server handles from OCI connection pools or session pools will not be passed to the callback. Hence in some cases, the callback could be called with an empty list of connections.

OCI Failover for Independent Connections

No special handling is required for independent connections; all such connections that are connected to failed instances are immediately disconnected. For idle connections, TAF will be engaged to reestablish the connection when the connection is used on a subsequent OCI call. Connections that are in use at the time of the failure event will be broken out immediately, so that TAF can begin. Note that this applies for the "in-use" connections of connection and session pools also.

Event Callback

The event callback, of type OCIEventCallback, has the following signature:

void evtcallback_fn (dvoid     *evtctx,
                     OCIEvent  *eventhp );

where evtctx is the client context and OCIEvent is an event handle which is opaque to the OCI library. The other input argument is eventhp, the event handle: that is, the attributes associated with an event.

If registered, this function will be called once for each event. In the case of RAC HA events, this callback will be invoked after the affected connections have been disconnected. The following environment handle attributes are used to register an event callback and context, respectively:

• OCI_ATTR_EVTCBK is of datatype OCIEventCallback *. It is read-only.

• OCI_ATTR_EVTCTX is of datatype dvoid *. It is also read-only.

text *myctx = "dummy context"; /* dummy context passed to callback fn */
...
/* OCI_ATTR_EVTCBK and OCI_ATTR_EVTCTX are read-only. */
OCIAttrSet(envhp, (ub4) OCI_HTYPE_ENV, (dvoid *) evtcallback_fn,
           (ub4) 0, (ub4) OCI_ATTR_EVTCBK, errhp);
OCIAttrSet(envhp, (ub4) OCI_HTYPE_ENV, (dvoid *) myctx,
           (ub4) 0, (ub4) OCI_ATTR_EVTCTX, errhp);
...

Within the OCI Event callback, the list of affected server handles is encapsulated in the OCIEvent handle. For RAC HA DOWN events, client applications can iterate over a list of server handles that are affected by the event by using OCIAttrGet() with attribute types OCI_ATTR_HA_SVRFIRST and OCI_ATTR_HA_SVRNEXT:

OCIAttrGet(eventhp, OCI_HTYPE_EVENT, (dvoid *)&srvhp, (ub4 *)0,
           OCI_ATTR_HA_SRVFIRST, errhp); 
/* or, */
OCIAttrGet(eventhp, OCI_HTYPE_EVENT, (dvoid *)&srvhp, (ub4 *)0,
           OCI_ATTR_HA_SRVNEXT, errhp);

When called with attribute OCI_ATTR_HA_SRVFIRST, this function will retrieve the first server handle in the list of server handles affected. When called with attribute OCI_ATTR_HA_SRVNEXT, this function will retrieve the next server handle in the list. This function will return OCI_NO_DATA and srvhp will be a NULL pointer, when there are no more server handles to return.

srvhp is an output pointer to a server handle whose connection has been closed as a result of an HA event. errhp is an error handle to populate. The application returns an OCI_NO_DATA error when there are no more affected server handles to retrieve.

When retrieving the list of server handles that have been affected by an HA event, be aware that the connection has already been closed and many server handle attributes are no longer valid. Instead, use the user memory segment of the server handle to store any per-connection attributes required by the event notification callback. This memory remains valid until the server handle is freed.

Custom Pooling: Tagged Server Handles

The following features apply to custom pools:

1. You can tag a server handle with its parent connection object if it is created on behalf of a custom pool. Use the "user memory" parameters of OCIHandleAlloc() to request that the server handle be allocated with a user memory segment. A pointer to the "user memory" segment is returned by OCIHandleAlloc().

2. When an HA event occurs, and an affected server handle has been retrieved, there is a means to retrieve the server handle's tag information so appropriate cleanup can be performed. The attribute OCI_ATTR_USER_MEMORY is used to retrieve a pointer to a handle's user memory segment. OCI_ATTR_USER_MEMORY is valid for all user-allocated handles. If the handle was allocated with extra memory, this attribute will return a pointer to the user memory. A NULL pointer will be returned for those handles not allocated with extra memory. This attribute is read-only and is of datatype dvoid*.

Event Notification Example

sword retval;
OCIServer *srvhp;
struct myctx {
   dvoid *parentConn_myctx;
   uword numval_myctx;
};
typedef struct myctx myctx; 
myctx  *myctxp;
/* Allocate a server handle with user memory - pre 10.2 functionality */
if (retval = OCIHandleAlloc(envhp, (dvoid **)&srvhp, OCI_HTYPE_SERVER,
                            (size_t)sizeof(myctx), (dvoid **)&myctxp)
/* handle error */
myctxp->parentConn_myctx = <parent connection reference>;
 
/* In an event callback function, retrieve the pointer to the user memory */
evtcallback_fn(dvoid *evtctx, OCIEvent *eventhp)
{ 
  myctx *ctxp = (myctx *)evtctx;
  OCIServer *srvhp;
  OCIError *errhp;
  sb4       retcode;
  retcode = OCIAttrGet(eventhp, OCI_HTYPE_SERVER, &srvhp, (ub4 *)0,
                       OCI_ATTR_HA_SRVFIRST, errhp); 
  while (!retcode) /* OCIAttrGet will return OCI_NO_DATA if no more srvhp */ 
  {  
     OCIAttrGet((dvoid *)srvhp, OCI_HTYPE_SERVER, (dvoid *)&ctxp,
                (ub4)0, (ub4)OCI_ATTR_USER_MEMORY, errhp);
           /* Remove the server handle from the parent connection object */
     retcode = OCIAttrGet(eventhp, OCI_HTYPE_SERVER, &srvhp, (ub4 *)0,
                          OCI_ATTR_HA_SRVNEXT, errhp);
...
  }
...
}

Determining Transparent Application Failover (TAF) Capabilities

You can have the application adjust its behavior if a connection is or is not TAF-enabled. Use OCIAttrGet() as follows to find out if a server handle is TAF-enabled:

boolean taf_capable;
...
OCIAttrGet(srvhp, (ub4) OCI_HTYPE_SERVER, (dvoid *) &taf_capable, 
           (ub4) sizeof(taf_capable), (ub4)OCI_ATTR_TAF_ENABLED, errhp);
...

where taf_capable is a boolean variable, which this call sets to TRUE if the server handle is TAF-enabled, and FALSE if not; srvhp is an input target server handle; OCI_ATTR_TAF_ENABLED is a an attribute which is a pointer to a boolean variable and is read-only; errhp is an input error handle.

OCI Streams Advanced Queuing Functions

The OCI library includes several functions related to Streams Advanced Queuing:

• OCIAQEnq()

• OCIAQDeq()

• OCIAQListen()

• OCIAQListen2()

• OCIAQEnqArray()

• OCIAQDeqArray()

You can enqueue an array of messages to a single queue. The messages all share the same enqueue options, but each message in the array can have different message properties. You can also dequeue an array of messages from a single queue. For transaction group queues, you can dequeue all messages for a single transaction group using one call.

OCI Streams Advanced Queuing Descriptors

The following descriptors are used by OCI Streams AQ operations:

• OCIAQEnqOptions

• OCIAQDeqOptions

• OCIAQMsgProperties

• OCIAQAgent

You can allocate these descriptors with respect to the service handle using the standard OCIDescriptorAlloc() call. The following code shows examples of this:

OCIDescriptorAlloc(svch, &enqueue_options, OCI_DTYPE_AQENQ_OPTIONS, 0, 0 ); 
OCIDescriptorAlloc(svch, &dequeue_options, OCI_DTYPE_AQDEQ_OPTIONS, 0, 0 ); 
OCIDescriptorAlloc(svch, &message_properties, OCI_DTYPE_AQMSG_PROPERTIES, 0, 0);
OCIDescriptorAlloc(svch, &agent, OCI_DTYPE_AQAGENT, 0, 0 ); 

Each descriptor has a variety of attributes which can be set or read.

Streams Advanced Queuing in OCI Versus PL/SQL

The following tables compare functions, parameters, and options for OCI Streams AQ functions and descriptors, and PL/SQL AQ functions in the DBMS_AQ package.

The following table compares the parameters for the enqueue functions:

queue_name

queue_name

enqueue_options

enqueue_options

message_properties

message_properties

payload

payload

msgid

msgid

The following table compares the parameters for the dequeue functions:

queue_name

queue_name

dequeue_options

dequeue_options

message_properties

message_properties

payload

payload

msgid

msgid

The following table compares parameters for the listen functions:

agent_list

agent_list

wait

wait

agent

agent

The following table compares parameters for the array enqueue functions:

queue_name

queue_name

enqueue_options

enqopt

array_size

iters

message_properties_array

msgprop

payload_array

payload

msgid_array

msgid

The following table compares parameters for the array dequeue functions:

queue_name

queue_name

dequeue_options

deqopt

array_size

iters

message_properties_array

msgprop

payload_array

payload

msgid_array

msgid

The following table compares parameters for the agent attributes:

name

address

protocol

The following table compares parameters for the message properties:

priority

delay

expiration

correlation

attempts

recipient_list

exception_queue

enqueue_time

state

sender_id

transaction_group

original_msgid

The following table compares enqueue option attributes:

visibility

relative_msgid

sequence_deviation

The following table compares dequeue option attributes:

consumer_name

dequeue_mode

navigation

visibility

wait

msgid

correlation

Buffered Messaging

Buffered messaging is a nonpersistent messaging capability within Streams AQ, that was first available in Oracle Database 10g Release 2. Buffered messages reside in shared memory, and can be lost in the event of instance failure. Unlike persistent messages, no redo is written to disk. Buffered message enqueue and dequeue is much faster than persistent message operations. Since shared memory is limited, buffered messages may have to be spilled to disk. Flow control may be enabled to prevent applications from flooding the shared memory when the message consumers are slow or have stopped for some reason. The following functions are used for buffered messaging:

...
OCIAQMsgProperties  *msgprop;
OCIAQEnqueueOptions *enqopt;
message              msg;    /* message is an object type */
null_message         nmsg;   /* message indicator */
...
/* Allocate descriptors */
  OCIDescriptorAlloc(envhp, (dvoid **)&enqopt, OCI_DTYPE_AQENQ_OPTIONS, 0,
                     (dvoid **)0));
 
 OCIDescriptorAlloc(envhp, (dvoid **)&msgprop,OCI_DTYPE_AQMSG_PROPERTIES, 0,
                    (dvoid **)0));
 
/* Set delivery mode to buffered */
 dlvm = OCI_MSG_BUFFERED;
 OCIAttrSet(enqopt,  OCI_DTYPE_AQENQ_OPTIONS, (dvoid *)&dlvm, sizeof(ub2),
            OCI_ATTR_MSG_DELIVERY_MODE, errhp);
/* Set visibility to Immediate (visibility must always be immediate for buffered
   messages) */
vis = OCI_ENQ_ON_COMMIT;
 
OCIAttrSet(enqopt, OCI_DTYPE_AQENQ_OPTIONS,(dvoid *)&vis, sizeof(ub4),
           OCI_ATTR_VISIBILITY, errhp)
 
/* Message was an object type created earlier, msg_tdo is its Type
   Descriptor Object */
OCIAQEnq(svchp, errhp, "Test_Queue", enqopt, msgprop, msg_tdo, (dvoid **)&mesg,
         (dvoid **)&nmesg, (OCIRaw **)0, 0));
...

...
#define NMESGS 10
OCIAQMsgProperties  *msgprop[NMESGS];
OCIAQEnqueueOptions *enqopt;
message              msg[NMESGS];    /* message is an object type  */
null_message         nmsg[NMESGS];   /* message indicator */
...
iters = NMESGS;

OCIDescriptorAlloc(envhp, (dvoid **)&enqopt, OCI_DTYPE_AQENQ_OPTIONS, 0,
                   (dvoid **)0));
/* visibility must be on_commit */
vis = OCI_ENQ_ON_COMMIT;
OCIAttrSet(enqopt, OCI_DTYPE_AQENQ_OPTIONS,(dvoid *)&vis, sizeof(ub4),
           OCI_ATTR_VISIBILITY, errhp)
/* delivery mode is buffered */
dlvm = OCI_MSG_BUFFERED;
OCIAttrSet(enqopt,  OCI_DTYPE_AQENQ_OPTIONS, (dvoid *)&dlvm, sizeof(ub2),
           OCI_ATTR_MSG_DELIVERY_MODE, errhp);
for (i = 0; i < NMESGS; i++)
{
OCIDescriptorAlloc(envhp, (dvoid **)&msgprop[i] ,OCI_DTYPE_AQMSG_PROPERTIES, 0,
                   (dvoid **)0));
/* initialize the message */
...
}

OCIAQEnqArray(svchp, errhp, "Test_Queue", enqopt,  &iters, msgprop, msg_tdo,
              (dvoid **)&msg,(dvoid **)&nmsg, (OCIRaw **)0, (dvoid *)0,
              (OCICallbackAQEnq)0, (ub4)0);
...

...
OCIAQMsgProperties  *msgprop;
OCIAQDequeueOptions *deqopt;
...
OCIDescriptorAlloc(envhp, (dvoid **)&mprop, OCI_DTYPE_AQMSG_PROPERTIES, 0,
                   (dvoid **)0));
OCIDescriptorAlloc(envhp, (dvoid **)&deqopt, OCI_DTYPE_AQDEQ_OPTIONS, 0,
                   (dvoid **)0);

/* Set visibility to Immediate (visibility must always be immediate for buffered
   message operations) */
vis = OCI_ENQ_ON_COMMIT;
OCIAttrSet(deqopt, OCI_DTYPE_AQDEQ_OPTIONS,(dvoid *)&vis, sizeof(ub4),
           OCI_ATTR_VISIBILITY, errhp)
/* delivery mode is buffered */
dlvm  = OCI_MSG_BUFFERED;
OCIAttrSet(deqopt, OCI_DTYPE_AQDEQ_OPTIONS, (dvoid *)&dlvm,  sizeof(ub2),
           OCI_ATTR_MSG_DELIVERY_MODE, errhp);
/* set the consumer for which to dequeue the message (this needs to be specified
   regardless of the type of message being dequeued.
*/
consumer = "FIRST_SUBSCRIBER";
OCIAttrSet(deqopt, OCI_DTYPE_AQDEQ_OPTIONS, (dvoid *)consumer,
           (ub4)strlen((char*)consumer), OCI_ATTR_CONSUMER_NAME, errhp);
/* Dequeue the message but don't return the payload (to simplify the code
   snippet)
*/
OCIAQDeq(svchp, errhp,  "test_queue", deqopt, msgprop, msg_tdo, (dvoid **)0,
         (dvoid **)0, (OCIRaw**)0, 0);
...

#define NMESGS 10
OCIAQMsgProperties  *msgprop[NMESGS];
OCIAQDequeueOptions *deqopt;
...
 
OCIDescriptorAlloc(envhp, (dvoid **)&deqopt, OCI_DTYPE_AQDEQ_OPTIONS, 0,
                   (dvoid **)0);
/* Set visibility to Immediate (visibility must always be immediate for buffered
   message operations) */
vis = OCI_ENQ_ON_COMMIT;
OCIAttrSet(deqopt, OCI_DTYPE_AQDEQ_OPTIONS,(dvoid *)&vis, sizeof(ub4),
           OCI_ATTR_VISIBILITY, errhp)
/* delivery mode is buffered */
dlvm  = OCI_MSG_BUFFERED;
OCIAttrSet(deqopt, OCI_DTYPE_AQDEQ_OPTIONS, (dvoid *)&dlvm, sizeof(ub2),
           OCI_ATTR_MSG_DELIVERY_MODE, errhp);
/* set the consumer for which to dequeue the message (this needs to be specified
   regardless of the type of message being dequeued.
*/
consumer = "FIRST_SUBSCRIBER";
OCIAttrSet(deqopt, OCI_DTYPE_AQDEQ_OPTIONS, (dvoid *)consumer,
           (ub4)strlen((char*)consumer), OCI_ATTR_CONSUMER_NAME, errhp);
for (i = 0; i < NMESGS; i++)
{
OCIDescriptorAlloc(envhp, (dvoid **)&msgprop[i] ,OCI_DTYPE_AQMSG_PROPERTIES, 0,
                   (dvoid **)0));
}
/* don't return message payload (to simplify the code snippet */
OCIAQDeqArray(svchp, errhp, "test_queue", (OCIAQDeqOptions *)deqopt,  &iters, 0,
              mesg_tdo,  (dvoid **)0, (dvoid **)0, 0, 0, 0, 0));
...

Publish-Subscribe Registration Functions in OCI

Registration can be done in two ways:

• You register directly to the database. This way is simple and the registration will take effect immediately.

• Open Registration. You register using LDAP, from which the database receives the registration request. This is useful when the client cannot have a database connection (the client wants to register for a database open event while the database is down), or if the client wants to register for the same event or events in multiple databases at one time.

Let us next consider these two alternative ways of registration.

/* Set QOS levels */
ub4 qosflags = OCI_SUBSCR_QOS_RELIABLE | OCI_SUBSCR_QOS_PURGE_ON_NTFN;

/* Set flags in subscription handle */
(void)OCIAttrSet((dvoid *)subscrhp, (ub4)OCI_HTYPE_SUBSCRIPTION,
               (dvoid *)&qosflags, (ub4)0, (ub4)OCI_ATTR_SUBSCR_QOSFLAGS, errhp);

/* Set auto-expiration after 30 seconds */
ub4 timeout = 30;
(void)OCIAttrSet((dvoid *)subscrhp, (ub4)OCI_HTYPE_SUBSCRIPTION,
                 (dvoid *)&timeout, (ub4)0, (ub4)OCI_ATTR_SUBSCR_TIMEOUT, errhp);

ub4 port = 1581;
(void)OCIAttrSet((dvoid *)envhp, (ub4)OCI_HTYPE_ENV, (dvoid *)&port, (ub4)0,
                 (ub4)OCI_ATTR_SUBSCR_PORTNO, errhp);

(void)OCIAttrGet((dvoid *)subhp, (ub4)OCI_HTYPE_ENV, (dvoid *)&port,
                 (ub4)0, (ub4)OCI_ATTR_SUBSCR_PORTNO, errhp);

Notification Callback in OCI

The client needs to register a notification callback that gets invoked when there is some activity on the subscription for which interest has been registered. In the AQ namespace, for instance, this occurs when a message of interest is enqueued.

This callback is typically set through the OCI_ATTR_SUBSCR_CALLBACK attribute of the subscription handle.

The callback must return a value of OCI_CONTINUE and adhere to the following specification:

typedef ub4 (*OCISubscriptionNotify) ( dvoid           *pCtx,
                                       OCISubscription *pSubscrHp,
                                       dvoid           *pPayload,
                                       ub4             iPayloadLen,
                                       dvoid           *pDescriptor,
                                       ub4             iMode);

The parameters are described as follows:

pCtx (IN)

A user-defined context specified when the callback was registered.

pSubscrHp (IN)

The subscription handle specified when the callback was registered.

pPayload (IN)

The payload for this notification. For this release, only ub1 * (a sequence of bytes) for the payload is supported.

iPayloadLen (IN)

The length of the payload for this notification.

pDescriptor (IN)

The namespace-specific descriptor. Namespace-specific parameters can be extracted from this descriptor. The structure of this descriptor is opaque to the user and its type is dependent on the namespace.

The attributes of the descriptor are namespace-specific. For Advanced Queuing, the descriptor is OCI_DTYPE_AQNFY. The attributes of this descriptor are:

• Queue Name - OCI_ATTR_QUEUE_NAME

• Consumer Name - OCI_ATTR_CONSUMER_NAME

• Message Id - OCI_ATTR_NFY_MSGID

• Message Properties - OCI_ATTR_MSG_PROP

  See Also:

  "OCI and Streams Advanced Queuing"

iMode (IN)

Call-specific mode. Valid value:

• OCI_DEFAULT - executes the default call

Notification Procedure

The PL/SQL procedure that will be invoked when there is some activity on the subscription for which interest has been registered, has to be created in the database.

This procedure is typically set through the OCI_ATTR_SUBSCR_RECPT attribute of the subscription handle.

Publish-Subscribe Direct Registration Example

This example shows how system events, client notification, and Advanced Queuing work together to implement publish subscription notification.

The following PL/SQL code creates all objects necessary to support a publish-subscribe mechanism under the user schema, pubsub. In this code, the Agent snoop subscribes to messages that are published at logon events. Note that the user pubsub needs AQ_ADMINISTRATOR_ROLE and AQ_USER_ROLE privileges to use Advance Queuing functionality. The initialization parameter _SYSTEM_TRIG_ENABLED must be set to TRUE (the default) to enable triggers for system events.

----------------------------------------------------------
----create queue table for persistent multiple consumers
----------------------------------------------------------
connect pubsub/pubsub;
---- Create or replace a queue table
begin
  DBMS_AQADM.CREATE_QUEUE_TABLE(
  QUEUE_TABLE=>'pubsub.raw_msg_table', 
  MULTIPLE_CONSUMERS => TRUE,
  QUEUE_PAYLOAD_TYPE =>'RAW',
  COMPATIBLE => '8.1.5');
end;
/
----------------------------------------------------------
---- Create a persistent queue for publishing messages
----------------------------------------------------------
---- Create a queue for logon events
begin
  DBMS_AQADM.CREATE_QUEUE(QUEUE_NAME=>'pubsub.logon',
  QUEUE_TABLE=>'pubsub.raw_msg_table',
  COMMENT=>'Q for error triggers');
end;
/
----------------------------------------------------------
---- Start the queue
----------------------------------------------------------
begin
  DBMS_AQADM.START_QUEUE('pubsub.logon');
end;
/
----------------------------------------------------------
---- define new_enqueue for convenience
----------------------------------------------------------
create or replace procedure new_enqueue(queue_name  in varchar2,
                                        payload  in raw ,
correlation in varchar2 := NULL,
exception_queue in varchar2 := NULL)
as
  enq_ct     dbms_aq.enqueue_options_t;
  msg_prop   dbms_aq.message_properties_t;
  enq_msgid  raw(16);
  userdata   raw(1000);
begin
  msg_prop.exception_queue := exception_queue;
  msg_prop.correlation := correlation;
  userdata := payload;
  DBMS_AQ.ENQUEUE(queue_name,enq_ct, msg_prop,userdata,enq_msgid);
end;
/
----------------------------------------------------------
---- add subscriber with rule based on current user name, 
---- using correlation_id
----------------------------------------------------------
declare
subscriber sys.aq$_agent;
begin
  subscriber := sys.aq$_agent('SNOOP', null, null);
  dbms_aqadm.add_subscriber(queue_name => 'pubsub.logon',
                            subscriber => subscriber,
                            rule => 'CORRID = ''ix'' ');
end;
/
----------------------------------------------------------
---- create a trigger on logon on database
----------------------------------------------------------
---- create trigger on after logon
create or replace trigger systrig2
   AFTER LOGON
   ON DATABASE
   begin
     new_enqueue('pubsub.logon', hextoraw('9999'), dbms_standard.login_user);
   end;
/

----------------------------------------------------------
---- create a PL/SQL callback for notification of logon 
---- of user 'ix' on database
----------------------------------------------------------
---- 
create or replace procedure plsqlnotifySnoop(
  context raw, reginfo sys.aq$_reg_info, descr sys.aq$_descriptor,
  payload raw, payloadl number)
as
begin
 dbms_output.put_line('Notification : User ix Logged on\n');
end;
/

After the subscriptions are created, the client needs to register for notification using callback functions. The following sample code performs the necessary steps for registration. The initial steps of allocating and initializing session handles are omitted here for sake of clarity.

...
static ub4 namespace = OCI_SUBSCR_NAMESPACE_AQ;

static OCISubscription *subscrhpSnoop = (OCISubscription *)0;
static OCISubscription *subscrhpSnoopMail = (OCISubscription *)0;
static OCISubscription *subscrhpSnoopServer = (OCISubscription *)0;

/* callback function for notification of logon of user 'ix' on database */

static ub4 notifySnoop(ctx, subscrhp, pay, payl, desc, mode)
    dvoid *ctx;
    OCISubscription *subscrhp;
    dvoid *pay;
    ub4 payl;
    dvoid *desc;
    ub4 mode;
{
    printf("Notification : User ix Logged on\n");
  (void)OCIHandleFree((dvoid *)subscrhpSnoop,
            (ub4) OCI_HTYPE_SUBSCRIPTION);
    return 1;
}

static void checkerr(errhp, status)
OCIError *errhp;
sword status;
{
  text errbuf[512];
  ub4 buflen;
  sb4 errcode;

  if (status == OCI_SUCCESS) return;

  switch (status)
  {
  case OCI_SUCCESS_WITH_INFO:
    printf("Error - OCI_SUCCESS_WITH_INFO\n");
    break;
  case OCI_NEED_DATA:
    printf("Error - OCI_NEED_DATA\n");
    break;
  case OCI_NO_DATA:
    printf("Error - OCI_NO_DATA\n");
    break;
  case OCI_ERROR:
    OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,
            errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
    printf("Error - %s\n", errbuf);
    break;
  case OCI_INVALID_HANDLE:
    printf("Error - OCI_INVALID_HANDLE\n");
    break;
  case OCI_STILL_EXECUTING:
    printf("Error - OCI_STILL_EXECUTING\n");
    break;
  case OCI_CONTINUE:
    printf("Error - OCI_CONTINUE\n");
    break;
  default:
    printf("Error - %d\n", status);
    break;
  }
}

static void initSubscriptionHn (subscrhp,
                         subscriptionName,
                         func,
                         recpproto,
                         recpaddr,
                         recppres)
OCISubscription **subscrhp;
  char * subscriptionName;
  dvoid * func;
  ub4 recpproto;
  char * recpaddr;
  ub4 recppres;
{
    /* allocate subscription handle */
    (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **)subscrhp,
        (ub4) OCI_HTYPE_SUBSCRIPTION,
        (size_t) 0, (dvoid **) 0);

    /* set subscription name in handle */
    (void) OCIAttrSet((dvoid *) *subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
        (dvoid *) subscriptionName,
        (ub4) strlen((char *)subscriptionName),
        (ub4) OCI_ATTR_SUBSCR_NAME, errhp);

    /* set callback function in handle */
    if (func)
      (void) OCIAttrSet((dvoid *) *subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
          (dvoid *) func, (ub4) 0,
          (ub4) OCI_ATTR_SUBSCR_CALLBACK, errhp);

    /* set context in handle */
    (void) OCIAttrSet((dvoid *) *subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
        (dvoid *) 0, (ub4) 0,
       (ub4) OCI_ATTR_SUBSCR_CTX, errhp);

    /* set namespace in handle */
    (void) OCIAttrSet((dvoid *) *subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
        (dvoid *) &namespace, (ub4) 0,
        (ub4) OCI_ATTR_SUBSCR_NAMESPACE, errhp);

    /* set receive with protocol in handle */
    (void) OCIAttrSet((dvoid *) *subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
        (dvoid *) &recpproto, (ub4) 0,
        (ub4) OCI_ATTR_SUBSCR_RECPTPROTO, errhp);

    /* set recipient address in handle */
    if (recpaddr)
      (void) OCIAttrSet((dvoid *) *subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
          (dvoid *) recpaddr, (ub4) strlen(recpaddr),
          (ub4) OCI_ATTR_SUBSCR_RECPT, errhp);

    /* set receive with presentation in handle */
    (void) OCIAttrSet((dvoid *) *subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
        (dvoid *) &recppres, (ub4) 0,
        (ub4) OCI_ATTR_SUBSCR_RECPTPRES, errhp);

    printf("Begining Registration for subscription %s\n", subscriptionName);
    checkerr(errhp, OCISubscriptionRegister(svchp, subscrhp, 1, errhp,
        OCI_DEFAULT));
   printf("done\n");

}


int main( argc, argv)
int    argc;
char * argv[];
{
    OCISession *authp = (OCISession *) 0;

/*****************************************************
Initialize OCI Process/Environment
Initialize Server Contexts
Connect to Server
Set Service Context
******************************************************/

/* Registration Code Begins */
/* Each call to initSubscriptionHn allocates
   and Initialises a Registration Handle */

/* Register for OCI notification */
    initSubscriptionHn(    &subscrhpSnoop,    /* subscription handle*/
    (char*) "PUBSUB.LOGON:SNOOP", /* subscription name */
                                  /*<queue_name>:<agent_name> */
        (dvoid*)notifySnoop,  /* callback function */
        OCI_SUBSCR_PROTO_OCI, /* receive with protocol */
        (char *)0, /* recipient address */
        OCI_SUBSCR_PRES_DEFAULT); /* receive with presentation */

/* Register for email notification */
    initSubscriptionHn(    &subscrhpSnoopMail,  /* subscription handle */
     (char*) "PUBSUB.LOGON:SNOOP",              /* subscription name */ 
                                                /* <queue_name>:<agent_name> */
        (dvoid*)0, /* callback function */
        OCI_SUBSCR_PROTO_MAIL, /* receive with protocol */
        (char*)  "longying.zhao@oracle.com", /* recipient address */
        OCI_SUBSCR_PRES_DEFAULT); /* receive with presentation */

/* Register for server to server notification */
    initSubscriptionHn(    &subscrhpSnoopServer, /* subscription handle */
       (char*)  "PUBSUB.LOGON:SNOOP",            /* subscription name */
                                                 /* <queue_name>:<agent_name> */
        (dvoid*)0, /* callback function */
        OCI_SUBSCR_PROTO_SERVER, /* receive with protocol */
         (char*) "pubsub.plsqlnotifySnoop", /* recipient address */
        OCI_SUBSCR_PRES_DEFAULT); /* receive with presentation */

    checkerr(errhp, OCITransCommit(svchp, errhp, (ub4) OCI_DEFAULT));

/*****************************************************
The Client Process does not need a live Session for Callbacks
End Session and Detach from Server
******************************************************/

    OCISessionEnd ( svchp,  errhp, authp, (ub4) OCI_DEFAULT);

    /* detach from server */
    OCIServerDetach( srvhp, errhp, OCI_DEFAULT);

   while (1)    /* wait for callback */
    sleep(1);
}

If user IX logs on to the database, the client is notified by e-mail, and the callback function notifySnoop is called. An e-mail notification will be sent to the address xyz@company.com and the PL/SQL procedure plsqlnotifySnoop will also be called in the database.

Publish-Subscribe LDAP Registration Example

The following code fragment illustrates how to do LDAP registration. Please read all the program comments:

...

  /* TO use LDAP registration feature, OCI_EVENTS | OCI_USE_LDAP must be set 
     in OCIInitialize: */
                                                                             
  (void) OCIInitialize((ub4) OCI_EVENTS|OCI_OBJECT|OCI_USE_LDAP, (dvoid *)0,
                       (dvoid * (*)(dvoid *, size_t)) 0,
                       (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                       (void (*)(dvoid *, dvoid *)) 0 );

...

  /* set LDAP attributes in the environment handle */

  /* LDAP host name */
  (void) OCIAttrSet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)"yow", 3,
                    OCI_ATTR_LDAP_HOST, (OCIError *)errhp);

  /* LDAP server port */ 
  ldap_port = 389;
  (void) OCIAttrSet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)&ldap_port,
                    (ub4)0, OCI_ATTR_LDAP_PORT, (OCIError *)errhp);

  /* bind DN of the client, normally the enterprise user name */
  (void) OCIAttrSet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)"cn=orcladmin",
                    12, OCI_ATTR_BIND_DN, (OCIError *)errhp);

  /* password of the client */
  (void) OCIAttrSet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)"welcome",
                    7, OCI_ATTR_LDAP_CRED, (OCIError *)errhp);

  /* authentication method is "simple", username/password authentication */
  ldap_auth = 0x01;
  (void) OCIAttrSet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)&ldap_auth,
                    (ub4)0, OCI_ATTR_LDAP_AUTH, (OCIError *)errhp);

  /* adminstrative context: this is the DN above cn=oraclecontext */
  (void) OCIAttrSet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)"cn=acme,cn=com",
                    14, OCI_ATTR_LDAP_CTX, (OCIError *)errhp);

...

  /* retrieve the LDAP attributes from the environment handle */

  /* LDAP host */
  (void) OCIAttrGet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)&buf, 
                    &szp,  OCI_ATTR_LDAP_HOST,  (OCIError *)errhp);

  /* LDAP server port */
  (void) OCIAttrGet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)&intval, 
                    0,  OCI_ATTR_LDAP_PORT,  (OCIError *)errhp);

  /* client binding DN */
  (void) OCIAttrGet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)&buf, 
                    &szp,  OCI_ATTR_BIND_DN,  (OCIError *)errhp);

  /* client password */
  (void) OCIAttrGet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)&buf, 
                    &szp,  OCI_ATTR_LDAP_CRED,  (OCIError *)errhp);

  /* adminstrative context */
  (void) OCIAttrGet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)&buf, 
                    &szp,  OCI_ATTR_LDAP_CTX,  (OCIError *)errhp);

  /* client authentication method */
  (void) OCIAttrGet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)&intval, 
                    0,  OCI_ATTR_LDAP_AUTH,  (OCIError *)errhp);
  
  ...

  /* to set up the server DN descriptor in the subscription handle */

  /* allocate a server DN descriptor, dn is of type "OCIServerDNs **", 
     subhp is of type "OCISubscription **" */
  (void) OCIDescriptorAlloc((dvoid *)envhp, (dvoid **)dn, 
                         (ub4) OCI_DTYPE_SRVDN, (size_t)0, (dvoid **)0);

  /* now *dn is the server DN descriptor, add the DN of the first database 
     that we want to register */
  (void) OCIAttrSet((dvoid *)*dn, (ub4) OCI_DTYPE_SRVDN, 
                    (dvoid *)"cn=server1,cn=oraclecontext,cn=acme,cn=com",
                    42, (ub4)OCI_ATTR_SERVER_DN, errhp);
  /* add the DN of another database in the descriptor */
  (void) OCIAttrSet((dvoid *)*dn, (ub4) OCI_DTYPE_SRVDN, 
                    (dvoid *)"cn=server2,cn=oraclecontext,cn=acme,cn=com",
                    42, (ub4)OCI_ATTR_SERVER_DN, errhp);

  /* set the server DN descriptor into the subscription handle */
  (void) OCIAttrSet((dvoid *) *subhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
                 (dvoid *) *dn, (ub4)0, (ub4) OCI_ATTR_SERVER_DNS, errhp);

  ...

  /* now we will try to get the server DN information from the subscription
     handle */
 
  /* first, get the server DN descriptor out */
  (void) OCIAttrGet((dvoid *) *subhp, (ub4) OCI_HTYPE_SUBSCRIPTION, 
                    (dvoid *)dn, &szp, OCI_ATTR_SERVER_DNS, errhp);

  /* then, get the number of server DNs in the descriptor */
  (void) OCIAttrGet((dvoid *) *dn, (ub4)OCI_DTYPE_SRVDN, (dvoid *)&intval,
                    &szp, (ub4)OCI_ATTR_DN_COUNT, errhp);

  /* allocate an array of char * to hold server DN pointers returned by
     oracle */
    if (intval)
    {
      arr = (char **)malloc(intval*sizeof(char *));
      (void) OCIAttrGet((dvoid *)*dn, (ub4)OCI_DTYPE_SRVDN, (dvoid *)arr,
                        &intval, (ub4)OCI_ATTR_SERVER_DN, errhp);
    }

  /* OCISubscriptionRegister() calls have two modes: OCI_DEFAULT and 
     OCI_REG_LDAPONLY. If OCI_DEFAULT is used, there should be only one
     server DN in the server DN descriptor. The registration request will
     be sent to the database. If a database connection is not available,
     the registration request will be detoured to the LDAP server. On the 
     other hand, if mode OCI_REG_LDAPONLY is used the registration request
     will be directly sent to LDAP. This mode should be used when there are 
     more than one server DNs in the server DN descriptor, or we are sure
     that a database connection is not available.

     In this example, two DNs are entered; so we should use mode 
     OCI_REG_LDAPONLY in register. */
  OCISubscriptionRegister(svchp, subhp, 1, errhp, OCI_REG_LDAPONLY);

  ...

  /* as OCISubscriptionRegister(), OCISubscriptionUnregister() also has
     mode OCI_DEFAULT and OCI_REG_LDAPONLY. The usage is the same. */

  OCISubscriptionUnRegister(svchp, *subhp, errhp, OCI_REG_LDAPONLY);
}
...

Registering for Database Change Notification

The calling session must have the CHANGE NOTIFICATION system privilege and SELECT privileges on all objects that it attempts to register. A registration is a persistent entity that is recorded in the database, and is visible to all instances of Real Application Clusters (RAC). Transactions that modify registered objects in any instance of the RAC generate notification.

The objects referenced are identified during the execution of the queries. If the query refers to a non-materialized view, then the underlying tables on top of which the view is based are registered. Queries based on 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:

  notification_callback (dvoid *ctx, OCISubscription *subscrhp, dvoid *payload,
                         ub4 paylen, dvoid *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 a RAC can be used to send and retrieve change 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.

• 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 OCIStatement handle for subsequent executions, it must repopulate the registration handle attribute of the statement handle. A binding of a subscription handle to a statement handle is only permitted when the statement is a query (determined at execute time). If a DML statement is executed as part of the execution, then an exception is issued.

Subscription Handle Attributes for Change Notification

The subscription handle attributes for change notification are described next. The attributes can be divided into generic (which are common to all subscriptions) and namespace- specific attributes particular to change notification. The IN attributes on the statement handle can only be modified before the registration is created.

OCI_ATTR_SUBSCR_NAMESPACE (generic) - This must be set to OCI_SUBSCR_NAMESPACE_DBCHANGE for subscription handles.

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

When a new change 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, a parallel example is presented here:

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 change 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. 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 change notification messages. When this option is chosen, ROWID level granularity of information will not be available in the notifications even if OCI_ATTR_CHNF_ROWID was set to TRUE

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.

Change Notification Descriptor

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

OCI_ATTR_CHDES_DBNAME (OCIText *) - Name of the database (source of the change notification)

OCI_ATTR_CHDES_NFYTYPE - Flags describing the notification type:

• OCI_EVENT_NONE - No further information about the change notification

• OCI_EVENT_STARTUP - Startup

• OCI_EVENT_SHUTDOWN - Instance shutdown

• OCI_EVENT_SHUTDOWN_ANY - Any instance shutdown - Real Application Clusters (RAC)

• OCI_EVENT_DEREG - Unregistered or timed out

• OCI_EVENT_OBJCHANGE - Object change notification

OCI_ATTR_CHDES_TABLE_CHANGES - A collection type describing operations on tables of datatype (OCIColl *). Each element of the collection is a table change descriptor of type OCI_DTYPE_TABLE_CHDES which has the following attributes:

• OCI_ATTR_CHDES_TABLE_NAME (OCIText *) - 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:

  • OCI_OPCODE_ALLROWS - The table is completely invalidated.

  • 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_OPCODE_ALTER - Table altered (schema change) - this includes DDL statements and internal operations that cause row migration.

  • 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 change descriptor of type OCI_DTYPE_ROW_CHDES which has the following attributes:

  • OCI_ATTR_CHDES_ROW_ROWID (OraText *) - String representation of a ROWID.

  • OCI_ATTR_CHDES_ROW_OPFLAGS - Reflects the operation type: INSERT, UPDATE, DELETE, or OTHER.

Database Change Notification Example

The following is a simple OCI program. See the comments.

/***************************************************************************
 The following is an example of an OCI program that creates a registration
 involving the HR.EMPLOYEES and HR.DEPARTMENTS tables and waits to
 receive a Notification. After compilation the program can be executed
 at the command prompt as
 ./regquery
 
  Sample output after registration:
  --------------------------------
  Allocated handles
  Created Registration
  Registered query SELECT MANAGER_ID from EMPLOYEES where EMPLOYEE_ID=206
  Registered query SELECT department_id  from DEPARTMENTS where DEPARTMENT_NAME =
   'Payroll'
  Waiting for Notification to arrive.
 
  Now we can update the EMPLOYEES table and receive the notification. From
  sqlplus perform the following 
 
  UPDATE EMPLOYEES SET SALARY=SALARY WHERE EMPLOYEE_ID = 200;
  COMMIT;
 
  Program output from notification handler:
  -----------------------------------------
  Received Notification
  EMPLOYEE table modified 
  Number of rows modified is 1
  HR.EMPLOYEES table has been modified in row AAAKCeAABAAAKC1AAC 
  Executing stmt select * from HR.EMPLOYEES where rowid='AAAKCeAABAAAKC1AAC'
  First column of modified row is 200
 
*******************************************************************************/
 
#ifndef S_ORACLE
# include<s.h>
#endif
 
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>
 
#define MAXSTRLENGTH 1024
 
static int notifications_processed =0;
static void checker();
 
 
 
int main(int argc, char **argv)
{
  NotificationDriver();
}
 
 
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;
  }
}
 
void my_notification_callback(ctx, subscrhp, payload, payl, descriptor, mode)
dvoid *ctx;
OCISubscription *subscrhp;
dvoid *payload;
ub4 *payl;
dvoid *descriptor;
ub4 mode;
{
  dvoid *change_descriptor =  descriptor;
  ub4   notify_type;
  OCIEnv *envhp;
  OCIError *errhp;
  OCIServer *srvhp;
  OCISvcCtx *svchp;
  OCISession *usrhp;
  OCIStmt *stmthp;
  dvoid *tmp;
 
  dvoid *elemind = (dvoid *)0;
  OCIColl *table_changes = (OCIColl  *)0 ;  
                   /* Collection of pointers to table chg descriptors */
  dvoid **table_descp;          /* Pointer to Table Change Descriptor */
  dvoid *table_desc;              /*  Table Change Descriptor */
  ub4 num_rows = 0;
  ub4 table_op;
  ub4 num_tables = 0;
  ub2 i, j;
  boolean exist;
  text *table_name;
 
  printf("Received Notification\n");
 
 
 
  /* Initialize environment and allocate Error Handle. 
     Note that the environment has to be initialized in object mode
     since we might be operating on collections.
  */
  OCIEnvCreate( (OCIEnv **) &envhp, 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);
 
  /* Get the Notification Type */
  checker(errhp,
          OCIAttrGet( change_descriptor, OCI_DTYPE_CHDES, &notify_type, NULL, 
                OCI_ATTR_CHDES_NFYTYPE, errhp));
  if (notify_type == OCI_EVENT_SHUTDOWN)
    printf("Shutdown Notification\n");
  else if (notify_type == OCI_EVENT_DEREG)
    printf("Registration Removed\n");
 
  if (notify_type != OCI_EVENT_OBJCHANGE)
  {
    OCIHandleFree((dvoid *)envhp, OCI_HTYPE_ENV);
    OCIHandleFree((dvoid *)errhp, OCI_HTYPE_ERROR);
    return;
  }
 
  /* The below code is only executed if the notification is of type
     OCI_EVENT_OBJCHANGE
   */
  /* 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);
 
  /* Allocate a statement handle */
  OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp,
                  (ub4) OCI_HTYPE_STMT, 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);
 
  checker(errhp, OCIServerAttach( srvhp, errhp, (text *) 0, (sb4) 0, 
          (ub4) OCI_DEFAULT));
 
   /* allocate a SESSION  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));
  OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX,
             (dvoid *)usrhp, (ub4)0, OCI_ATTR_SESSION, errhp);
 
  /* Obtain the collection of table change descriptors */
  checker(errhp,OCIAttrGet(change_descriptor, OCI_DTYPE_CHDES, &table_changes, 
                           NULL, OCI_ATTR_CHDES_TABLE_CHANGES, errhp));
 /* Obtain the size of the collection (i.e number of tables modified) */
  if (table_changes)
    checker(errhp,OCICollSize(envhp, errhp, (CONST OCIColl *) table_changes,
            &num_tables));
   else
     num_tables =0;
 
  /* For each element of the collection, extract the table name of the modified
     table */
  for (i=0; i < num_tables; i++)
  {
    OCIColl *row_changes = (OCIColl  *)0;
    /* Collection of pointers to row chg. Descriptors */
    dvoid **row_descp;            /*  Pointer to Row Change Descriptor */
    dvoid   *row_desc;               /*   Row Change Descriptor */
    text *row_id;
    ub4 rowid_size;
    text *ocistmt;
    OCIDefine *defnp1 = (OCIDefine *)0;
    char *outstr;
 
    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, &table_name, 
                             NULL,
                             OCI_ATTR_CHDES_TABLE_NAME, errhp));
    if (strcmp(table_name, "HR.EMPLOYEES") == 0)
      printf("EMPLOYEE table modified \n");
    else if (strcmp(table_name, "HR.DEPARTMENTS") == 0)
      printf("DEPARTMENTS table modified \n");
 
    checker(errhp,OCIAttrGet (table_desc, OCI_DTYPE_TABLE_CHDES, 
                              (dvoid *)&table_op, NULL,
                               OCI_ATTR_CHDES_TABLE_OPFLAGS, errhp));
 
    /* If the ROWID granularity of info not available, move-on. Rowids
       can be rolled up into a full table notification if too many rows
       were updated on a single table or insufficient shared memory on
       the server side to hold rowids
     */
    if (table_op & OCI_OPCODE_ALLROWS)
    {
       printf("Full Table Invalidation\n");
       continue;   
     }
 
     /* Obtain the collection of  ROW CHANGE descriptors */
    checker(errhp,OCIAttrGet (table_desc, OCI_DTYPE_TABLE_CHDES, &row_changes,
                               NULL, OCI_ATTR_CHDES_TABLE_ROW_CHANGES, errhp));
 
    if (row_changes)
      checker(errhp,OCICollSize(envhp, errhp, row_changes, &num_rows));
    else
      num_rows =0;
 
    printf ("Number of rows modified is %d\n", num_rows);
    fflush(stdout);
    for (j=0; j<num_rows; j++)
    {
      OCICollGetElem(envhp, errhp, (OCIColl *) row_changes,
                     j, &exist, &row_descp, &elemind);
      row_desc = *row_descp;
 
      OCIAttrGet (row_desc, OCI_DTYPE_ROW_CHDES, (dvoid *)&row_id,
                  &rowid_size, OCI_ATTR_CHDES_ROW_ROWID, errhp);
      printf ("%s table has been modified in row %s \n", table_name, row_id);
      fflush(stdout);
      ocistmt = (text *)malloc(MAXSTRLENGTH*sizeof(char));
 
      /* QUERY FROM DATABASE TO VIEW CONTENTS OF CHANGED ROW */
      sprintf (ocistmt, "select * from %s where rowid='%s'", table_name, row_id);
      printf("Executing stmt %s\n", ocistmt);
 
      /* prepare query statement*/
      checker(errhp,OCIStmtPrepare(stmthp, errhp, ocistmt, 
                                   (ub4)strlen((char *)ocistmt),
                                   (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));
      outstr = (char *)malloc(MAXSTRLENGTH*sizeof(char));
 
      checker(errhp,OCIDefineByPos(stmthp, &defnp1, errhp, 1, (dvoid *)outstr, 
                                   MAXSTRLENGTH * sizeof(char),
                                   SQLT_STR, (dvoid *)0, (ub2 *)0, (ub2 *)0, 
                                   OCI_DEFAULT)); 
 
      /* execute the statement */
      checker(errhp,OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4) 0, 
                                   (CONST OCISnapshot *) NULL, 
                                   (OCISnapshot *) NULL, OCI_DEFAULT));
 
      printf("First column of modified row is %s\n", outstr);
    }  /* Loop for j in 1..numrows */
 
  } /* Loop for I in 1..numtables */
 
  /* End session and detach from server */
  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);
 
  notifications_processed++;
}  /* End function my_notification_callback */
 
 
/* The following routine creates registrations and waits for notifications. */
void NotificationDriver()
{
  OCISvcCtx *svchp;
  OCIError *errhp;
  OCISession *usrhp;
  OCIStmt *stmthp;
  OCIEnv *envhp;
  OCIServer *srvhp;
  text *username;
  OCISubscription *subscrhp;
  ub4 namespace = OCI_SUBSCR_NAMESPACE_DBCHANGE;
  ub4 timeout = 1800;
  text dname[MAXSTRLENGTH];
  OCIDefine *defnp1 = (OCIDefine *)0;
  OCIDefine *defnp2 = (OCIDefine *)0;
  OCIDefine *defnp3 = (OCIDefine *)0;
  OCIDefine *defnp4 = (OCIDefine *)0;
  OCIDefine *defnp5 = (OCIDefine *)0;
  int mgr_id =0;
  int dept_id =0;
  dvoid *tmp;
  boolean rowids_needed = TRUE;
 
  char query_text1[] = "SELECT MANAGER_ID from EMPLOYEES where EMPLOYEE_ID=206";
  char query_text2[] =
  "SELECT department_id  from DEPARTMENTS where DEPARTMENT_NAME = 'Payroll'";
  /* DEPARTMENTS  is also a cached object */
 
  /* Initialize the environment. The environment has to be initialized
     with OCI_EVENTS and OCI_OBJECTS to create a change 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));
   OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, (ub4) OCI_HTYPE_SVCCTX,
                   52, (dvoid **)0);
  /* 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);
 
  /* allocate subscription handle */
  OCIHandleAlloc ((dvoid *) envhp, (dvoid **) &subscrhp, OCI_HTYPE_SUBSCRIPTION,
                   (size_t) 0,
                    (dvoid **) 0);
  printf("Allocated handles\n");
 
  /* set the namespace to DBCHANGE */
  OCIAttrSet (subscrhp, OCI_HTYPE_SUBSCRIPTION,  (dvoid *) &namespace, 
              sizeof(ub4), 
              OCI_ATTR_SUBSCR_NAMESPACE, errhp);
  /* Associate a notification callback */
  OCIAttrSet (subscrhp, OCI_HTYPE_SUBSCRIPTION,
              (void *)my_notification_callback,  0, 
              OCI_ATTR_SUBSCR_CALLBACK, errhp);
 
  /* Allow extraction of rowid information */
  checker(errhp, OCIAttrSet (subscrhp, OCI_HTYPE_SUBSCRIPTION,
                  (dvoid *)&rowids_needed, sizeof(ub4), 
                  OCI_ATTR_CHNF_ROWIDS, errhp));
 
  /* Can optionally provide a client specific context using 
     OCI_ATTR_SUBSCR_CTX */
 
  /* Set a timeout value of half an hour */
  OCIAttrSet(subscrhp, OCI_HTYPE_SUBSCRIPTION, 
             (dvoid *)&timeout, 0, OCI_ATTR_SUBSCR_TIMEOUT, errhp);
 
  /* Create a new registration in the  DBCHANGE namespace */
  checker(errhp,OCISubscriptionRegister(svchp, &subscrhp, 1, errhp, OCI_DEFAULT));
 
  printf("Created Registration\n");
/* Multiple queries can now be associated with the subscription */
 
  /* Prepare the statement */
  checker(errhp,OCIStmtPrepare (stmthp, errhp, query_text1,
                                strlen(query_text1), OCI_V7_SYNTAX, OCI_DEFAULT));
  checker(errhp,OCIDefineByPos(stmthp, &defnp1, errhp, 1, (dvoid *)&mgr_id, 
                               sizeof(mgr_id), SQLT_INT, (dvoid *)0, 
                               (ub2 *)0, (ub2 *)0, OCI_DEFAULT));
  /* Associate the statement with the subscription handle */
  checker(errhp,OCIAttrSet (stmthp, OCI_HTYPE_STMT, subscrhp, 0,
                            OCI_ATTR_CHNF_REGHANDLE, errhp));
 
  /* Execute the statement The execution of the statement  performs the object
 registration */
  checker(errhp,OCIStmtExecute (svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                 (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL ,
                 OCI_DEFAULT));
  printf("Registered query %s\n", query_text1);
 
  /* Use the same registration for the departments table */
  checker(errhp,OCIStmtPrepare (stmthp, errhp, query_text2,
                strlen(query_text2), OCI_V7_SYNTAX, OCI_DEFAULT));
 
  checker(errhp,OCIDefineByPos(stmthp, &defnp3,
               errhp, 1, (dvoid *)&dept_id, sizeof(dept_id),
                SQLT_INT, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT));
  checker(errhp,OCIAttrSet (stmthp, OCI_HTYPE_STMT, subscrhp, 0,
                     OCI_ATTR_CHNF_REGHANDLE, errhp));
  checker(errhp,OCIStmtExecute (svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                 (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL ,
                 OCI_DEFAULT));
  printf("Registered query %s\n", query_text2);
 
  printf("Waiting for Notifications to arrive\n");
  /* Wait for notifications to arrive */
  while (notifications_processed != 1);
 
  /* Unregister the subscription */
  checker(errhp, 
           OCISubscriptionUnRegister(svchp,subscrhp, errhp, OCI_DEFAULT));
 
  /* End the session and detach from the server */
  checker(errhp, OCISessionEnd(svchp, errhp, usrhp, (ub4) 0));
  checker(errhp, OCIServerDetach(srvhp, errhp, (ub4) OCI_DEFAULT));
 
  /* Free all the handles */
  OCIHandleFree((dvoid *)subscrhp, OCI_HTYPE_SUBSCRIPTION);
  OCIHandleFree((dvoid *)stmthp, OCI_HTYPE_STMT);
  OCIHandleFree((dvoid *) srvhp, (ub4) OCI_HTYPE_SERVER);
  OCIHandleFree((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX);
  OCIHandleFree((dvoid *) usrhp, (ub4) OCI_HTYPE_SESSION);
  OCIHandleFree((dvoid *) errhp, (ub4) OCI_HTYPE_ERROR);
  OCIHandleFree((dvoid *) envhp, (ub4) OCI_HTYPE_ENV);
}

Examples of Startup and Shutdown in OCI

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) will be 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((dvoid *) envhp, (dvoid **) &admhp,
         (ub4) OCI_HTYPE_ADMIN, (size_t) 0, (dvoid **) 0));

/* Set attribute pfile in the admin handle 
(do not do this if you want to use the spfile) */
checkerr (errhp, OCIAttrSet( (dvoid *) admhp, (ub4) OCI_HTYPE_ADMIN,
          (dvoid *) 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((dvoid *) 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:

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

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

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

/*  Example 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));
...