Skip Headers

Oracle Call Interface Programmer's Guide
Release 2 (9.2)

Part Number A96584-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

9
OCI Programming Advanced Topics

This chapter introduces advanced programming topics, including the following:

Thread Safety

The thread safety feature of the Oracle database server and OCI libraries allows developers to use the OCI in a multithreaded environment. With thread safety, OCI code can be reentrant, with multiple threads of a user program making OCI calls without side effects from one thread to another.


Note:

Thread safety is not available on every platform. Check your Oracle system-specific documentation for more information.


The following sections describe how you can use the OCI to develop multithreaded applications.

Advantages of OCI Thread Safety

The implementation of thread safety in the Oracle Call Interface has the following benefits and advantages:

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 (also called 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 an Oracle database. The applications server (agent) is very well suited to being a multithreaded application server, with each thread serving a client application. In an Oracle environment this application server is an OCI or precompiler program.

Basic Concepts of Multithreaded Development

Threads are lightweight processes that exist within a larger process. Threads share the same code and data segments but have their own program counters, machine registers, and stack. Global and static variables are common to all threads, and a mutual exclusivity mechanism may be required to manage access to these variables from multiple threads within an application.

Once spawned, threads run asynchronously to one another. They can access common data elements and make OCI calls in any order. Because of this shared access to data elements, a mechanism is required to maintain the integrity of data being accessed by multiple threads.

The mechanism to manage data access takes the form of mutexes (mutual exclusivity locks), that ensure that no conflicts arise between multiple threads that are accessing shared resources within an application. In OCI, mutexes are granted on a per-environment-handle basis.

Implementing Thread Safety

In order to take advantage of thread safety, an application must be running on a thread-safe platform. Then the application must tell the OCI layer that the application is running in multithreaded mode, by specifying OCI_THREADED for the mode parameter of the opening call to OCIEnvCreate().

Once OCIEnvCreate() is called with OCI_THREADED, all subsequent calls to OCIEnvCreate() must also be made with OCI_THREADED.


Note:

Applications running on non-thread-safe platforms should not pass a value of OCI_THREADED to OCIInitialize() or OCIEnvCreate().


If an application is single-threaded, whether or not the platform is thread-safe, the application should pass a value of OCI_DEFAULT to OCIInitialize() or OCIEnvCreate(). Single-threaded applications which run in OCI_THREADED mode may incur performance hits.

If a multithreaded application is running on a thread-safe platform, the OCI library will manage mutexing for the application on a per-environment-handle basis. If the application programmer desires, this application can override this feature and maintain its own mutexing scheme. This is done by specifying a value of OCI_NO_MUTEX to the OCIEnvCreate() call.

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

  1. If an application has multiple environment handles, but each only has one thread (one session exists in each environment handle), no mutexing is required.
  2. If an application running in OCI_THREADED mode maintains one or more environment handles, each of which has multiple connections, it also has the following options:
    • Pass a value of OCI_NO_MUTEX for the mode of OCIEnvCreate(). In this case the application must mutex OCI calls by made on the same environment handle itself. This has the advantage that the mutexing scheme can be optimized based on 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 to OCIEnvCreate(). In this case, 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 could be blocked while the other finishes processing at the server.


Mixing 7.x and Later Release OCI Calls

If an application is mixing later release and 7.x OCI calls, and the application has been initialized as thread-safe (with the appropriate calls of the later release), it is not necessary to call opinit() to achieve thread safety. The application will get 7.x behavior on any subsequent 7.x function calls.

Multithreading Example

See cdemothr.c in the demo directory for an example of a multithreading application.

The OCIThread Package

The OCIThread package provides a number of commonly used threading primitives for use by Oracle customers. It offers a portable interface to threading capabilities native to various platforms. It does not implement threading on platforms that do not have native threading capability.

OCIThread does not provide a portable implementation of multithreaded facilities. It only serves as a set of portable covers for native multithreaded facilities. Therefore, platforms that do not have native support for multithreading will only be able to support a limited implementation of OCIThread. As a result, products that rely on all of OCIThread's functionality will not port to all platforms. Products that must port to all platforms must use only a subset of OCIThread's functionality. This issue is discussed further in later sections of this document.

The OCIThread API is split into three main parts. Each part is described briefly here. The following subsections describe each in greater detail.

See Also:

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 properly initialized before any of its functionality can be used. OCIThread's process initialization function, OCIThreadProcessInit(), must be called with care, as described below.

The observed behavior of the initialization and termination functions is the same regardless of whether OCIThread is in single-threaded or multithreaded environment.

OCIThread Context

Most calls to OCIThread functions take the OCI environment or user session handle as a parameter. The OCIThread context is part of the OCI environment or user session handle and it must be initialized by calling OCIThreadInit(). Termination of the OCIThread context occurs by calling OCIThreadTerm().


Note:

The OCIThread context is an opaque data structure. Do not attempt to examine the contents of the context.


The following functions are used to implement thread initialization and termination. Detailed descriptions of each function can be found in "Thread Management Functions" .

Function Purpose

OCIThreadProcessInit()

Performs OCIThread process initialization.

OCIThreadInit()

Initializes OCIThread context.

OCIThreadTerm()

Terminates the OCIThread layer and frees context memory.

OCIThreadIsMulti()

Tells the caller whether the application is running in a multithreaded environment or a single-threaded environment.

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 on multithreaded and single-threaded platforms.

OCIThreadMutex

The type OCIThreadMutex is used to represent a mutual exclusion lock (mutex). A mutex is typically used for one of two purposes:

Mutex pointers can be declared as parts of client structures or as stand-alone variables. Before they can be used, they must be initialized using OCIThreadMutexInit(). Once they are no longer needed, they must be destroyed using OCIThreadMutexDestroy(). A mutex pointer must not be used after it is destroyed.

A thread can acquire a mutex by using OCIThreadMutexAcquire(). This ensures that only one thread at a time is allowed to hold a given mutex. A thread that holds a mutex can release it by calling OCIThreadMutexRelease().

OCIThreadKey

The type OCIThreadKey can be thought of as a process-wide variable that has a thread-specific value. What this means is that all the threads in a process can use any given key. However, each thread can examine or modify that key independently of the other threads. The value that a thread sees when it examines the key will always be the same as the value that it last set for the key. It will not see any values set for the key by the other threads.

The type of the value held by a key is a dvoid * generic pointer.

Keys can be created using OCIThreadKeyInit(). When a key is created, its value is initialized to NULL for all threads.

A thread can set a key's value using OCIThreadKeySet(). A thread can get a key's value using OCIThreadKeyGet().

The OCIThread key functions will save and retrieve data specific to the thread. When clients maintain a pool of threads and assign the threads to different tasks, it may not be appropriate for a task to use OCIThread key functions to save data associated with it.

Here is a scenario of how things can fail: A thread is assigned to execute the initialization of a task. During the initialization, the task stored some data related to it in the thread using OCIThread key functions.

After the initialization, the thread is returned back to the threads pool. Later, the threads pool manager assigned another thread to perform some operations on the task, and the task needs to retrieve the data it stored earlier in initialization. Since the task is running in another thread, it will not be able to retrieve the same data. Applications that use thread pools should be aware of this and be cautious when using OCIThread key functions.

OCIThreadKeyDestFunc

OCIThreadKeyDestFunc is the type of a pointer to a key's destructor routine. Keys can be associated with a destructor routine when they are created (see OCIThreadKeyInit()).

A key's destructor routine will be called whenever a thread that has a non-NULL value for the key terminates.

The destructor routine returns nothing and takes one parameter. The parameter will be the value that was set for key when the thread terminated.

The destructor routine is guaranteed to be called on a thread's value in the key after the termination of the thread and before process termination. No more precise guarantee can be made about the timing of the destructor routine call; thus no code in the process may assume any post-condition of the destructor routine. In particular, the destructor is not guaranteed to execute before a join call on the terminated thread returns.

OCIThreadId

OCIThreadId is the type that will be used to identify a thread. At any given time, no two threads will ever have the same OCIThreadId. However, OCIThreadId values can be recycled; that is, once a thread dies, a new thread may be created that has the same OCIThreadId as the one that died. In particular, the thread ID must uniquely identify a thread T within a process, and it must be consistent and valid in all threads U of the process for which it can be guaranteed that T is running concurrently with U. The thread ID for a thread T must be retrievable within thread T. This will be done using OCIThreadIdGet().

The OCIThreadId type supports the concept of a NULL thread ID: the NULL thread ID will never be the same as the ID of an actual thread.

Passive Threading Functions

The following functions are used to manipulate mutexes, thread keys and thread IDs.

See Also:

Complete descriptions of each function can be found in "Thread Management Functions" .

Function Purpose

OCIThreadMutexInit()

Allocates and initializes a mutex.

OCIThreadMutexDestroy()

Destroys and deallocates a mutex.

OCIThreadMutexAcquire()

Acquires a mutex for the thread in which it is called.

OCIThreadMutexRelease()

Releases a mutex.

OCIThreadKeyInit()

Allocates and initializes a key.

OCIThreadKeyDestroy()

Destroys and deallocates a key.

OCIThreadKeyGet()

Gets the calling thread's current value for a key.

OCIThreadKeySet()

Sets the calling thread's value for a key.

OCIThreadIdInit()

Allocates and initializes a thread ID.

OCIThreadIdDestroy()

Destroys and deallocates a thread ID.

OCIThreadIdSet()

Sets on thread ID to another.

OCIThreadIdSetNull()

Nulls a thread ID.

OCIThreadIdGet()

Retrieves a thread ID for the thread in which it is called.

OCIThreadIdSame()

Determines if two thread IDs represent the same thread.

OCIThreadIdNull()

Determines if a thread ID is NULL.

Active Threading Primitives

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

Active primitives should only be called by code running in a multithreaded environment. You can call OCIThreadIsMulti() to determine whether the environment is multithreaded or single-threaded.

OCIThreadHandle

Type OCIThreadHandle is used to manipulate a thread in the active primitives: OCIThreadJoin() and OCIThreadClose(). A thread handle opened by OCIThreadCreate() must be closed in a matching call to OCIThreadClose(). A thread handle is invalid after the call to OCIThreadClose().

The distinction between a thread ID and a thread handle in OCIThread usage follows the distinction between the thread ID and the thread handle on Windows NT. On many platforms, the underlying native types are the same.

Active Threading Functions

The following functions are used to implement active threading.

See Also:

Complete descriptions of the functions are available in "Thread Management Functions"

Function Purpose

OCIThreadHndInit()

Allocates and initializes a thread handle.

OCIThreadHndDestroy()

Destroys and deallocates a thread handle.

OCIThreadCreate()

Creates a new thread.

OCIThreadJoin()

Allows the calling thread to join with another.

OCIThreadClose()

Closes a thread handle.

OCIThreadHandleGet()

Retrieves a thread handle.

Using the OCIThread Package

This section summarizes some of the more important details relating to the use of OCIThread.

Process initialization

OCIThread only requires that the process initialization function (OCIThreadProcessInit()) be called when OCIThread is being used in a multithreaded application. Failing to call OCIThreadProcessInit() in a single-threaded application is not an error.

OCIThread initialization

Separate calls to OCIThreadInit() will all return the same OCIThread context.

Remember that each call to OCIThreadInit() must eventually be matched by a call to OCIThreadTerm().

Active versus Passive Threading Primitives

OCIThread client code written without using any active primitives can be compiled and used without modifications on both single-threaded and multithreaded platforms.

OCIThread client code written using active primitives will only work correctly on multithreaded platforms. In order to write a version of the same application to run on single-threaded platform, it is necessary to branch your code, whether by branching versions of the source file or by branching at runtime with the OCIThreadIsMulti() call.

Example Using OCIThread

The following code sample illustrates the use of OCIThread.

See Also:

For a listing of the complete demonstration programs, see Appendix B, "OCI Demonstration Programs"

static OCIEnv *envhp; 
static OCIError *errhp; 
void parent(argc, argv) 
sb4 argc; 
text **argv;
{
  OCIThreadId *tidArr[5];  
  OCIThreadHandle *tHndArr[5]; 
  ub4 i;  
  OCIThreadKey *key;  
  (void) OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0,  
                       (dvoid * (*)(dvoid *, size_t)) 0,  
                       (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                       (void (*)(dvoid *, dvoid *)) 0 ); 
  (void) OCIEnvInit( (OCIEnv **) &envhp, OCI_DEFAULT, (size_t) 0,
                     (dvoid **) 0 ); 
   (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp,  
                         OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0); 
  OCIThreadProcessInit(); 
  OCIThreadInit(envhp, errhp); 
  OCIThreadKeyInit(envhp, errhp, &key, (OCIThreadKeyDestFunc) NULL); 
  for (i=0; i<5; i++)   
    {
      OCIThreadIdInit(envhp, errhp, &(tidArr[i])); 
      OCIThreadHndInit(envhp, errhp, &(tHndArr[i])); 
    } 
  for (i=0; i<5; i++) 
    OCIThreadCreate(envhp, errhp, child, (dvoid *)key,  
                            tidArr[i], tHndArr[i]); 
  for (i=0; i<5; i++) 
    { 
      OCIThreadJoin(envhp, errhp, tHndArr[i]);  
      OCIThreadClose(envhp, errhp, tHndArr[i]); 
    } 
  for (i=0; i<5; i++)   
    { 
       OCIThreadIdDestroy(envhp, errhp, &(tidArr[i])); 
       OCIThreadHndDestroy(envhp, errhp, &(tHndArr[i])); 
    } 
  OCIThreadKeyDestroy(envhp, errhp, &key); 
  OCIThreadTerm(envhp, errhp);  
} 
void child(arg) 
dvoid *arg; 
{ 
  OCIThreadKey *key = (OCIThreadKey *)arg; 
  OCIThreadId *tid; 
  dvoid *keyval; 
  OCIThreadIdInit(envhp, errhp, &tid); 
  OCIThreadIdGet(envhp, errhp, tid); 
   if (OCIThreadKeySet(envhp, errhp, key, (dvoid *)tid) != OCI_SUCCESS) 
      printf("Could not set value for key\n"); 
   if (OCIThreadKeyGet(envhp, errhp, key, &keyval) !=OCI_SUCCESS) 
      printf("Could not retrieve value for key\n"); 
   if (keyval != (dvoid *)tid) 
     printf("Incorrect value from key after setting it\n"); 
  /* we must destroy thread id */ 
  OCIThreadIdDestroy(envhp, errhp, &tid); 
}

Connection Pooling

Connection pooling is the use of a group (the pool) of reusable physical connections by several sessions, in order to balance loads. The management of the pool is done by OCI, not the application. Applications that can use connection pooling include middle-tier applications for web application servers and e-mail servers.

A sample usage of this feature would be in a web application server connected to a back-end Oracle database. Suppose that a web application server gets several concurrent requests for data from the database server. Typically, it would have to explicitly manage the connections to the database. However, by using this functionality, it can leave that task to OCI. The application can create a pool (or a set of pools) in each environment during initialization.

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.

Connection pooling further simplifies the session and connection separation by hiding the management of the physical connection pool from the end user, who has only to create the necessary database sessions. 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.

Similarities and Differences from Shared Server

Connection pooling on the middle-tier is similar to what shared server offers on the back end. Connection pooling makes a dedicated server instance behave like a shared server instance by managing the session multiplexing logic on the middle tier.

Thus, the pooling of dedicated server processes including incoming connections into the dedicated server processes is controlled by the connection pool on the middle tier. The main difference between connection pooling and a shared server is that in the latter case, the connection from the client is normally to a dispatcher in the database instance. The dispatcher is responsible for directing the client request to an appropriate shared server. On the other hand, the physical connection from the connection pool is established directly from the middle-tier to the dedicated server process in the back-end server pool.

Connection pooling is beneficial only if the middle tier itself is multithreaded. Each thread could maintain a session to the database. The actual connections to the database are maintained by the connection pool and these connections (including the pool of dedicated database server processes) are shared among all the threads in the middle tier.

Stateless Sessions Versus Stateful Sessions

What connection pooling offers is stateless connections and stateful sessions. Users who need to work with stateless sessions should see "Session Pooling".

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, as it is described above:

Figure 9-1 OCI Connection Pooling

Text description of lnoci043.gif follows
Text description of the illustration lnoci043.gif


OCI Calls for Connection Pooling

The steps in using connection pooling in your application are:

Allocate the Pool Handle

Connection pooling requires that the pool handle OCI_HTYPE_CPOOL be allocated by OCIHandleAlloc(). Multiple pools can be created for a given environment handle.

For a single connection pool, here is an allocation example:

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

Create the Connection Pool

The function OCIConnectionPoolCreate() initializes the connection pool handle. It has these IN parameters:

All the above attributes can be configured dynamically. So the application has the flexibility of reading the current load (number of open connections and number of busy connections) and tuning these attributes appropriately.

If the pool attributes (connMax, connMin, connIncr) are to be changed dynamically, OCIConnectionPoolCreate() must be called with mode set to OCI_CPOOL_REINITIALIZE.

The OUT parameters poolName and poolNameLen will contain values to be used in subsequent OCIServerAttach() and OCILogon2() calls in place of the database name and the database name length arguments.

There is no limit on the number of pools that can be created by an application. Middle tier applications can take advantage of this feature and create multiple pools to connect to the same server or to different servers, to balance the load based on the specific needs of the application.

Here is an example of this call:

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));

Logon to the Database

There are three interfaces that can be used to log on to the database in connection pooling mode. Each one is described below. The application will need to log on to the database for each thread, using one of the following interfaces.

(1) OCILogon2()

This is the simplest interface. Use this interface when you need a simple Connection Pool connection and do not need to alter any attributes of the session handle. This interface can also be used to make proxy connections to the database.

Here is an example using OCILogon2():

for (i = 0; i < MAXTHREADS; ++i) 
{ 
   OCILogon2(envhp, errhp, &svchp[i], "scott", 5, "tiger", 5, poolName,
             poolNameLen, OCI_LOGON2_CPOOL));

}

In order to use this interface to get a proxy connection, set the password parameter to NULL.

(2) OCISessionGet()

This is the recommended interface. It gives the user the additional option of using external authentication methods, such as certificates, distinguished name, and so on. OCISessionGet() is the recommended uniform function call to retrieve a session.

Here is an example using OCISessionGet():

for (i = 0; i < MAXTHREADS; ++i) 
{ 
        OCISessionGet(envhp, errhp, &svchp, authp,
                      (OraText *) poolName,
                      strlen(poolName), NULL, 0, NULL, NULL, NULL,
                      OCI_SESSGET_CPOOL)
 }

(3) OCIServerAttach() and OCISessionBegin():

Another interface can be used if the application needs to set any special attributes on the user session handle and server handle. For such a requirement, applications need to do this:

Allocate all the handles (connection pool handle, server handles, session handles and service context handles).

The OCI_MIGRATE flag will be set internally in any case. Credentials can be set to OCI_CRED_RDBMS or OCI_CRED_PROXY. If the credentials are set to OCI_CRED_PROXY, only username needs to be set on the session handle. (no explicit primary session needs to be created and OCI_ATTR_MIGSESSION need not be set).

SGA Limitation in Connection Pooling

With OCI_CPOOL mode (connection pooling), the session memory (UGA) in the back-end database will come out of the SGA. This may require some SGA tuning on the back-end database to have a larger SGA if your application consumes more session memory than the SGA can accommodate. The memory tuning requirements for the back-end database will be similar to configuring the LARGE POOL in case of a shared server back end except that the instance is still in dedicated mode.

See Also:

For more information, see the section on configuring Shared Server in the Oracle9i Database Performance Tuning Guide and Reference

If you are still running into the SGA limitation, you should consider:

The application should avoid using dedicated database links on the back end with connection pooling.

If the back end is a dedicated server, effective connection pooling will not be possible because sessions using dedicated database links will be tied to a physical connection rendering that same connection unusable by other sessions. If your application uses dedicated database links and you do not see effective sharing of back-end processes among your sessions, you should consider using shared database links.

See Also:

For more information about distributed databases, see the section on shared database links in Oracle9i Database Administrator's Guide

Logoff from the Database

Corresponding to the logon calls, there are three different interfaces to use to log off from the database in connection pooling mode.

(1) OCILogoff():

If OCILogon2() was used to make the connection, OCILogoff() must be used to log off.

(2) OCISessionRelease()

If OCISessionGet() was called to make the connection, then OCISessionRelease() must be called to log off.

(3) OCISessionEnd() and OCIServerDetach()

If OCIServerAttach() and OCISessionBegin() were called to make the connection and start up the session, then OCISessionEnd() must be called to end the session and OCIServerDetach() must be called to release the connection.

Destroy the Connection Pool

Use OCIConnectionPoolDestroy() to destroy the connection pool.

Free the Pool Handle

The pool handle is freed using OCIHandleFree().

These last three actions are illustrated in this code fragment:

 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));

See Also:

Increasing Scrollable Cursor Performance

Better response time is obtained if you use OCI client-side prefetch buffers. After calling OCIStmtExecute() for a scrollable cursor, you can call OCIStmtFetch2() using OCI_FETCH_LAST to obtained the size of the result set. Then you can set OCI_ATTR_PREFETCH_ROWS to about 20% of the size and set OCI_ATTR_PREFETCH_MEMORY in case some result sets could take a large amount of memory.

Examples of Connection Pooling

Examples of connection pooling can be found here:

See Also:

cdemocp.c and cdemocpproxy.c in directory demo

Here is another example of connection pooling:

#include <oci.h>

#define MAXTHREAD 10

static OCIError   *errhp;
static OCIEnv     *envhp;
static OCICPool   *poolhp;

static int employeeNum[MAXTHREAD];

static OraText *poolName;
static sb4 poolNameLen;
static text *database = (text *)"";
static text *username =(text *)"SCOTT";
static text *password =(text *)"TIGER";
static text *appusername =(text *)"APPUSER";
static text *apppassword =(text *)"APPPASSWD";

static ub4 conMin = 2;
static ub4 conMax = 5;
static ub4 conIncr = 1;

static void checkerr (OCIError *errhp, sword status);
static void threadFunction (dvoid *arg);

int main (void)
{
  int i = 0;
 
  OCIEnvCreate (&envhp, OCI_THREADED, (dvoid *)0,  (dvoid * (*)()) 0,
    (dvoid * (*)()) 0, (dvoid (*)()) 0, 0, (dvoid *)0);

  (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR,
                   (size_t) 0, (dvoid **) 0);


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

  /* CREATE THE CONNECTION POOL */
  checkerr (errhp, OCIConnectionPoolCreate(envhp, 
                   errhp,poolhp, &poolName, &poolNameLen,
                   database,strlen(database),
                   conMin, conMax, conIncr,
                   appusername,strlen(appusername),
                   apppassword,strlen(apppassword),OCI_DEFAULT));

  /* Multiple threads using the connection pool */
  {
    OCIThreadId     *thrid[MAXTHREAD];
    OCIThreadHandle *thrhp[MAXTHREAD];

    OCIThreadProcessInit ();
    checkerr (errhp, OCIThreadInit (envhp, errhp));
    for (i = 0; i < MAXTHREAD; ++i)
    {
      checkerr (errhp, OCIThreadIdInit (envhp, errhp, &thrid[i]));
      checkerr (errhp, OCIThreadHndInit (envhp, errhp, &thrhp[i]));
    }
    for (i = 0; i < MAXTHREAD; ++i)
    {
      employeeNum[i]=i;
      checkerr (errhp, OCIThreadCreate (envhp, errhp, threadFunction, 
        (dvoid *) &employeeNum[i], thrid[i], thrhp[i]));
    }
    for (i = 0; i < MAXTHREAD; ++i)
    {
      checkerr (errhp, OCIThreadJoin (envhp, errhp, thrhp[i]));
      checkerr (errhp, OCIThreadClose (envhp, errhp, thrhp[i]));
      checkerr (errhp, OCIThreadIdDestroy (envhp, errhp, &(thrid[i])));
      checkerr (errhp, OCIThreadHndDestroy (envhp, errhp, &(thrhp[i])));
    }
    checkerr (errhp, OCIThreadTerm (envhp, errhp));
  } /* ALL THE THREADS ARE COMPLETE */

  checkerr(errhp, OCIConnectionPoolDestroy(poolhp, errhp, OCI_DEFAULT));
  checkerr(errhp, OCIHandleFree((dvoid *)poolhp, OCI_HTYPE_CPOOL));
  checkerr(errhp, OCIHandleFree((dvoid *)errhp, OCI_HTYPE_ERROR));
} /* end of main () */

static void threadFunction (dvoid *arg)
{ 
  int empno = *(int *)arg;
  OCISvcCtx *svchp = (OCISvcCtx *) arg;
  text insertst1[256];
  OCIStmt *stmthp = (OCIStmt *)0;

  checkerr(errhp,OCILogon2(envhp, errhp, &svchp, 
                        (CONST OraText *)username, strlen(username), 
                        (CONST OraText *)password, strlen(password), 
                        (CONST OraText *)poolName, poolNameLen,
                        OCI_CPOOL));

  sprintf(insertst1,"INSERT INTO emp(empno, ename, job, sal, deptno) values\
          (%d,'abc','MANAGER',122,20)",empno);

  OCIHandleAlloc(envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, (size_t)0,
                 (dvoid **)0);

  checkerr(errhp, OCIStmtPrepare (stmthp, errhp, (text *)insertst1,
           (ub4)strlen(insertst1), OCI_NTV_SYNTAX, OCI_DEFAULT)); 

  checkerr(errhp, OCIStmtExecute (svchp, stmthp, errhp, (ub4)1, (ub4)0,
           (OCISnapshot *)0, (OCISnapshot *)0, OCI_DEFAULT ));

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

  checkerr(errhp, OCIHandleFree((dvoid *) stmthp, OCI_HTYPE_STMT));
  checkerr(errhp, OCILogoff((dvoid *) svchp, errhp));
} /* end of threadFunction (dvoid *) */

void checkerr(errhp, status)
OCIError *errhp;
sword status;
{
  text errbuf[512];
  sb4 errcode = 0;

  switch (status)
  {
  case OCI_SUCCESS:
    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;
  }
}

Session Pooling

Session pooling means that the application will create and maintain a group of stateless sessions to the database. These sessions will be handed over to thin clients as requested. If no sessions are available, a new one may be created. When the client is done with the session, the client will release it to the pool. Thus, the number of sessions in the pool can increase dynamically.

Some of the sessions in the pool may be 'tagged' with certain properties. For instance, a user may request for a default session, set certain attributes on it, then label it or 'tag' it and return in to the pool. That user, or some other user, can require a session with the same attributes, and thus request for a session with the same tag. There may be several sessions in the pool with the same tag. The 'tag' on a session can be changed or reset.

See Also:

"Using Tags in Session Pools" for a discussion of using tags.

Proxy sessions, too, can be created and maintained through this interface.

The behavior of the application when no free sessions are available and the pool has reached it's maximum size, will depend on certain attributes. A new session may be created or an error returned, or the thread may just block and wait for a session to become free.

The main benefit of this type of pooling will be performance. Making a connection to the database is a time-consuming activity, especially when the database is remote. Thus, instead of a client spending time connecting to the server, authenticating its credentials, and then receiving a valid session, it can just pick one from the pool.

Functionality of OCI Session Pooling

Session pooling has the following features:

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

See Also:

"OCISessionGet()" for a further discussion of tagging sessions.

Handles for Session Pooling

Two handle types have been added for session pooling:

OCISPool

This is the session pool handle. It is allocated using OCIHandleAlloc(). It needs to be passed to OCISessionPoolCreate(), and OCISessionPoolDestroy(). It has the attribute type OCI_HTYPE_SPOOL.

An example of the OCIHandleAlloc() call follows:

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

For an environment handle, multiple session pools can be created.

OCIAuthInfo

This is the authentication information handle. It is allocated using OCIHandleAlloc(). It is passed to OCISessionGet(). It supports all the attributes that are supported for user session handle. Please refer to user session handle attributes for more information. The authentication information handle has the attribute type OCI_HTYPE_AUTHINFO.

An example of the OCIHandleAlloc() call follows:

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

Using OCI Session Pooling

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

OCI Calls for Session Pooling

Here are the usages for OCI calls for session pooling.

Allocate the Pool Handle

Session pooling requires that the pool handle OCI_HTYPE_SPOOL be allocated by calling OCIHandleAlloc().

Multiple pools can be created for a given environment handle. For a single session pool, here is an allocation example:

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

Create the Pool Session

The function OCISessionPoolCreate() can be used to create the session pool. Here is an example of how to use this call:

OCISessionPoolCreate(envhp,
                     errhp,poolhp, &poolName, &poolNameLen,
                     database,(sb4)strlen((const signed char *)database),
                     conMin, conMax, conIncr,
                     appusername,(sb4)strlen((const signed char *)appusername),
                     apppassword,(sb4)strlen((const signed char *)apppassword),
                     OCI_DEFAULT);

Logon to the Database

There are two interfaces that can be used to logon to the database in session pooling mode.

(1) OCILogon2():

This is the simplest interface. However, it does not give the user the option of using tagging.

Here is an example of how OCILogon2() can be used to log on to the database in session pooling mode:

for (i = 0; i < MAXTHREADS; ++i) 
{ 
  OCILogon2(envhp, errhp, &svchp[i], "scott", 5, "tiger", 5, poolName,
            poolNameLen, OCI_LOGON2_SPOOL));

}

(2) OCISessionGet():

This is the recommended interface. It gives the user the option of using tagging to label sessions in the pool, and thus make it easier to retrieve specific sessions.

An example of using OCISessionGet() follows:

OCISessionGet(envhp, errhp, &svchp, authInfop,
              (OraText *)database,strlen(database), tag,
               strlen(tag), &retTag, &retTagLen, &found, 
               OCI_SESSGET_SPOOL)

Logoff from the Database

Corresponding to the preceding logon calls, there are two interfaces to use to log off from the database in session pooling mode.

(1) OCILogoff():

If OCILogon2() was used to make the connection, OCILogoff() must be used to log off.

(2) OCISessionRelease()

If OCISessionGet() was called to make the connection, then OCISessionRelease() must be called to log off.

Destroy the Session Pool

OCISessionPoolDestroy() must be called to destroy the session pool. Here is an example of how this call can be made:

OCISessionPoolDestroy(poolhp, errhp, OCI_DEFAULT);

Free the Pool Handle

OCIHandleFree() must be called to free the session pool handle. Here is how this call can be made:

OCIHandleFree((dvoid *)poolhp, OCI_HTYPE_SPOOL);

Example of OCI Session Pooling

Here is an example of session pooling:

See Also:

cdemosp.c in directory demo

Statement Caching

Statement caching refers to the feature, first introduced in release 9.2, that provides and manages a cache of statements for each session. In the server, it means that cursors are ready to be used without the need to parse the statement again. Statement caching can be used with connection pooling and with session pooling, and will improve performance and scalability. It can be used without session pooling as well, and it works with TAF. The OCI calls that implement statement caching are:

Statement Caching Without Session Pooling

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 define 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

The concepts remain the same, except that the statement cache is enabled at the session pool layer rather than at the session layer. If a session pool has statement cache enabled, then all the statements in all the sessions in the pool will be cached, else all the statements in all the sessions remain not cached.

Rules for Statement Caching

Here are some notes to follow:

Statement Caching Code Example

Here is an example of statement caching:

See Also:

ocisc.c in directory demo

User-Defined Callback Functions

The Oracle Call Interface has the capability to execute user-specific code in addition to OCI calls. This functionality can be used for:

The OCI callback feature has been added by providing support for calling user code before or after executing the OCI calls. Functionality has also been provided to allow the user-defined code to be executed instead of executing the OCI code.

The user callback code can also be registered dynamically without modifying the source code of the application. The dynamic registration is implemented by loading up to five user-created dynamically linked libraries after the initialization of the environment handle during the OCIEnvCreate() call. These user-created libraries (such as Dynamic Link Libraries (DLLs) on NT, or shared libraries on SolarisTM Operating Environment) register the user callbacks for the selected OCI calls transparently to the application.

Sample Application

For a listing of the complete demonstration programs that illustrate the OCI user callback feature, see Appendix B, "OCI Demonstration Programs".

Registering User Callbacks

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.

See Also:

For detailed descriptions of these functions and their parameters, refer to the descriptions of OCIUserCallbackGet() and OCIUserCallbackRegister()

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


Note:

If any callback returns anything other than OCI_CONTINUE, then that return code is passed to the subsequent callbacks. If a replacement or exit callback returns a return code other than OCI_CONTINUE, then the final (not OCI_CONTINUE) return code is returned from the OCI call.


OCIUserCallbackRegister

A user callback is registered using the OCIUserCallbackRegister() call.

See Also:

See OCIUserCallbackRegister() for the syntax of this call.

Currently, OCIUserCallbackRegister() is only registered on the environment handle. The user's callback function of typedef OCIUserCallback is registered along with its context for the OCI call identified by the OCI function code, fcode. The type of the callback, whether entry, replacement, or exit, is specified by the when parameter.

For example, the stmtprep_entry_dyncbk_fn entry callback function and its context dynamic_context, are registered against the environment handle hndlp for the OCIStmtPrepare() call by calling the OCIUserCallbackRegister() function with the following parameters.

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

User Callback Function

The user callback function has to follow the following syntax:

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 */

In addition to the parameters described in the OCIUserCallbackRegister() call, the callback is called with the return code, errnop, and all the parameters of the original OCI as declared by the prototype definition.

The return code is always passed in as OCI_SUCCESS and *errnop is always passed in as 0 for the first entry callback. Note that *errnop refers to the content of errnop because errnop is an IN/OUT parameter.

If the callback does not want to change the OCI return code, then it must return OCI_CONTINUE, and the value returned in *errnop is ignored. If on the other hand, the callback returns any other return code than OCI_CONTINUE, the last returned return code becomes the return code for the call. At the this point, the value of *errnop returned is set in the error handle, or in the environment handle if the error information is returned in the environment handle because of the absence of the error handle for certain OCI calls such as OCIHandleAlloc().

For replacement callbacks, the returnCode is the non-OCI_CONTINUE return code from the previous callback or OCI call and *errnop is the value of the error number being returned in the error handle. This allows the subsequent callback to change the return code or error information if needed.

The processing of replacement callbacks is different in that if it returns anything other than OCI_CONTINUE, then subsequent replacement callbacks and OCI code is bypassed and processing jumps to the exit callbacks.

Note that if the replacement callbacks return OCI_CONTINUE to allow processing of OCI code, then the return code from entry callbacks is ignored.

All the original parameters of the OCI call are passed to the callback as variable parameters and the callback must retrieve them using the va_arg macros. The callback demonstration programs provide examples.

See Also:

See Appendix B, "OCI Demonstration Programs"

A null value can be registered to de-register a callback. That is, if the value of the callback (OCIUserCallback) is NULL in the OCIUserCallbackRegister() call, then the user callback is de-registered.

When using the thread-safe mode, the OCI program acquires all mutexes before calling the user callbacks.

UserCallback Control Flow

This pseudocode describes the overall processing of a typical 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
}

UserCallback for OCIErrorGet()

If the callbacks are a total replacement of the OCI code, then they usually maintain their own error information in the call context and use that to return error information in bufp and errnop parameters of the replacement callback of the OCIErrorGet() call.

If on the other hand, the callbacks are either partially overriding OCI code, or just doing some other post processing, then they can use the exit callback to modify the error text and errnop parameters of the OCIErrorGet() by their own error message and error number. Note that the *errnop passed into the exit callback is the error number in the error or the environment handle.

Errors from Entry Callbacks

If an entry callback wants to return an error to the caller of the OCI call, then it must register a replacement or exit callback. This is because if the OCI code is executed, then the error code from the entry callback is ignored. Therefore the entry callback should pass the error to the replacement or exit callback through its own context.

Dynamic Callback Registrations

Because user callbacks are expected to be used for monitoring OCI behavior or to access other data sources, it is desirable that the registration of the callbacks be done transparently and non-intrusively. This is accomplished by loading user-created dynamically linked libraries at OCI initialization time. These dynamically linked libraries are called packages. The user-created packages register the user callbacks for the selected OCI calls. These callbacks can further register or de-register user callbacks as needed when receiving control at runtime.

A makefile (ociucb.mk on SolarisTM Operating Environment) is provided with the OCI demonstration programs to create the package. The exact naming and location of this package is operating system dependent. The source code for the package must provide code for special callbacks that are called at OCI initialization and environment creation times.

The loading of the package is controlled by setting an operating system environment variable, ORA_OCI_UCBPKG. This variable names the packages in a generic way. The packages must be located in the $ORACLE_HOME/lib directory.

Loading Multiple Packages

The ORA_OCI_UCBPKG variable can contain a semicolon separated list of package names. The packages are loaded in the order they are specified in the list.

For example, previously one specified the package as:

setenv ORA_OCI_UCBPKG mypkg

Now, you can still specify the package as above, but in addition multiple packages can be specified as:

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

All these packages are loaded in order. That is, mypkg is loaded first and msoftpkg is loaded last.

A maximum of five packages can be specified.


Note:

The sample makefile ociucb.mk creates ociucb.so.1.0 on a SolarisTM Operating Environment system or ociucb.dll on an NT system. To load the ociucb package, the environmental variable ORA_OCI_UCBPKG must be set to ociucb. On SolarisTM Operating Environment, if the package name ends with .so, OCIInitialize() fails. The package name must end with .so.1.0.

For further details about creating the dynamic link libraries, read the makefiles provided in the demo directory for your platform. For further information on user-defined callbacks, see your platform-specific documentation on compiling and linking applications.


Package Format

Previously a package had to specify the source code for the OCIEnvCallback() function. Now the OCIEnvCallback() function is obsolete. Instead, the package source must provide two functions. The first function has to be named as packagename suffixed with the word Init. For example, if the package is named foo, then the source file (for example, but not necessarily, foo.c) should contain a fooInit() function with a call to OCISharedLibInit() function specified exactly as:

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));
}

The last parameter of the OCISharedLibInit() function, fooEnvCallback(), in this case, is the name of the second function. It can be named anything, but by convention it can be named packagename suffixed with the word EnvCallback.

This function is a replacement for OCIEnvCallback(). Now all the dynamic user callbacks must be registered in this function. The function must be of type OCIEnvCallbackType, which is specified as:

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

When an environment handle is created, then this callback function is called at the very end. The env parameter is the newly created environment handle.

The mode, xtramem_sz, and usrmemp are the parameters passed to the OCIEnvCreate() call. The last parameter, ucbDesc, is a descriptor that is passed to the package. The package uses this descriptor to register the user callbacks as described later.

A sample ociucb.c file is provided in the demo directory. The makefile ociucb.mk is also provided (on SolarisTM Operating Environment) in the demo directory to create the package. Please note that this may be different on other platforms. The demo directory also contains full user callback demo programs (cdemoucb.c, cdemoucbl.c,) illustrating this.

User Callback Chaining

User callbacks can both be registered statically in the application itself or dynamically at runtime in the DLLs. A mechanism is needed to allow the application to override a previously registered callback and then later invoke the overridden one in the newly registered callback to preserve the behavior intended by the dynamic registrations. This can result in chaining of user callbacks.

For this purpose, the OCIUserCallbackGet() function is provided to find out which function and context is registered for an OCI call.

See Also:

See OCIUserCallbackGet() for the syntax of this call

Accessing Other Data Sources Through OCI

Because Oracle is the predominant database accessed, applications can take advantage of the OCI interface to access non-Oracle data by using the user callbacks to access them. This allows an application written in OCI to access Oracle data without any performance penalty. To access non-Oracle data sources, drivers can be written that access the non-Oracle data in user callbacks. Because OCI provides a very rich interface, there is usually a straightforward mapping of OCI calls to most data sources. This solution is better than writing applications for other middle layers such as ODBC that introduce performance penalties for all data sources. Using OCI does not incur any penalty for the common case of accessing Oracle data sources, and incurs the same penalty that ODBC does for non-Oracle data sources.

Restrictions on Callback Functions

There are certain restrictions on the usage of callback functions, including OCIEnvCallback():

Example of OCI Callbacks

For example, lets suppose that there are five packages each registering entry, replacement, and exit callbacks for OCIStmtPrepare call. That is, the ORA_OCI_UCBPKG variable is set as:

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

In each package pkgN (where N can be 1 through 5), the pkgNInit() and PkgNEnvCallback() functions are specified as:

pkgNInit(metaCtx, libCtx, argfmt, argc, argv) 
{ 
  return OCISharedLibInit(metaCtx, libCtx, argfmt, argc, argv, pkgNEnvCallback); 
} 
 

The pkgNEnvCallback() function registers the entry, replacement, and exit callbacks as:

 pkgNEnvCallback(env, mode, xtramemsz, usrmemp, 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; 
} 
 

Finally, in the source code for the application, user callbacks can be registered with the NULL ucbDesc as:

  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); 
 

When the OCIStmtPrepare() call is executed, the callbacks are called in the following order:

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() 


Note:

The exit callbacks are called in the reverse order of the entry and replacement callbacks


The entry and exit callbacks can return any return code and the processing continues to the next callback. However, if the replacement callback returns anything other than OCI_CONTINUE, then the next callback (or OCI code if it is the last replacement callback) in the chain is bypassed and processing jumps to the exit callback. For example, if pkg3_replace_callback_fn() returned OCI_SUCCESS, then pkg4_replace_callback_fn(), pkg5_replace_callback_fn(), and the OCI processing for the OCIStmtPrepare call is bypassed. Instead, pkg5_exit_callback_fn() is executed next.

OCI Callbacks From External Procedures

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

See Also:

These functions are listed in Chapter 19, "OCI Cartridge Functions". For information about writing C subroutines that can be called from PL/SQL code, including a list of which OCI calls can be used, and some example code, refer to the Oracle9i Application Developer's Guide - Fundamentals.

Application Failover Callbacks

Application failover callbacks can be used in the event of the failure of one database instance, and failover to another instance. Because of the delay which can occur during failover, the application developer may want to inform the user that failover is in progress, and request that the user stand by. Additionally, the session on the initial instance may have received some ALTER SESSION commands. These will not be automatically replayed on the second instance. Consequently, the developer may wish to replay these ALTER SESSION commands on the second instance.

See Also:

For more detailed information about application failover, refer to Oracle9i Real Application Clusters Concepts and Oracle9i Net Services Reference Guide

Failover Callback Overview

To address the problems described above, 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 reauthenticated 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.

See Also:

See "Handling OCI_FO_ERROR" for more information about this scenario

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:

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_ERROR also indicates that failover was unsuccessful, but it gives the application the opportunity to handle the error and retry failover.

See Also:

See "Handling OCI_FO_ERROR" for more information about this value

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 and registration.

Part 1, Failover Callback Definition

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 aborted. 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;
}

Part 2, Failover Callback Registration

int register_callback(svrh, errh)
dvoid *svrh; /* 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."))))
     return(1);
  /* initialize the context. */
  strcpy((char *)failover.context_function, "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);
}

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 should reattempt failover.


Note:

This functionality is only available to applications linked with the 8.0.5 or later OCI libraries running against any Oracle server.


Consider the following timeline of events:

Time Event

T0

Database fails (failure lasts until T5).

T1

Failover triggered by user activity.

T2

User attempts to reconnect; attempt fails.

T3

Failover callback invoked with OCI_FO_ERROR.

T4

Failover callback enters predetermined sleep period.

T5

Database comes back up again.

T6

Failover callback triggers new failover attempt; it is successful.

T7

User successfully reconnects

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 might be used to implement the failover strategy similar to the scenario described above. 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;
}

The following is sample output from a program containing this failover callback function:

executing select...
7369    SMITH    CLERK
7499    ALLEN    SALESMAN
 Failing Over ... Please stand by 
 Failover type was found to be SELECT 
 Failover Context is :My context.
 Failover error gotten. Sleeping...Retrying.  Failover error gotten. 
Sleeping...Retrying.  Failover error gotten. Sleeping...Retrying.  Failover 
error gotten. Sleeping...Retrying.  Failover error gotten. Sleeping...Retrying.  
Failover error gotten. Sleeping...Retrying.  Failover error gotten. 
Sleeping...Retrying.  Failover error gotten. Sleeping...Retrying.  Failover 
error gotten. Sleeping...Retrying.  Failover error gotten. Sleeping...Retrying. 
 Failover ended ...resuming services
7521    WARD    SALESMAN
7566    JONES    MANAGER
7654    MARTIN    SALESMAN
7698    BLAKE    MANAGER
7782    CLARK    MANAGER
7788    SCOTT    ANALYST
7839    KING    PRESIDENT
7844    TURNER    SALESMAN
7876    ADAMS    CLERK
7900    JAMES    CLERK
7902    FORD    ANALYST

OCI and Advanced Queuing

The OCI provides an interface to Oracle's Advanced Queuing feature. Oracle AQ provides message queuing as an integrated part of the Oracle server. Oracle AQ provides this functionality by integrating the queuing system with the database, thereby creating a message-enabled database. By providing an integrated solution Oracle AQ frees application developers to devote their efforts to their specific business logic rather than having to construct a messaging infrastructure.


Note:

In order to use Advanced Queuing, you must be using the Enterprise Edition


See Also:

OCI Advanced Queuing Functions

The OCI library includes several functions related to Advanced Queuing:

OCI Advanced Queuing Descriptors

The following descriptors are used by OCI AQ operations:

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 and/or read.

See Also:

These attributes are described in more detail in "Advanced Queuing Descriptor Attributes"

Advanced Queuing in OCI versus PL/SQL

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

PL/SQL Function OCI Function

DBMS_AQ.ENQUEUE

OCIAQEnq()

DBMS_AQ.DEQUEUE

OCIAQDeq()

DBMS_AQ.LISTEN

OCIAQListen()

DBMS_AQ.ENQUEUE Parameter OCIAQEnq() Parameter

queue_name

queue_name

enqueue_options

enqueue_options

message_properties

message_properties

payload

payload

msgid

msgid

Note: OCIAQEnq() also requires the following additional parameters: svch, errh, payload_tdo, payload_ind, and flags

DBMS_AQ.DEQUEUE Parameter OCIAQDeq() Parameter

queue_name

queue_name

dequeue_options

dequeue_options

message_properties

message_properties

payload

payload

msgid

msgid

Note: OCIAQDeq() also requires the following additional parameters: svch, errh, queue_name, dequeue_options, message_properties, payload_tdo, payload, payload_ind, and flags

DBMS_AQ.LISTEN Parameter OCIAQListen() Parameter

agent_list

agent_list

wait

wait

agent

agent

Note: OCIAQListen() also requires the following additional parameters: svchp, errhp, agent_list, num_agents, wait, agent, and flags

PL/SQL Agent Parameter OCIAQAgent Attribute

name

OCI_ATTR_AGENT_NAME

address

OCI_ATTR_AGENT_ADDRESS

protocol

OCI_ATTR_AGENT_PROTOCOL

PL/SQL Message Property OCIAQMsgProperties Attribute

priority

OCI_ATTR_PRIORITY

delay

OCI_ATTR_DELAY

expiration

OCI_ATTR_EXPIRATION

correlation

OCI_ATTR_CORRELATION

attempts

OCI_ATTR_ATTEMPTS

recipient_list

OCI_ATTR_RECIPIENT_LIST

exception_queue

OCI_ATTR_EXCEPTION_QUEUE

enqueue_time

OCI_ATTR_ENQ_TIME

state

OCI_ATTR_MSG_STATE

sender_id

OCI_ATTR_SENDER_ID

original_msgid

OCI_ATTR_ORIGINAL_MSGID

PL/SQL Enqueue Option OCIAQEnqOptions Attribute

visibility

OCI_ATTR_VISIBILITY

relative_msgid

OCI_ATTR_RELATIVE_MSGID

sequence_deviation

OCI_ATTR_SEQUENCE_DEVIATION

PL/SQL Dequeue Option OCIAQDeqOptions Attribute

consumer_name

OCI_ATTR_CONSUMER_NAME

dequeue_mode

OCI_ATTR_DEQ_MODE

navigation

OCI_ATTR_NAVIGATION

visibility

OCI_ATTR_VISIBILITY

wait

OCI_ATTR_WAIT

msgid

OCI_ATTR_DEQ_MSGID

correlation

OCI_ATTR_CORRELATION

Publish-Subscribe Notification

The publish-subscribe notification feature allows an OCI application to receive client notifications directly, register an e-mail address to which notifications can be sent, register a HTTP URL to which notifications can be posted, or register a PL/SQL procedure to be invoked on a notification. Figure 9-2, "Publish-Subscribe Model" illustrates the process.

Figure 9-2 Publish-Subscribe Model

Text description of lnoci039.gif follows
Text description of the illustration lnoci039.gif


An OCI application can:

In all the above scenarios the notification can be received directly by the OCI application, or the notification can be sent to a pre-specified e-mail address, or it can be sent to a pre-defined HTTP URL, or a pre-specified database PL/SQL procedure can be invoked as a result of a notification.

Registered clients are notified asynchronously when events are triggered or on an explicit AQ enqueue. Clients do not need to be connected to a database.

See Also:

Publish-Subscribe Registration Functions

Registration can be done in two ways:

Let us next consider these two alternative ways of registration.

Publish-Subscribe Register Directly to the Database

The following steps are required in an OCI application to register and receive notifications for events. It is assumed that the appropriate event trigger or AQ queue has been set up. The initialization parameter COMPATIBLE must be set to 8.1 or higher.

See Also:

Note:

The publish-subscribe feature is only available on multithreaded platforms.


  1. Execute OCIInitialize() with OCI_EVENTS mode to specify that the application is interested in registering for and receiving notifications. This starts a dedicated listening thread for notifications on the client.
  2. Execute OCIHandleAlloc() with handle type OCI_HTYPE_SUBSCRIPTION to allocate a subscription handle.
  3. Execute OCIAttrSet() to set the subscription handle attributes for:
    • OCI_ATTR_SUBSCR_NAME - subscription name
    • OCI_ATTR_SUBSCR_NAMESPACE - subscription namespace
    • OCI_ATTR_SUBSCR_CALLBACK - notification callback
    • OCI_ATTR_SUBSCR_CTX - callback context
    • OCI_ATTR_SUBSCR_PAYLOAD - payload buffer for posting
    • OCI_ATTR_SUBSCR_RECPT - recipient name
    • OCI_ATTR_SUBSCR_RECPTPROTO - protocol to receive notification with
    • OCI_ATTR_SUBSCR_RECPTPRES - presentation to receive notification with

    OCI_ATTR_SUBSCR_NAME, OCI_ATTR_SUBSCR_NAMESPACE and OCI_ATTR_SUBSCR_RECPTPROTO must be set before registering a subscription.

    If OCI_ATTR_SUBSCR_RECPTPROTO is set to OCI_SUBSCR_PROTO_OCI, then OCI_ATTR_SUBSCR_CALLBACK and OCI_ATTR_SUBSCR_CTX also need to be set.

    If OCI_ATTR_SUBSCR_RECPTPROTO is set to OCI_SUBSCR_PROTO_MAIL, OCI_SUBSCR_PROTO_SERVER, or OCI_SUBSCR_PROTO_HTTP, then OCI_ATTR_SUBSCR_RECPT also needs to be set.

    Setting OCI_ATTR_SUBSCR_CALLBACK and OCI_ATTR_SUBSCR_RECPT at the same time will cause an application error.

    OCI_ATTR_SUBSCR_PAYLOAD is required before posting to a subscription.

    See Also:

    For information on these attributes, see "Subscription Handle Attributes"

  4. If OCI_ATTR_SUBSCR_RECPTPROTO is set to OCI_SUBSCR_PROTO_OCI, then define the callback routine to be used with the subscription handle.

    See Also:

    "Notification Callback"

  5. If OCI_ATTR_SUBSCR_RECPTPROTO is set to OCI_SUBSCR_PROTO_SERVER, then define the PL/SQL procedure, to be invoked on notification, in the database.

    See Also:

    "Notification Procedure"

  6. Execute OCISubscriptionRegister() to register with the subscriptions. This call can register interest in several subscriptions at the same time.

Open Registration for Publish-Subscribe

Prerequisites for this method are:

The steps in open registration using Oracle Enterprise Security Manager (OESM) are:

1. In each enterprise domain, create enterprise role, ENTERPRISE_AQ_USER_ROLE.

2. For each database in the enterprise domain, add global role GLOBAL_AQ_USER_ROLE to enterprise role ENTERPRISE_AQ_USER_ROLE.

3. For each enterprise domain, add enterprise role ENTERPRISE_AQ_USER_ROLE to privilege group cn=OracleDBAQUsers, under cn=oraclecontext, under the administrative context.

4. For each enterprise user that should be authorized to register for events in the database, grant enterprise role ENTERPRISE_AQ_USER_ROLE.

Using OCI to Open Register with LDAP

1. Call OCIInitialize() with mode set to OCI_EVENTS | OCI_USE_LDAP.

2. Call OCIAttrSet() to set the following environment handle attributes for accessing LDAP:

3. Call OCIHandleAlloc() with handle type OCI_HTYPE_SUBSCRIPTION, to allocate a subscription handle.

4. Call OCIDescriptorAlloc() with descriptor type OCI_DTYPE_SRVDN, to

allocate a server DN descriptor.

5. Call OCIAttrSet() to set the server DN descriptor attributes for OCI_ATTR_SERVER_DN, the distinguished name of the database in which the client wants to receive notifications. OCIAttrSet() can be called multiple times for this attribute so that more than one database server is included in the registration

6. Call OCIAttrSet() to set the subscription handle attributes for:

7. Call OCISubscriptionRegister() to register the subscriptions. The registration will take effect when the database accesses LDAP to pick up new registrations. The frequency of pick-ups is determined by the value of REG_SYNC_INTERVAL.

OCI Functions Used to Manage Publish-Subscribe Notification

The following functions are used to manage publish-subscribe notification.

Table 9-1 Publish-Subscribe Functions  
Function Purpose

OCISubscriptionDisable()

Disables a subscription.

OCISubscriptionEnable()

Enables a subscription.

OCISubscriptionPost()

Posts a subscription.

OCISubscriptionRegister()

Registers a subscription.

OCISubscriptionUnRegister()

Unregisters a subscription.

Notification Callback

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.

See Also:

For information, see "Subscription Handle Attributes"

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:

iMode (IN)

Call-specific mode. Valid value:

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. For information, see "Subscription Handle Attributes".

See Also:

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.


Rem ------------------------------------------------------
REM create queue table for persistent multiple consumers
Rem ------------------------------------------------------
connect pubsub/pubsub;
Rem  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;
/
Rem ------------------------------------------------------
Rem  Create a persistent queue for publishing messages
Rem ------------------------------------------------------
Rem  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;
/
Rem ------------------------------------------------------
Rem  Start the queue
Rem ------------------------------------------------------
begin
  DBMS_AQADM.START_QUEUE('pubsub.logon');
end;
/
Rem ------------------------------------------------------
Rem  define new_enqueue for convenience
Rem ------------------------------------------------------
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;
/
Rem ------------------------------------------------------
Rem  add subscriber with rule based on current user name, 
Rem  using correlation_id
Rem ------------------------------------------------------
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 = ''SCOTT'' ');
end;
/
Rem ------------------------------------------------------
Rem  create a trigger on logon on database
Rem ------------------------------------------------------
Rem  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;
/

Rem ------------------------------------------------------
Rem  create a PL/SQL callback for notification of logon 
Rem  of user `scott' on database
Rem ------------------------------------------------------
Rem  
create or replace procedure plsqlnotifySnoop(
  context raw, reginfo sys.aq$_reg_info, descr sys.aq$_descriptor,
  payload raw, payloadl number)
as
begin
 dbms_output.putline('Notification : User Scott 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.

...
ub4 namespace = OCI_SUBSCR_NAMESPACE_AQ;
/* callback function for notification of logon of user 'scott' on database */
ub4 notifySnoop(ctx, subscrhp, pay, payl, desc, mode)
    dvoid *ctx;
    OCISubscription *subscrhp;
    dvoid *pay;
    ub4 payl;
    dvoid *desc;
    ub4 mode;
{
    printf("Notification : User Scott Logged on\n");
}
int main()
{
    OCISession *authp = (OCISession *) 0;
    OCISubscription *subscrhpSnoop = (OCISubscription *)0;
    OCISubscription *subscrhpSnoopMail = (OCISubscription *)0;
    OCISubscription *subscrhpSnoopServer = (OCISubscription *)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 */
        "PUBSUB.SNOOP:ADMIN", /* 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 */
        "PUBSUB.SNOOP:ADMIN", /* subscription name */ 
/* <queue_name>:<agent_name> */
        (dvoid*)0, /* callback function */
        OCI_SUBSCR_PROTO_MAIL, /* receive with protocol */
         "xyz@company.com", /* recipient address */
        OCI_SUBSCR_PRES_DEFAULT); /* receive with presentation */

/* Register for server to server notification */
    initSubscriptionHn(    &subscrhpSnoopServer,    /* subscription handle */
        "PUBSUB.SNOOP:ADMIN", /* subscription name */ 
/* <queue_name>:<agent_name> */
        (dvoid*)0, /* callback function */
        OCI_SUBSCR_PROTO_SERVER, /* receive with protocol */
         "pubsub.plsqlnotifySnoop", /* recipient address */
        OCI_SUBSCR_PRES_DEFAULT); /* receive with presentation */
/*****************************************************
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);
}
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);
    checkerr(errhp, OCISubscriptionRegister(svchp, subscrhp, 1, errhp,
        OCI_DEFAULT));

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

If user SCOTT 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);
}

...


Go to previous page Go to next page
Oracle
Copyright © 1996, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback