9 OCI Programming Advanced Topics

This chapter contains these topics:

Connection Pooling in OCI

This section includes the following topics:

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

One use of this feature is 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. The application can create a pool (or a set of pools) in each environment during application initialization.

OCI Connection Pooling Concepts

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

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

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

Similarities and Differences from a Shared Server

Connection pooling on the middletier is similar to what a 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.

The connection pool on the middle tier controls the pooling of dedicated server processes including incoming connections into the dedicated server processes. 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. However, the physical connection from the connection pool is established directly from the middletier to the dedicated server process in the back-end server pool.

Connection pooling is beneficial only if the middle tier is multithreaded. Each thread can 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

Stateless sessions are serially reusable across mid-tier threads. After a thread is done processing a database request on behalf of a three-tier user, the same database session can be reused for a completely different request on behalf of a completely different three-tier user.

Stateful sessions to the database, however, are not serially reusable across mid-tier threads because they may have some particular state associated with a particular three-tier user. Examples of such state may include open transactions, the fetch state from a statement, or a PL/SQL package state. So long as the state exists, the session is not reusable for a different request.

Note:

Stateless sessions too may have open transactions, open statement fetch state, and so on. However, such a state persists for a relatively short duration (only during the processing of a particular three-tier request by a mid-tier thread) that allows the session to be serially reused for a different three-tier user (when such state is cleaned up).

Stateless sessions are typically used in conjunction with statement caching.

What connection pooling offers is stateless connections and stateful sessions. If you must work with stateless sessions, see "Session Pooling in OCI".

Multiple Connection Pools

You can use the advanced concept of multiple connection pools 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.

Figure 9-1 illustrates OCI connection pooling.

Figure 9-1 OCI Connection Pooling

Description of Figure 9-1 follows
Description of "Figure 9-1 OCI Connection Pooling"

Transparent Application Failover

Transaction application failover (TAF) is enabled for connection pooling. The concepts of TAF apply equally well with connections in the connection pool except that the BACKUP and PRECONNECT clauses should not be used in the connect string and do not work with connection pooling and TAF.

When a connection in the connection pool fails over, it uses the primary connect string itself to connect. Sessions fail over when they use the pool for a database round-trip after their instance failure. The listener is configured to route the connection to a good instance if available, as is typical with service-based connect strings.

See Also:

Oracle Database Net Services Administrator's Guide, the chapter about configuring transparent application failover

OCI Calls for Connection Pooling

To use connection pooling in your application, you must:

  1. Allocate the Pool Handle

  2. Create the Connection Pool

  3. Log On to the Database

  4. Deal with SGA Limitations in Connection Pooling

  5. Log Off from the Database

  6. Destroy the Connection Pool

  7. Free the Pool Handle

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((void *) envhp, (void **) &poolhp, OCI_HTYPE_CPOOL, 
                      (size_t) 0, (void **) 0));

Create the Connection Pool

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

  • connMin, the minimum number of connections to be opened when the pool is created.

  • connIncr, the incremental number of connections to be opened when all the connections are busy and a call needs a connection. This increment is used only when the total number of open connections is less than the maximum number of connections that can be opened in that pool.

  • connMax, the maximum number of connections that can be opened in the pool. When the maximum number of connections are open in the pool, and all the connections are busy, if a call needs a connection, it waits until it gets one. However, if the OCI_ATTR_CONN_NOWAIT attribute is set for the pool, an error is returned.

  • A poolUsername and a poolPassword, to allow user sessions to transparently migrate between connections in the pool.

  • In addition, an attribute OCI_ATTR_CONN_TIMEOUT, can be set to time out the connections in the pool. Connections idle for more than this time are terminated periodically to maintain an optimum number of open connections. If this attribute is not set, then the connections are never timed out.

Note:

Shrinkage of the pool only occurs when there is a network round-trip. If there are no operations, then the connections stay active.

Because all the preceding attributes can be configured dynamically, the application can read the current load (number of open connections and number of busy connections) and tune 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 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 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) connMin, (ub4) connMax, (ub4) connIncr,
                   (text *)poolUsername,strlen(poolUserLen),
                   (text *)poolPassword,strlen(poolPassLen),
                   OCI_DEFAULT));

Log On to the Database

The application must log on to the database for each thread, using one of the following interfaces.

  • 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], "hr", 2, "hr", 2, poolName,
             poolNameLen, OCI_LOGON2_CPOOL));

}

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

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

  • OCIServerAttach() and OCISessionBegin()

    You can use another interface if the application must set any special attributes on the user session handle and server handle. For such a requirement, applications must allocate all the handles (connection pool handle, server handles, session handles, and service context handles). You would follow this sequence:

    1. Create the connection pool.

      Connection pooling does the multiplexing of a virtual server handle over physical connections transparently, eliminating the need for users to do so. The user gets the feeling of a session having a dedicated (virtual) connection. Because the multiplexing is done transparently to the user, users must not attempt to multiplex sessions over the virtual server handles themselves. The concepts of session migration and session switching, which require explicit multiplexing at the user level, are defunct for connection pooling and should not be used.

    2. Call OCIServerAttach() with mode set to OCI_CPOOL.

      In an OCI program, the user should create (OCIServerAttach() with mode set to OCI_CPOOL), a unique virtual server handle for each session that is created using the connection pool. There should be a one-to-one mapping between virtual server handles and sessions.

    3. Call OCISessionBegin() with mode set to OCI_DEFAULT.

      Credentials can be set to OCI_CRED_RDBMS, OCI_CRED_EXT, or OCI_CRED_PROXY using OCISessionBegin(). If the credentials are set to OCI_CRED_EXT, no user name and no password need to be set on the session handle. If the credentials are set to OCI_CRED_PROXY, only the user name must be set on the session handle. (no explicit primary session must be created and OCI_ATTR_MIGSESSION need not be set).

      The user should not set OCI_MIGRATE flag in the call to OCISessionBegin() when the virtual server handle points to a connection pool (OCIServerAttach() called with mode set to OCI_CPOOL). Oracle supports passing the OCI_MIGRATE flag only for compatibility reasons. Do not use the OCI_MIGRATE flag, because the perception that the user gets when using a connection pool is of sessions having their own dedicated (virtual) connections that are transparently multiplexed onto real connections.

Deal with SGA Limitations in Connection Pooling

With OCI_CPOOL mode (connection pooling), the session memory (UGA) in the back-end database comes 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 are similar to configuring the LARGE POOL in a shared server back end except that the instance is still in dedicated mode.

See Also:

Oracle Database Performance Tuning Guide, the section about configuring a shared server

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

  • Reducing the session memory consumption by having fewer open statements for each session

  • Reducing the number of sessions in the back end by pooling sessions on the mid-tier

  • Or otherwise, turning off connection pooling

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

If the back end is a dedicated server, effective connection pooling is not possible because sessions using dedicated database links are 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 must consider using shared database links.

See Also:

Oracle Database Administrator's Guide, the section on shared database links for more information about distributed databases

Log Off from the Database

From the following calls, choose the one that corresponds to the logon call and use it to log off from the database in connection pooling mode.

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((void *) svchp[i], errhp));
  }
  checkerr(errhp, OCIConnectionPoolDestroy(poolhp, errhp, OCI_DEFAULT));
  checkerr(errhp, OCIHandleFree((void *)poolhp, OCI_HTYPE_CPOOL));

See Also:

Examples of OCI Connection Pooling

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

Session Pooling in OCI

This section includes the following topics:

Session pooling means that the application creates and maintains a group of stateless sessions to the database. These sessions are provided 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 releases 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 a default session, set certain attributes on it, label it or tag it, and return it to the pool. That user, or some other user, can require a session with the same attributes, and thus request 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"

Proxy sessions, too, can be created and maintained through session pooling in OCI.

The behavior of the application when no free sessions are available and the pool has reached its maximum size depends 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 session pooling is 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 can perform the following tasks:

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

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

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

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

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

  • Allow for session pooling with authentication.

Homogeneous and Heterogeneous Session Pools

A session pool can be either homogeneous or heterogeneous. Homogeneous session pooling means that sessions in the pool are alike for authentication (they have the same user name, 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 can 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, can request a session with the same tags to have a session with 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

OCI Handles for Session Pooling

The following handle types are for session pooling.

OCISPool

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

An example of the OCIHandleAlloc() call follows:

OCISPool *spoolhp;
OCIHandleAlloc((void *) envhp, (void **) &spoolhp, OCI_HTYPE_SPOOL, 
                        (size_t) 0, (void **) 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 a user session handle. See User Session Handle Attributes for more information. The authentication information handle has the attribute type OCI_HTYPE_AUTHINFO (see Table 2-1).

An example of the OCIHandleAlloc() call follows:

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

See Also:

Using OCI Session Pooling

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

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

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

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

    1. Allocates an authentication information handle of type OCIAuthInfo using OCIHandleAlloc()

    2. Sets the user name and password in the authentication information handle using OCIAttrSet()

    3. Gets a pooled session using OCISessionGet() with mode set to OCI_SESSGET_SPOOL

    4. Performs the transaction

    5. Allocates the handle

    6. Prepares the statement

      Note:

      When using service contexts obtained from OCI session pool, you are required to use the service context returned by OCISessionGet() (or OCILogon2()), and not create other service contexts outside of these calls.

      Any statement handle obtained using OCIStmtPrepare2() with the service context should be subsequently used only in conjunction with the same service context, and never with a different service context.

    7. Executes the statement

    8. Commits or rolls back the transactions

    9. Releases the session (log off) with OCISessionRelease()

    10. Frees the authentication information handle with OCIHandleFree()

    11. Ends the loop for each thread

  4. Destroy the session pool using OCISessionPoolDestroy().

OCI Calls for Session Pooling

Here are the usages for OCI calls for session pooling. OCI provides calls for session pooling to perform the following tasks:

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((void *) envhp, (void **) &poolhp, OCI_HTYPE_SPOOL, (size_t) 0,
               (void **) 0));

Create the Pool Session

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

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

Log On to the Database

You can use these calls to log on to the database in session pooling mode.

  • OCILogon2()

    This is the simplest call. However, it does not give the user the option of using tagging. Here is an example of how to use OCILogon2() to log on to the database in session pooling mode:

    for (i = 0; i < MAXTHREADS; ++i) 
{ 
  OCILogon2(envhp, errhp, &svchp[i], "hr", 2, "hr", 2, poolName,
            poolNameLen, OCI_LOGON2_SPOOL));
}

  • OCISessionGet()

    This is the recommended call to use. It gives the user the option of using tagging to label sessions in the pool, which makes it easier to retrieve specific sessions. An example of using OCISessionGet() follows. It is taken from cdemosp.c in the demo directory.

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

    When using service contexts obtained from an OCI session pool, you are required to use the service context returned by OCISessionGet() (or OCILogon2()), and not create other service contexts outside of these calls.

    Any statement handle obtained using OCIStmtPrepare2() with the service context should be subsequently used only in conjunction with the same service context, and never with a different service context.

Log Off from the Database

From the following calls, choose the one that corresponds to the logon call and use it to log off from the database in session pooling mode.

Destroy the Session Pool

Call OCISessionPoolDestroy() to destroy the session pool, as shown in the following example:

OCISessionPoolDestroy(poolhp, errhp, OCI_DEFAULT);

Free the Pool Handle

Call OCIHandleFree() to free the session pool handle, as shown in the following example:

OCIHandleFree((void *)poolhp, OCI_HTYPE_SPOOL);

Note:

Developers: You are advised to commit or roll back any open transaction before releasing the connection back to the pool. If this is not done, Oracle Database automatically commits any open transaction when the connection is released.

If an instance failure is detected while the session pool is being used, OCI tries to clean up the sessions to that instance.

Example of OCI Session Pooling

For an example of session pooling in a tested complete program, see cdemosp.c in directory demo.

Runtime Connection Load Balancing

Oracle Real Application Clusters (Oracle RAC) is a database option in which a single database is hosted by multiple instances on multiple nodes. The Oracle RAC shared disk method of clustering databases increases scalability. The nodes can easily be added or freed to meet current needs and improve availability, because if one node fails, another can assume its workload. Oracle RAC adds high availability and failover capacity to the database, because all instances have access to the whole database.

Balancing of work requests occurs at two different times: at connect time and at runtime. These are referred to as connect time load balancing (provided by Oracle Net Services) and runtime connection load balancing. For Oracle RAC environments, session pools use service metrics received from the Oracle RAC load balancing advisory through Fast Application Notification (FAN) events to balance application session requests. The work requests coming into the session pool can be distributed across the instances of Oracle RAC offering a service, using the current service performance.

See Also:

Connect time load balancing occurs when a session is first created by the application. It is necessary that the sessions that are part of the pool be well distributed across Oracle RAC instances, when they are first created. This ensures that sessions on each of the instances get a chance to execute work.

Runtime connection load balancing routes work requests to sessions in a session pool that best serve the work. It occurs when an application selects a session from an existing session pool and thus is a very frequent activity. For session pools that support services at one instance only, the first available session in the pool is adequate. When the pool supports services that span multiple instances, there is a need to distribute the work requests across instances so that the instances that are providing better service or have greater capacity get more requests.

Runtime connection load balancing is enabled by default in an Oracle Database Release 11.1 or later client communicating with a server of Oracle Database Release 10.2 or later. Setting the mode parameter to OCI_SPC_NO_RLB when calling OCISessionPoolCreate() disables runtime connection load balancing.

Receiving Load Balancing Advisory FAN Events

To receive the service metrics based on the service time, the following requirements must be met:

  • Oracle RAC environment with Oracle Clusterware must be set up and enabled.

  • The application must have been linked with the threads library.

  • The OCI environment must be created in OCI_EVENTS and OCI_THREADED mode.

  • The service must be modified to set up its goal and the connection load balancing goal as follows:

    EXEC DBMS_SERVICE.MODIFY_SERVICE("myService",
     DBMS_SERVICE.GOAL_SERVICE_TIME,
     clb_goal => dbms_service.clb_goal_short);

See Also:

Database Resident Connection Pooling

This section includes the following topics:

Database resident connection pooling (DRCP) provides a connection pool in the database server for typical web application usage scenarios where the application acquires a database connection, works on it for a relatively short duration, and then releases it. DRCP pools server processes, each of which is the equivalent of a dedicated server process and a database session combined, which are referred to as pooled servers. Pooled servers can be shared across multiple applications running on the same or several hosts. A connection broker process manages the pooled servers at the database instance level. DRCP is a configurable feature chosen at program runtime, allowing traditional and DRCP-based connection architectures to be in concurrent use.

DRCP is especially relevant for architectures with multiprocess single-threaded application servers (such as PHP and Apache) that cannot do middle-tier connection pooling. DRCP is also very useful in large scale Web deployments where hundreds or thousands of web servers or mid-tiers need database access, client-side pools (even in multithreaded systems and languages such as Java). Using DRCP, the database can scale to tens of thousands of simultaneous connections. If your Database web application must scale with large numbers of connections, DRCP is your connection pooling solution.

DRCP complements middle-tier connection pools that share connections between threads in a middle-tier process. In addition, DRCP enables sharing of database connections across middle-tier processes on the same middle-tier host, across multiple middle-tier hosts, and across multiple middle-tiers (web servers, containers) accommodating applications written in different languages. This results in significant reduction in key database resources needed to support a large number of client connections, thereby reducing the database tier memory footprint and boosting the scalability of both middle-tier and database tiers. Having a pool of readily available servers has the additional benefit of reducing the cost of creating and tearing down client connections.

Clients get connections out of the database resident connection pool connect to an Oracle Database background process known as the connection broker. The connection broker implements the pool functionality and multiplexes pooled servers among persistent inbound connections from the client

When a client requires database access, the connection broker picks up a server process from the pool and hands it off to the client. The client is then directly connected to the server process until the request is served. After the server has finished, the server process is released back into the pool and the connection from the client is restored to the connection broker as a persistent inbound connection from the client process. In DRCP, releasing resources leaves the session intact, but no longer associated with a connection (server process). Because this session stores its user global area (UGA) in the program global area (PGA), not in the system global area (SGA), a client can reestablish a connection transparently upon detecting activity.

DRCP is typically preferred for applications with a large number of connections. Shared servers are useful for a medium number of connections and dedicated sessions are preferred for small numbers of connections. The threshold sizes are relative to the amount of memory available on the database host.

DRCP provides the following advantages:

  • It enables resource sharing among multiple client applications and middle-tier application servers.

  • It improves scalability of databases and applications by reducing resource usage on the database host.

Compared to shared servers, DRCP offers theses additional benefits:

  • DRCP provides a direct tie with the database server furnished by client-side connection pooling (that is, there is no man-in-the-middle like client-side connection pooling, but unlike shared servers)

  • DRCP can pool database servers (like client-side connection pooling and shared servers),

  • DRCP can pool sessions (like client-side connection pooling and unlike shared servers)

  • DRCP can share connections across mid-tier boundaries (unlike client-side connection pooling)

DRCP offers a unique connection pooling solution that addresses scalability requirements in environments requiring large numbers of connections with minimal database resource usage.

See Also:

Configuring Database Resident Connection Pooling

The pool is managed by the DBA using the DBMS_CONNECTION_POOL package. The pool is installed by default, but is shutdown. The DBA must start it and specify DRCP configuration options that include, for example, the minimum and maximum number of pooled servers to be allowed in the pool, the number of connection brokers to be created, and the maximum number of connections that each connection broker can handle, and so forth. See the references for more information.

OCI session pool APIs have been extended to interoperate with the database resident connection pool. See the references for more information.

See Also:

Using OCI Session Pool APIs with DRCP

The sections that follow describe OCI session pool APIs that have been extended to interoperate with the database resident connection pool. An OCI application typically initializes the environment for the OCI session pool for DRCP using OCISessionPoolCreate() by specifying the database connection string (connStr), whether a user name (userid) and password (password) are associated with each session, the minimum (sessMin) and the next increment (sessIncr) of sessions to be started if the mode parameter is specified as OCI_SPC_HOMOGENEOUS to allow all sessions in the pool to be authenticated with the user name and password passed in, the maximum (sessMax) number of sessions allowed in the session pool, and so forth.

Sessions are obtained from DRCP from the OCI session pool using OCISessionGet(), by specifying the OCI_SESSGET_SPOOL attribute in the mode parameter and sessions are released to DRCP to the OCI session pool using OCISessionRelease(). The OCI session pool can also transparently keep connections to the connection broker cached to improve performance. OCI applications can reuse the sessions within which the application leaves sessions of a similar state by using OCISessionGet() (authInfop) and setting the OCI_ATTR_CONNECTION_CLASS attribute and specifying a connection class name or by using the OCIAuthInfo handle before calling OCISessionGet(). Using OCISessionGet() (mode), OCI applications can also specify session purity, that is, whether to reuse a pooled session (set the OCI_SESSGET_PURITY_SELF attribute) or to use a new session (set the OCI_SESSGET_PURITY_NEW attribute).

In addition, features offered by the traditional client-side OCI session pool, such as tagging, statement caching, and transparent application failover (TAF) are also supported with DRCP.

Session Purity and Connection Class

In Oracle Database Release 11.1, OCI introduced two settings that can be specified when obtaining a session using OCISessionGet():

Session Purity

Session purity specifies whether the application logic is set up to reuse a pooled session or to use a new session. OCISessionGet() has been enhanced to take in a purity setting of OCI_SESSGET_PURITY_NEW or OCI_SESSGET_PURITY_SELF. Alternatively, you can set OCI_ATTR_PURITY_NEW or OCI_ATTR_PURITY_SELF on the OCIAuthInfo handle before calling OCISessionGet(). Both methods are equivalent.

Note:

When reusing a session from the pool, the NLS attributes of the server take precedence over those of the client.

For example, if the client has NLS_LANG set to french_france.us7ascii and if it is assigned a German session from the pool, the client session would be German.

You can use connection classes to restrict sharing and to avoid this problem.

Example 9-1 shows how a connection pooling application sets up a NEW session.

Example 9-1 Setting Session Purity

/* OCIAttrSet method */

ub4 purity = OCI_ATTR_PURITY_NEW;
OCIAttrSet (authInfop, OCI_HTYPE_AUTHINFO,  &purity, sizeof (purity),
            OCI_ATTR_PURITY, errhp);
OCISessionGet (envhp, errhp, &svchp, authInfop, poolName, poolNameLen, NULL, 0,
               NULL, NULL, NULL, OCI_SESSGET_SPOOL);
/* poolName is the name returned by OCISessionPoolCreate() */

/*  OCISessionGet mode method */
OCISessionGet (envhp, errhp, &svchp, authInfop, poolName, poolNameLen, NULL, 0,
               NULL, NULL, NULL, OCI_SESSGET_SPOOL | OCI_SESSGET_PURITY_NEW);
/* poolName is the name returned by OCISessionPoolCreate() */

Connection Class

Connection class defines a logical name for the type of connection required by the application. Sessions from the OCI session pool cannot be shared by different users (A session first created for user HR is only given out to subsequent requests by user HR.) The connection class setting allows for further separation between the sessions of a given user. The connection class setting lets different applications (connecting as the same database user) identify their sessions using a logical name that corresponds to the application. OCI then ensures that such sessions belonging to a particular connection class are not shared outside of the connection class.

You can use the OCI_ATTR_CONNECTION_CLASS attribute on the OCIAuthInfo handle to set the connection class. The connection class is a string attribute. OCI supports a maximum connection class length of 1024 bytes. The asterisk (*) is a special character and is not allowed in the connection class name.

Example 9-2 shows that an HRMS application needs sessions identified with the connection class HRMS.

Example 9-2 Setting the Connection Class as HRMS

OCISessionPoolCreate (envhp, errhp, spoolhp, &poolName, &poolNameLen, "HRDB",
    strlen("HRDB"), 0, 10, 1, "HR", strlen("HR"), "HR", strlen("HR"),
    OCI_SPC_HOMOGENEOUS);
 
OCIAttrSet (authInfop, OCI_HTYPE_AUTHINFO, "HRMS", strlen ("HRMS"),
    OCI_ATTR_CONNECTION_CLASS, errhp);
OCISessionGet (envhp, errhp, &svchp, authInfop, poolName, poolNameLen, NULL, 0,
    NULL, NULL, NULL, OCI_SESSGET_SPOOL);

Example 9-3 shows that a recruitment application needs sessions identified with the connection class RECMS.

Example 9-3 Setting the Connection Class as RECMS

OCISessionPoolCreate (envhp, errhp, spoolhp, &poolName, &poolNameLen, "HRDB",
    strlen("HRDB"), 0, 10, 1, "HR", strlen("HR"), "HR", strlen("HR"),
    OCI_SPC_HOMOGENEOUS);
 
OCIAttrSet (authInfop, OCI_HTYPE_AUTHINFO,  "RECMS", strlen("RECMS"),
    OCI_ATTR_CONNECTION_CLASS, errhp);
OCISessionGet (envhp, errhp, &svchp, authInfop, poolName, poolNameLen, NULL, 0,
    NULL, NULL, NULL, OCI_SESSGET_SPOOL);

Defaults for Session Purity and Connection Class

Table 9-1 illustrates the defaults used in various client scenarios.

Table 9-1 Defaults Used in Various Client Scenarios

Attribute or Setting Application Uses OCISessionGet() from Session Pool Other Connections Are Not Obtained from OCI Session Pool

OCI_ATTR_PURITY

OCI_ATTR_PURITY_SELF

OCI_ATTR_PURITY_NEW

OCI_ATTR_CONNECTION_CLASS

OCI-generated globally unique name for each client-side session pool that is used as the default connection class for all connections in the OCI session pool.

SHARED

Sharing of sessions

Sharing of sessions between threads requesting sessions from the OCI session pool.

Sharing among all connections of a particular database using the default SHARED connection class.

Starting the Database Resident Connection Pool

The database administrator (DBA) must log on as SYSDBA and start the default pool, SYS_DEFAULT_CONNECTION_POOL, using DBMS_CONNECTION_POOL.START_POOL with the default settings.

For detailed information about configuring the pool, see Oracle Database Administrator's Guide.

Enabling Database Resident Connection Pooling

Any application can benefit from database resident connection pool by specifying :POOLED in the Easy Connect string (see Example 9-4) or by specifying (SERVER=POOLED) in the TNS connect string (see Example 9-5).

Example 9-4 Specifying :POOLED in the Easy Connect String for Enabling DRCP

oraclehost.company.com:1521/books.company.com:POOLED

Example 9-5 Specifying SERVER=POOLED in a TNS Connect String for Enabling DRCP

BOOKSDB = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclehost.company.com)
     (PORT=1521))(CONNECT_DATA = (SERVICE_NAME=books.company.com)(SERVER=POOLED)))

Benefiting from the Scalability of DRCP in an OCI Application

Consider the following three types of application scenarios and note how each benefits from DRCP:

  • Applications that do not use OCI session pooling and also do not specify any connection class or purity setting (or specify a purity setting of NEW) get a new session from the DRCP. Similarly, when the application releases a connection back to the pool, the session is not shared with other instances of the same application by default. SQL*Plus is an example of a client that does not use OCI session pooling. It holds on to connections even when the connection is idle. As result, the pool server remains assigned to the client if the client session exists or if the client session does not log off. The application, however, does get the benefit of reusing an existing pooled server process.

  • Applications that use the OCISessionGet() call outside of an OCI session pool, or to specify the connection class and set purity=SELF can reuse both DRCP pooled server processes and sessions. However, following an OCISessionRelease() call, OCI terminates the connection to the connection broker. On the next OCISessionGet() call, the application reconnects to the broker. Once it reconnects, the DRCP assigns a pooled server (and session) belonging to the connection class specified. Reconnecting, however, incurs the cost of connection establishment and reauthentication. Such applications achieve better sharing of DRCP resources (processes and sessions) but do not get the benefit of caching connections to the connection broker.

  • Applications that use OCI session pool APIs and specify the connection class and set purity=SELF make full use of the DRCP functionality through reuse of both the pooled server process and the associated session. They get the benefit of cached connections to the connection broker. Cached connections do not incur the cost of reauthentication on the OCISessionGet() call.

Best Practices for Using DRCP

The steps to design an application that can benefit from the full power of DRCP are very similar to the steps required for an application that uses the OCI session pool as described in "Using OCI Session Pooling" and "OCI Calls for Session Pooling".

The only additional step is that for best performance, when deployed to run with DRCP, the application should specify an explicit connection class setting.

Multiple instances of the same application should specify the same connection class setting for best performance and enhanced sharing of DRCP resources. Ensure that the different instances of the application can share database sessions.

Example 9-6 shows a database resident connection pooling DRCP application.

Example 9-6 Database Resident Connection Pooling Application

/* Assume that all necessary handles are allocated. */
 
/*   This middletier uses a single database user. Create a homogeneous
     client-side session pool */
OCISessionPoolCreate (envhp, errhp, spoolhp, &poolName, &poolNameLen, "BOOKSDB",
    strlen("BOOKSDB"), 0, 10, 1, "SCOTT", strlen("SCOTT"), "password",
    strlen("password"), OCI_SPC_HOMOGENEOUS);
 
while (1)
{
   /* Process a client request */
   WaitForClientRequest();
   /* Application function */
 
   /* Set the Connection Class on the OCIAuthInfo handle that is passed as
      argument to OCISessionGet*/
   
   OCIAttrSet (authInfop, OCI_HTYPE_AUTHINFO,  "BOOKSTORE", strlen("BOOKSTORE"),
               OCI_ATTR_CONNECTION_CLASS, errhp);
 
   /* Purity need not be set, as default is OCI_ATTR_PURITY_SELF for OCISessionPool
       connections */
 
   /* You can get a SCOTT session released by Mid-tier 2 */
   OCISessionGet(envhp, errhp, &svchp, authInfop, poolName, poolNameLen, NULL, 0,
                 NULL, NULL, NULL, OCI_SESSGET_SPOOL); 
 
   /* Database calls using the svchp obtained above  */
   OCIStmtExecute(...)
 
   /* This releases the pooled server on the database for reuse */
   OCISessionRelease (svchp, errhp, NULL, 0, OCI_DEFAULT);
}
 
/* Mid-tier is done - exiting */
OCISessionPoolDestroy (spoolhp, errhp, OCI_DEFAULT);

Example 9-7 and Example 9-8 show two deployment examples, each based on code in Example 9-6, in which code is deployed in 10 middle-tier hosts servicing the BOOKSTORE application.

For the first deployment example, assume that the database used is Oracle Database 11g (or earlier) in dedicated server mode but with DRCP not enabled. The client side has 11g libraries. Example 9-7 shows the connect string to use for this deployment. In this case, the application obtains dedicated server connections from the database.

Example 9-7 Connect String to Use for a Deployment in Dedicated Server Mode with DRCP Not Enabled

BOOKSDB = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclehost.company.com)
   (PORT=1521))(CONNECT_DATA = (SERVICE_NAME=books.company.com)))

For the second deployment example, assume that DRCP is enabled on the Oracle Database 11g database. Now all the middle-tier processes can benefit from the pooling capabilities offered by DRCP. The database resource requirement with DRCP is much less than what would be required with dedicated server mode. Example 9-8 shows how you change the connect string for this type of deployment.

Example 9-8 Connect String to Use for a Deployment with DRCP Enabled

BOOKSDB = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclehost.company.com)
  (PORT=1521))(CONNECT_DATA = (SERVICE_NAME=books.company.com)(SERVER=POOLED)))

Compatibility and Migration

An OCI application linked with Oracle Database 11g client libraries works unaltered against:

  • An Oracle Database 11g database with DRCP disabled

  • A database server from a release earlier than Oracle Database 11g

  • An Oracle Database 11g database server with DRCP enabled, when deployed with the DRCP connect string

Suitable clients benefit from enhanced scalability offered by DRCP if they are appropriately modified to use the OCI session pool APIs with the connection class and purity settings as previously described.

Restrictions on Using Database Resident Connection Pooling

The following actions cannot be performed or used with pooled servers:

  • Shutting down the database

  • Stopping the database resident connection pool

  • Change the password for the connected user

  • Using shared database links to connect to a database resident connection pool that is on a different instance

  • Using Advanced Security Options (ASO) with TCPS

  • Using migratable sessions on the server side, directly by using the OCI_MIGRATE option or indirectly by using the OCISessionPoolCreate() call

  • Using initial client roles

  • Using Application context attributes such as OCI_ATTR_APPCTX_NAME and OCI_ATTR_APPCTX_VALUE

DDL statements that pertain to database users in the pool need to be performed carefully, as the pre-DDL sessions in the pool can still be given to clients post-DDL. For example, while dropping users, ensure that there are no sessions of that user in the pool and no connections to the Broker that were authenticated as that user.

Sessions with explicit roles enabled, that are released to the pool, can be later handed out to connections (of the same user) that need the default logon role. Avoid releasing sessions with explicit roles, and instead terminate them.

Note:

You can use Oracle Advanced Security features such as encryption and strong authentication with DRCP.

Using DRCP with Custom Pools

DRCP is well integrated with OCI session pooling as described in "Database Resident Connection Pooling". Oracle highly recommends using OCI session pool as it is already integrated with DRCP, FAN, and RLB.

However, if an application is built using its own custom connection pool (or if the application does not use any pooling at all, but has periods of time when the session is not used and the application does not depend on getting back the specific session for correctness), it can still integrate with DRCP. You can do this by enabling the OCI_ATTR_SESSION_STATE attribute as described in "Marking Sessions Explicitly as Stateful or Stateless".

When an application flags a session as being OCI_SESSION_STATELESS, OCI benefits from this session annotation to return the session transparently to the DRCP pool (when DRCP is enabled). Similarly, when the application indicates the session as being OCI_SESSION_STATEFUL, OCI benefits from this changed session state annotation to transparently check out an appropriate session from the DRCP pool.

Applications should mark session state as promptly as possible to enable efficient utilization of the underlying database resources.

Note:

Other DRCP attributes such as connection class and purity still must be specified as previously described in detail.

Marking Sessions Explicitly as Stateful or Stateless

An application typically requires a specific database session for the duration of a unit of work. The session is said to be STATEFUL for this duration. At the end of this unit of work, if the application does not depend on retaining the specific session for subsequent units of work, the session is said to be STATELESS.

As the application detects when a session transitions from being STATEFUL to STATELESS and vice versa, the application can explicitly inform OCI regarding these transitions by using OCI_ATTR_SESSION_STATE.

This indication by the application or caller can allow OCI and Oracle Database to take advantage of this information for transparently performing certain scalability optimizations. For example, the session could be given to someone else who needs it when the application is not working on it. Or, the session could be replaced by a different session when the application needs it again.

See Also:

"Using DRCP with Custom Pools"

Here is an example of marking the state of sessions in a code fragment:

wait_for_transaction_request();
do {
 
ub1 state;
 
/* mark  database session as STATEFUL  */
state = OCI_SESSION_STATEFUL;
checkerr(errhp, OCIAttrSet(usrhp, OCI_HTYPE_SESSION,
        &state, 0, OCI_ATTR_SESSION_STATE, errhp));
/* do database work consisting of one or more related calls to the database */
 
...
 
/* done with database work, mark session as stateless */
state = OCI_SESSION_STATELESS;
checkerr(errhp, OCIAttrSet(usrhp, OCI_HTYPE_SESSION,
         &state, 0,OCI_ATTR_SESSION_STATE, errhp));
 
wait_for_transaction_request();
 
} while(not _done);

If a session is obtained from outside an OCI session pool, the session starts as OCI_SESSION_STATEFUL and remains OCI_SESSION_STATEFUL throughout the life of the session unless the application explicitly changes it to OCI_SESSION_STATELESS.

If a session is obtained from an OCI session pool, the session is by default marked as OCI_SESSION_STATEFUL when the first call is initiated on that session after getting it from the pool. The session is also by default marked as being OCI_SESSION_STATELESS when it is released to the pool. Hence, there is no need to set this attribute explicitly with an OCI session pool. OCI session pooling does this transparently. Use this attribute only if you are not using OCI session pooling.

See Also:

"OCI_ATTR_SESSION_STATE"

DRCP with Real Application Clusters

When the Database Resident Connection Pool is configured in a database in a Real Application Clusters environment, the pool configuration is applied to each of the database instances. Starting or stopping of the pool on one instance will start or stop the pool on all the instances.

DRCP with Data Guard

The Database Resident Connection Pool has certain conditions to operate in a Data Guard environment.

  • Starting the pool: The pool can be started on a physical standby database only if the pool has been already started on the primary database. If the pool is down on the primary, it cannot be started on the standby database.

  • Stopping the pool: The pool cannot be stopped on a physical standby database if it is up and running on the primary database. It can only be stopped if on the primary database, the pool is not running.

  • Pool parameters cannot be configured, restored to defaults, or altered on a physical standby database.

  • When role reversal takes place, that is, the Primary goes down and the Secondary database takes up the role of the Primary database, the limitations mentioned previously for the physical standby database no longer holds. Since the Standby database has now become the Primary, all pool operations are allowed.

  • On a logical standby database, all pool operations are allowed.

When to Use Connection Pooling, Session Pooling, or Neither

This section includes the following topics:

If database sessions are not reusable by mid-tier threads (that is, they are stateful) and the number of back-end server processes may cause scaling problems on the database, use OCI connection pooling.

If database sessions are reusable by mid-tier threads (that is, they are stateless) and the number of back-end server processes may cause scaling problems on the database, use OCI session pooling.

If database sessions are not reusable by mid-tier threads (that is, they are stateful) and the number of back-end server processes is never large enough to potentially cause any scaling issue on the database, there is no need to use any pooling mechanism.

Note:

Having nonpooled sessions or connections results in tearing down and re-creating the database session/connection for every mid-tier user request. This can cause severe scaling problems on the database side and excessive latency for the fulfillment of the request. Hence, Oracle strongly recommends that you adopt one of the pooling strategies for mid-tier applications based on whether the database session is stateful or stateless.

In connection pooling, the pool element is a connection and in session pooling, the pool element is a session.

As with any pool, the pooled resource is locked by the application thread for a certain duration until the thread has done its job on the database and the resource is released. The resource is unavailable to other threads during its period of use. Hence, application developers must be aware that any kind of pooling works effectively with relatively short tasks. However, if the application is performing a long-running transaction, it may deny the pooled resource to other sharers for long periods of time, leading to starvation. Hence, pooling should be used in conjunction with short tasks, and the size of the pool should be sufficiently large to maintain the desired concurrency of transactions.

Note the following additional information about connection pooling and session pooling:

  • OCI Connection Pooling

    Connections to the database are pooled. Sessions are created and destroyed by the user. Each call to the database picks up an appropriate available connection from the pool.

    The application is multiplexing several sessions over fewer physical connections to the database. The users can tune the pool configuration to achieve required concurrency.

    The life-time of the application sessions is independent of the life-time of the cached pooled connections.

  • OCI Session Pooling

    Sessions and connections are pooled by OCI. The application gets sessions from the pool and releases sessions back to the pool.

Functions for Session Creation

OCI offers the following functions for session creation:

  • OCILogon()

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

    See Also:

  • OCILogon2()

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

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

    See Also:

    "OCILogon2()"

  • OCISessionBegin()

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

    See Also:

    "OCISessionBegin()"

  • OCISessionGet()

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

    See Also:

    "OCISessionGet()"

Choosing Between Different Types of OCI Sessions

OCI includes the following types of sessions:

  • Basic OCI sessions

    The basic OCI session works by using user name and password over a dedicated OCI server handle. This is the no-pool mechanism. See When to Use Connection Pooling, Session Pooling, or Neither for information of when to use it.

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

  • Session pool sessions

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

    See When to Use Connection Pooling, Session Pooling, or Neither on connection pool sessions versus session pooling sessions versus no-pooling sessions.

  • Connection pool sessions

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

    The sessions are automatically migratable between the server handles of the connection pool. Each session can have user name and password or be a proxy session. See When to Use Connection Pooling, Session Pooling, or Neither on connection pool sessions versus session pooling sessions versus no-pooling sessions.

  • Sessions sharing a server handle

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

  • Proxy sessions

    Proxy sessions are useful if the password of the client must be protected from the middle tier. Proxy sessions can also be part of an OCI connection pool or an OCI session pool.

    See Also:

    "Middle-Tier Applications in OCI"

  • Migratable Sessions

With transaction handles being migratable, there should be no need for applications to use migratable sessions, instead use OCI connection pooling.

See Also:

"OCI Session Management"

Statement Caching in OCI

This section includes the following topics:

Statement caching refers to the feature 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. You can use statement caching with connection pooling and with session pooling, and improve performance and scalability. You can use it without session pooling as well. OCI calls that implement statement caching are:

Statement Caching Without Session Pooling in OCI

To perform statement caching without session pooling, users perform the usual OCI steps to log on. The call to obtain a session has a mode that specifies whether statement caching is enabled for the session. Initially the statement cache is empty. Developers try to find a statement in the cache using the statement text. If the statement exists, the API returns a previously prepared statement handle; otherwise, it returns a newly prepared statement handle.

The application developer can perform binds and defines and then simply execute and fetch the statement before returning the statement to the cache. If the statement handle is not found in the cache, the developer must set different attributes on the handle in addition to the other steps.

OCIStmtPrepare2() takes a mode that determines if the developer wants a prepared statement handle or a null statement handle if the statement is not found in the cache.

The pseudocode looks like this:

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, ...);
OCIStmtFetch2(svchp, ...);
OCIStmtRelease(stmthp, ...);
...

Statement Caching with Session Pooling in OCI

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

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

Note:

You can change the attributes after acquiring a session. However, once an attribute is changed, it will remain set on the underlying physical session. This value will not be reset back implicitly while releasing the session back to the session pool. Hence, it is the developer's responsibility to maintain the state of the sessions before releasing the session using OCIStmtRelease().

Enabling or disabling of statement caching is allowed on individual pooled sessions as it is on nonpooled sessions.

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

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

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

Rules for Statement Caching in OCI

Here are some rules to follow for statement caching in OCI:

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

  • You are required to keep one service context per session. Any statement handle obtained using OCIStmtPrepare2() with a certain service context should be subsequently used only in conjunction with the same service context, and never with a different service context.

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

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

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

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

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

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

    See Also:

Bind and Define Optimization in Statement Caching

To avoid repeated bind and define operations on statements in the cache by the application, the application can register an opaque context with a statement taken from the statement cache and register a callback function with the service context. The application data such as bind and define buffers can be enclosed in the opaque context. This context is registered with the statement the first time it is taken from the cache. When a statement is taken from the cache the second time and onwards, the application can reuse the bind and define buffers, that it had registered with that statement. It is still the application's responsibility to manage the bind and defines. It can reuse both the bind and define data and the buffers, or it can change only the data and reuse the buffers, or it can free and reallocate the buffers if the current size is not enough. In the last case, it must rebind and redefine. To clean up the memory allocated by the application toward these bind and define buffers, the callback function is called during aging out of the statement or purging of the whole cache as part of session closure. The callback is called for every statement being purged. The application frees the memory and does any other cleanup required, inside the callback function. Example 9-9 shows the pseudocode.

Example 9-9 Optimizing Bind and Define Operations on Statements in the Cache

Get the statement using OCIStmtPrepare2(...)
 
Get the opaque context from the statement if it exists
 
If opaque context does not exist
 
{
 
  Allocate fetch buffers, do the OCIBindByPos, OCIDefineByPos, and so forth
 
  Enclose the buffer addresses inside a context and set the context and
  callback function on the statement
 
}
Execute/Fetch using the statement, and process the data in the fetch buffers. 
 
OCIStmtRelease() that statement
 
Next OCIStmtPrepare2()
 
OCIAttrGet() opaque application context from statement handle
 
Execute/Fetch using the statement and process the data in the fetch buffers.
 
OCIStmtRelease()
 
. . .
 
void callback_fn (context, statement, mode)
 
{
 
   /* mode= OCI_CBK_STMTCACHE_STMTPURGE means this was called when statement was
      aging out of the statement cache or if the session is ended */
 
  <free the buffers in the context.>
 
}

See Also:

OCI Statement Caching Code Example

See cdemostc.c in directory demo for a working example of statement caching.

User-Defined Callback Functions in OCI

This section includes the following topics:

Oracle Call Interface can execute user-specific code in addition to OCI calls. You can use this functionality for:

  • Adding tracing and performance measurement code to enable users to tune their applications

  • Performing preprocessing or postprocessing code for specific OCI calls

  • Accessing other data sources with OCI by using the native OCI interface for Oracle Databases and directing the OCI calls to use user callbacks for non-Oracle data sources

The OCI callback feature provides support for calling user code before or after executing the OCI calls. It also allows the user-defined code to be executed instead of executing the OCI code.

The user callback code can 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 Windows, or shared libraries on Solaris, 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.

Registering User Callbacks in OCI

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

See Also:

"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 it is an entry callback, it is called when the program enters the OCI function.

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

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

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

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

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:

"OCIUserCallbackRegister()"

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 must use the following syntax:

typedef sword (*OCIUserCallback)
     (void *ctxp,      /* context for the user callback*/
      void *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, however, the callback returns any return code other than OCI_CONTINUE, the last returned return code becomes the return code for the call. At this point, the value returned for *errnop 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 are 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:

Appendix B, "OCI Demonstration Programs"

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

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

User Callback Control Flow

Example 9-10 shows pseudocode that describes the overall processing of a typical OCI call.

Example 9-10 Pseudocode That 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
}

User Callback 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 errcodep parameters of the replacement callback of the OCIErrorGet() call.

If, however, the callbacks are either partially overriding OCI code, or just doing some other postprocessing, then they can use the exit callback to modify the error text and errcodep parameters of the OCIErrorGet() call 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 must 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 nonintrusively. 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 deregister user callbacks as needed when receiving control at runtime.

A makefile (ociucb.mk on Solaris) 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.

Setting an operating system environment variable, ORA_OCI_UCBPKG, controls the loading of the package. 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, in the past the package was specified as:

setenv ORA_OCI_UCBPKG mypkg

Currently, you can still specify the package as before, 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 Solaris or ociucb.dll on a Windows system. To load the ociucb package, the environmental variable ORA_OCI_UCBPKG must be set to ociucb. On Solaris, if the package name ends with .so, OCIEnvCreate() or OCIEnvNlsCreate() 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 operating system. For further information about user-defined callbacks, see your operating system-specific documentation on compiling and linking applications.

Package Format

In the past, a package had to specify the source code for the OCIEnvCallback() function. However, the OCIEnvCallback() function is obsolete. Instead, the package source must provide two functions. The first function must 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) must contain a fooInit() function with a call to OCISharedLibInit() function specified exactly as:

sword fooInit(metaCtx, libCtx, argfmt, argc, argv)
      void *     metaCtx;         /* The metacontext */
      void *     libCtx;          /* The context for this package. */
      ub4        argfmt;          /* package argument format */
      sword      argc;            /* package arg count*/
      void *     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 is named packagename suffixed with the word EnvCallback.

This function is a replacement for OCIEnvCallback(). Currently, 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, void *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 usrmempp 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 Solaris) in the demo directory to create the package. Please note that this may be different on other operating systems. The demo directory also contains full user callback demo programs (cdemoucb.c, cdemoucbl.c) illustrating this.

User Callback Chaining

User callbacks can 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.

The OCIUserCallbackGet() function determines which function and context is registered for an OCI call.

See Also:

"OCIUserCallbackGet()"

Accessing Other Data Sources Through OCI

Because Oracle Database is the predominant database software 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. 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 to access 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

Suppose that there are five packages each registering entry, replacement, and exit callbacks for the OCIStmtPrepare() call. That is, the ORA_OCI_UCBPKG variable is set as shown in Example 9-11.

Example 9-11 Environment Variable Setting for the ORA_OCI_UCBPKG Variable

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 shown in Example 9-12.

Example 9-12 Specifying the pkgNInit() and PkgNEnvCallback() Functions

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

Example 9-13 shows how the pkgNEnvCallback() function registers the entry, replacement, and exit callbacks.

Example 9-13 Using pkgNEnvCallback() to Register Entry, Replacement, and Exit Callbacks

pkgNEnvCallback(OCIEnv *env, ub4 mode, size_t xtramemsz,
                                void *usrmemp, OCIUcb *ucbDesc)
{
  OCIHandleAlloc((void *)env, (void **)&errh, OCI_HTYPE_ERROR, (size_t) 0,
        (void **)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, Example 9-14 shows how in the source code for the application, user callbacks can be registered with the NULL ucbDesc.

Example 9-14 Registering User Callbacks with the NULL ucbDesc

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

Example 9-15 shows that when the OCIStmtPrepare() call is executed, the callbacks are called in the following order.

Example 9-15 Using the OCIStmtPrepare() Call to Call the Callbacks in 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 are bypassed. Instead, pkg5_exit_callback_fn() is executed next.

OCI Callbacks from External Procedures

There are several OCI functions that you can use as callbacks from external procedures. These functions are listed in Chapter 20. For information about writing C subroutines that can be called from PL/SQL code, including a list of which OCI calls you can use and some example code, see Oracle Database Advanced Application Developer's Guide.

Transparent Application Failover in OCI

This section includes the following topics:

Transparent application failover (TAF) is a client-side feature designed to minimize disruptions to end-user applications that occur when database connectivity fails because of instance or network failure. TAF can be implemented on a variety of system configurations including Oracle Real Application Clusters (Oracle RAC) and Oracle Data Guard physical standby databases. TAF can also be used after restarting a single instance system (for example, when repairs are made).

TAF can be configured to restore database sessions and, optionally, to replay open queries. Prior to Oracle Database 10g Release 2 (10.2), TAF with the SELECT failover option would be engaged only on the statement that was in use at the time of a failure. For example, if there were 10 statement handles in use by the application, and statement 7 was the failure-time statement (the statement in use when the failure happened), statements 1 through 6 and 8 through 10 would have to be reexecuted after statement 7 was failed over using TAF.

Starting with Oracle Database 10g Release 2 (10.2), this has been improved. Now all statements that an application attempts to use after a failure attempt failover. That is, an attempt to execute or fetch against other statements engages TAF recovery just as for the failure-time statement. Subsequent statements may now succeed (whereas in the past they failed), or the application may receive errors corresponding to an attempted TAF recovery (such as ORA-25401).

Note:

TAF is not supported for remote database links or for DML statements.

Configuring Transparent Application Failover

TAF can be configured on both the client side and the server side. If both are configured, server-side settings take precedence.

Configure TAF on the client side by including the FAILOVER_MODE parameter in the CONNECT_DATA portion of a connect descriptor.

See Also:

Oracle Database Net Services Reference for more information about client-side configuration of TAF (Connect Data Section)

Configure TAF on the server side by modifying the target service with the DBMS_SERVICE.MODIFY_SERVICE packaged procedure.

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

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about the server-side configuration of TAF (DBMS_SERVICE)

Transparent Application Failover Callbacks in OCI

Because of the delay that can occur during failover, the application developer may want to inform the user that failover is in progress, and request that the user wait for notification that failover is complete. Additionally, the session on the initial instance may have received some ALTER SESSION commands. These ALTER SESSION commands are not automatically replayed on the second instance. Consequently, the developer may want to replay them on the second instance. OCIAttrSet() calls that affect the session must also be reexecuted.

To accommodate these requirements, the application developer can register a failover callback function. If failover occurs, the callback function is invoked several times while reestablishing the user's session.

The first call to the callback function occurs when the database 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.

Once the connection has been reestablished, the client may want 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 cannot occur. Additionally, the callback is called each time a user handle besides the primary handle is reauthenticated on the new connection. Because each user handle represents a server-side session, the client may want to replay ALTER SESSION commands for that session.

See Also:

"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 ( void       * svchp, 
                       void       * envhp, 
                       void       * fo_ctx, 
                       ub4        fo_type, 
                       ub4        fo_event );

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

svchp

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

envhp

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

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

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 as follows:

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

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

fo_event

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

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

  • OCI_FO_END indicates successful completion of failover.

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

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

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

Failover Callback Registration

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

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

An example of callback registration is included as part of Example 9-17.

Failover Callback Example

This section shows an example of a simple user-defined callback function definition (see Example 9-16), failover callback registration (see Example 9-17), and failover callback unregistration (see Example 9-18).

Example 9-16 User-Defined Failover Callback Function Definition

sb4  callback_fn(svchp, envhp, fo_ctx, fo_type, fo_event)
void * svchp;
void * envhp;
void *fo_ctx;
ub4 fo_type;
ub4 fo_event;
{
switch (fo_event) 
   {
   case OCI_FO_BEGIN:
   {
     printf(" Failing Over ... Please stand by \n");
     printf(" Failover type was found to be %s \n",
                     ((fo_type==OCI_FO_SESSION) ? "SESSION" 
                     :(fo_type==OCI_FO_SELECT) ? "SELECT"
                     : "UNKNOWN!")); 
     printf(" Failover Context is :%s\n", 
                    (fo_ctx?(char *)fo_ctx:"NULL POINTER!"));
     break;
   }
   case OCI_FO_ABORT:
   {
     printf(" Failover stopped. Failover will not occur.\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;
}

Example 9-17 Failover Callback Registration

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

Example 9-18 Failover Callback Unregistration

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

Handling OCI_FO_ERROR

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

Note:

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

Failover does not work if a LOB column is part of the select list.

Consider the timeline of events presented in Table 9-2.

Table 9-2 Time and Event

Time Event

T0

Database fails (failure lasts until T5).

T1

Failover is triggered by user activity.

T2

User attempts to reconnect; attempt fails.

T3

Failover callback is invoked with OCI_FO_ERROR.

T4

Failover callback enters a predetermined sleep period.

T5

Database comes back up again.

T6

Failover callback triggers a 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.

Example 9-19 shows a callback function that you can use to implement the failover strategy similar to the scenario described earlier. In this case, the failover callback enters a loop in which it sleeps and then reattempts failover until it is successful:

Example 9-19 Callback Function That Implements a Failover Strategy

/*--------------------------------------------------------------------*/
/* the user-defined failover callback  */
/*--------------------------------------------------------------------*/
sb4  callback_fn(svchp, envhp, fo_ctx, fo_type, fo_event )
void * svchp;
void * envhp;
void *fo_ctx;
ub4 fo_type;
ub4 fo_event;
{
   OCIError *errhp;
   OCIHandleAlloc(envhp, (void **)&errhp, (ub4) OCI_HTYPE_ERROR,
              (size_t) 0, (void **) 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 occur.\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;
}

HA Event Notification

This section includes the following topics:

Suppose that a user employs a web browser to log in to an application server that accesses a back-end database server. Failure of the database instance can result in a wait that can be up to minutes in duration before the failure is known to the user. The ability to quickly detect failures of server instances, communicate this to the client, close connections, and clean up idle connections in connection pools is provided by HA event notification.

For high availability clients connected to an Oracle RAC database, you can use HA event notification to provide a best-effort programmatic signal to the client if there is a database failure. Client applications can register a callback on the environment handle to signal interest in this information. When a significant failure event occurs that applies to a connection made by this client, the callback is invoked, with information concerning the event (the event payload) and a list of connections (server handles) that were disconnected because of the failure.

For example, consider a client application that has two connections to instance A and two connections to instance B of the same database. If instance A goes down, a notification of the event is sent to the client, which then disconnects the two connections to instance B and invokes the registered callback. Note that if another instance C of the same database goes down, the client is not notified (because it does not affect any of the client's connections).

The HA event notification mechanism improves the response time of the application in the presence of failure. Before the mechanism was introduced in Oracle Database 10g Release 2 (10.2), a failure would result in the connection being broken only after the TCP timeout interval expired, which could take minutes. With HA event notification, the standalone, connection pool, and session pool connections are automatically broken and cleaned up by OCI, and the application callback is invoked within seconds of the failure event. If any of these server handles are TAF-enabled, failover is also automatically engaged by OCI.

Applications must connect to an Oracle RAC instance to enable HA event notification. Furthermore, these applications must:

  • Initialize the OCI Environment in OCI_EVENTS mode

  • Connect to a service that has notifications enabled (use the DBMS_SERVICE.MODIFY_SERVICE procedure to set AQ_HA_NOTIFICATIONS to TRUE)

  • Link with a thread library

Then these applications can register a callback that is invoked whenever an HA event occurs.

OCIEvent Handle

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

See Also:

"Event Handle Attributes"

OCI Failover for Connection and Session Pools

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

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

OCI Failover for Independent Connections

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

Event Callback

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

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

In this signature evtctx is the client context, and OCIEvent is an event handle that is opaque to the OCI library. The other input argument is eventhp, the event handle (the attributes associated with an event).

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

  • OCI_ATTR_EVTCBK is of data type OCIEventCallback *. It is read-only.

  • OCI_ATTR_EVTCTX is of data type void *. It is also read-only.

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

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

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

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

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

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

Custom Pooling: Tagged Server Handles

The following features apply to custom pools:

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

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

Note:

You are free to define the precise contents of the server handle's user memory segment to facilitate cleanup activities from within the HA event callback (or for other purposes if needed) because OCI does not write or read from this memory in any way. The user memory segment is freed with the OCIHandleFree() call on the server handle.

Example 9-20 shows an example of event notification.

Example 9-20 Event Notification

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

Determining Transparent Application Failover (TAF) Capabilities

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

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

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

OCI and Streams Advanced Queuing

This section includes the following topics:

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

Note:

To use Streams Advanced Queuing, you must be using the Enterprise Edition of Oracle Database.




See Also:








OCI Streams Advanced Queuing Functions


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



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




See Also:

"Streams Advanced Queuing and Publish-Subscribe Functions"








OCI Streams Advanced Queuing Descriptors


The following descriptors are used by OCI Streams AQ operations:


    

  • 

    OCIAQEnqOptions
    

    • 

  • 

    OCIAQDeqOptions
    

    • 

  • 

    OCIAQMsgProperties
    

    • 

  • 

    OCIAQAgent
    

    • 



You can allocate these descriptors with 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 that can be set or read.




See Also:

"Streams Advanced Queuing Descriptor Attributes"








Streams Advanced Queuing in OCI Versus PL/SQL


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




Table 9-3 AQ Functions






PL/SQL Function
OCI Function








DBMS_AQ.ENQUEUE




OCIAQEnq()








DBMS_AQ.DEQUEUE




OCIAQDeq()








DBMS_AQ.LISTEN




OCIAQListen(), OCIAQListen2()








DBMS_AQ.ENQUEUE_ARRAY




OCIAQEnqArray()








DBMS_AQ.DEQUEUE_ARRAY




OCIAQDeqArray()











Table 9-4 compares the parameters for the enqueue functions.




Table 9-4 Enqueue Parameters






DBMS_AQ.ENQUEUE Parameter
OCIAQEnq() Parameter








queue_name




queue_name








enqueue_options




enqueue_options








message_properties




message_properties








payload




payload








msgid




msgid








-




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











Table 9-5 compares the parameters for the dequeue functions.




Table 9-5 Dequeue Parameters






DBMS_AQ.DEQUEUE Parameter
OCIAQDeq() Parameter








queue_name




queue_name








dequeue_options




dequeue_options








message_properties




message_properties








payload




payload








msgid




msgid








-




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











Table 9-6 compares parameters for the listen functions.




Table 9-6 Listen Parameters






DBMS_AQ.LISTEN Parameter
OCIAQListen2() Parameter








agent_list




agent_list








wait




wait








agent




agent








listen_delivery_mode




lopts








-




Note: OCIAQListen2() requires the following additional parameters: svchp, errhp, agent_list, num_agents, agent, lmops, and flags.











Table 9-7 compares parameters for the array enqueue functions.




Table 9-7 Array Enqueue Parameters






DBMS_AQ.ENQUEUE_ARRAY Parameter
OCIAQEnqArray() Parameter








queue_name




queue_name








enqueue_options




enqopt








array_size




iters








message_properties_array




msgprop








payload_array




payload








msgid_array




msgid








-




Note: OCIAQEnqArray() requires the following additional parameters: svch, errh, payload_tdo, payload_ind, ctxp, enqcbfp, and flags.











Table 9-8 compares parameters for the array dequeue functions.




Table 9-8 Array Dequeue Parameters






DBMS_AQ.DEQUEUE_ARRAY Parameter
OCIAQDeqArray() Parameter








queue_name




queue_name








dequeue_options




deqopt








array_size




iters








message_properties_array




msgprop








payload_array




payload








msgid_array




msgid








-




Note: OCIAQDeqArray() requires the following additional parameters: svch, errh, msgprop, payload_tdo, payload_ind, ctxp, deqcbfp, and flags.











Table 9-9 compares parameters for the agent attributes.




Table 9-9 Agent Parameters






PL/SQL Agent Parameter
OCIAQAgent Attribute








name




OCI_ATTR_AGENT_NAME








address




OCI_ATTR_AGENT_ADDRESS








protocol




OCI_ATTR_AGENT_PROTOCOL











Table 9-10 compares parameters for the message properties.




Table 9-10 Message Properties






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








transaction_group




OCI_ATTR_TRANSACTION_NO








original_msgid




OCI_ATTR_ORIGINAL_MSGID








delivery_mode




OCI_ATTR_MSG_DELIVERY_MODE











Table 9-11 compares enqueue option attributes.




Table 9-11 Enqueue Option Attributes






PL/SQL Enqueue Option
OCIAQEnqOptions Attribute








visibility




OCI_ATTR_VISIBILITY








relative_msgid




OCI_ATTR_RELATIVE_MSGID








sequence_deviation




OCI_ATTR_SEQUENCE_DEVIATION


(deprecated)








transformation




OCI_ATTR_TRANSFORMATION








delivery_mode




OCI_ATTR_MSG_DELIVERY_MODE











Table 9-12 compares dequeue option attributes.




Table 9-12 Dequeue Option Attributes






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








deq_condition




OCI_ATTR_DEQCOND








transformation




OCI_ATTR_TRANSFORMATION








delivery_mode




OCI_ATTR_MSG_DELIVERY_MODE













Note:

OCIAQEnq() returns the error ORA-25219 while specifying the enqueue option OCI_ATTR_SEQUENCE along with OCI_ATTR_RELATIVE_MSGID. This happens when enqueuing two messages. For the second message, enqueue options OCI_ATTR_SEQUENCE and OCI_ATTR_RELATIVE_MSGID are set to dequeue this message before the first one. An error is not returned if you do not specify the sequence but, of course, the message is not dequeued before the relative message.

OCIAQEnq() does not return an error if the OCI_ATTR_SEQUENCE attribute is not set, but the message is not dequeued before the message with relative message ID.










Buffered Messaging


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



Example 9-21 shows an example of enqueue buffered messaging.




Example 9-21 Enqueue Buffered Messaging


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




Example 9-22 shows an example of dequeue buffered messaging.




Example 9-22 Dequeue Buffered Messaging


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

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






Note:

Array operations are not supported for buffered messaging. Applications can use the OCIAQEnqArray() and OCIAQDeqArray() functions with the array size set to 1.










Publish-Subscribe Notification in OCI


This section includes the following topics:



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




Figure 9-2 Publish-Subscribe Model

Description of Figure 9-2 follows

Description of "Figure 9-2 Publish-Subscribe Model"





An OCI application can:


    

  • 

    Register interest in notifications in the AQ namespace and be notified when an enqueue occurs
    

    • 

  • 

    Register interest in subscriptions to database events and receive notifications when the events are triggered
    

    • 

  • 

    Manage registrations, such as disabling registrations temporarily or dropping the registrations entirely
    

    • 

  • 

    Post or send notifications to registered clients
    

    • 



In all the preceding scenarios the notification can be received directly by the OCI application, or the notification can be sent to a prespecified email address, or it can be sent to a predefined HTTP URL, or a prespecified database PL/SQL procedure can be invoked because 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 in OCI


Registration can be done in two ways:


    

  • 

    Direct registration. You register directly to the database. This way is simple and the registration takes effect immediately. See "Publish-Subscribe Register Directly to the Database".
    

    • 

  • 

    Open registration. You register using Lightweight Directory Access Protocol (LDAP), from which the database receives the registration request. This is useful when the client cannot have a database connection (the client wants to register for a database open event while the database is down), or if the client wants to register for the same event or events in multiple databases simultaneously. See "Open Registration for Publish-Subscribe".
    

    • 






Publish-Subscribe Register Directly to the Database


The following steps are required in an OCI application to register directly 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 operating systems.


    

  1. 

    Call OCIEnvCreate() or OCIEnvNlsCreate() 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. 

  2. 

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

    3. 

  3. 

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

      

    • 

      OCI_ATTR_SUBSCR_NAME - subscription name
      

      • 

    • 

      OCI_ATTR_SUBSCR_NAMESPACE - subscription namespace
      

      • 

    • 

      OCI_ATTR_SUBSCR_HOSTADDR - environment handle attribute that sets the client IP (in either IPv4 or IPv6 format) to which notification is sent
      

      Oracle Database components and utilities support Internet Protocol version 6 (IPv6) addresses.
      

      

      See Also:
      
"OCI_ATTR_SUBSCR_HOSTADDR", "OCI_ATTR_SUBSCR_IPADDR", and Oracle Database Net Services Administrator's Guide for more information about the IPv6 format for IP addresses
      

      • 

    • 

      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_QOSFLAGS - QOS (quality of service) levels with the following values:
      

        

      • 

        If OCI_SUBSCR_QOS_PURGE_ON_NTFN is set, the registration is purged on the first notification.
        

        • 

      • 

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

        • 

      

      • 

    • 

      OCI_ATTR_SUBSCR_TIMEOUT - Registration timeout interval in seconds. The default is 0 if a timeout is not set.
      

      • 

    • 

      OCI_ATTR_SUBSCR_NTFN_GROUPING_CLASS - notification grouping class
      

      Notifications can be spaced out by using the grouping NTFN option with the following constants. A value supported for notification grouping class is:
      

      #define OCI_SUBSCR_NTFN_GROUPING_CLASS_TIME   1 /* time  */

      • 

    • 

      OCI_ATTR_SUBSCR_NTFN_GROUPING_VALUE - notification grouping value in seconds
      

      • 

    • 

      OCI_ATTR_SUBSCR_NTFN_GROUPING_TYPE - notification grouping type
      

      Supported values for notification grouping type:
      

      #define OCI_SUBSCR_NTFN_GROUPING_TYPE_SUMMARY 1  /* summary */
#define OCI_SUBSCR_NTFN_GROUPING_TYPE_LAST    2  /* last */ 

      • 

    • 

      OCI_ATTR_SUBSCR_NTFN_GROUPING_START_TIME - notification grouping start time
      

      • 

    • 

      OCI_ATTR_SUBSCR_NTFN_GROUPING_REPEAT_COUNT - notification grouping repeat count
      

      • 

    

    OCI_ATTR_SUBSCR_NAME, OCI_ATTR_SUBSCR_NAMESPACE, and OCI_ATTR_SUBSCR_RECPTPROTO must be set before you register 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 must 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 must be set.
    

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

    OCI_ATTR_SUBSCR_PAYLOAD is required before the application can perform a post to a subscription.
    

    

    See Also:
    
"Subscription Handle Attributes" and "Creating the OCI Environment" for setting up the environment with mode = OCI_EVENTS | OCI_OBJECT. OCI_OBJECT is required for grouping notifications.
    

    4. 

  4. 

    Set he values of QOS, timeout interval, namespace, and port (see Example 9-23).
    

    

    See Also:
    
"Setting QOS, Timeout Interval, Namespace, Client Address, and Port Number"
    

    5. 

  5. 

    Set OCI_ATTR_SUBSCR_RECPTPROTO to OCI_SUBSCR_PROTO_OCI, then define the callback routine to be used with the subscription handle.
    

    

    See Also:
    
"Notification Callback in OCI"
    

    6. 

  6. 

    Set OCI_ATTR_SUBSCR_RECPTPROTO to OCI_SUBSCR_PROTO_SERVER, then define the PL/SQL procedure, to be invoked on notification, in the database.
    

    

    See Also:
    
"Notification Procedure"
    

    7. 

  7. 

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

    8. 



Example 9-23 shows an example of setting QOS levels.




Example 9-23 Setting QOS Levels, the Notification Grouping Class, Value, and Type, and the Namespace Specific Context


/* Set QOS levels */
ub4 qosflags = OCI_SUBSCR_QOS_PAYLOAD;
 
/* Set QOS flags in subscription handle */
(void) OCIAttrSet((dvoid *) subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
                 (dvoid *) &qosflags, (ub4) 0,
                 (ub4) OCI_ATTR_SUBSCR_QOSFLAGS, errhp);
 
/* Set notification grouping class */
ub4 ntfn_grouping_class = OCI_SUBSCR_NTFN_GROUPING_CLASS_TIME;
(void) OCIAttrSet((dvoid *) subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
                 (dvoid *) &ntfn_grouping_class, (ub4) 0,
                 (ub4) OCI_ATTR_SUBSCR_NTFN_GROUPING_CLASS, errhp);
 
/* Set notification grouping value of 10 minutes */
ub4 ntfn_grouping_value = 600;
(void) OCIAttrSet((dvoid *) subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
                 (dvoid *) &ntfn_grouping_value, (ub4) 0,
                 (ub4) OCI_ATTR_SUBSCR_NTFN_GROUPING_VALUE, errhp);
 
/* Set notification grouping type */
ub4 ntfn_grouping_type = OCI_SUBSCR_NTFN_GROUPING_TYPE_SUMMARY;
 
/* Set notification grouping type in subscription handle */
(void) OCIAttrSet((dvoid *) subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
                 (dvoid *) &ntfn_grouping_type, (ub4) 0,
                 (ub4) OCI_ATTR_SUBSCR_NTFN_GROUPING_TYPE, errhp);
 
/* Set namespace specific context */
(void) OCIAttrSet((dvoid *) subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
                 (dvoid *) NULL, (ub4) 0,
                 (ub4) OCI_ATTR_SUBSCR_NAMESPACE_CTX, errhp);









Open Registration for Publish-Subscribe


Prerequisites for the open registration for publish-subscribe are as follows:



    

  • 

    The compatibility of the database must be 9.0 or higher.
    

    • 

  • 

    LDAP_REGISTRATION_ENABLED must be set to TRUE. This can be done this way:
    

     ALTER SYSTEM SET LDAP_REGISTRATION_ENABLED=TRUE

    

    The default is FALSE.
    

    • 

  • 

    LDAP_REG_SYNC_INTERVAL must be set to the time interval (in seconds) to refresh registrations from LDAP:
    

     ALTER SYSTEM SET LDAP_REG_SYNC_INTERVAL =  time_interval

    

    The default is 0, which means do not refresh.
    

    • 

  • 

    To force a database refresh of LDAP registration information immediately:
    

    ALTER SYSTEM REFRESH LDAP_REGISTRATION

    • 



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


    

  1. 

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

    2. 

  2. 

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

    3. 

  3. 

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

    4. 

  4. 

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

    5. 








Using OCI to Open Register with LDAP


    

  1. 

    Call OCIEnvCreate() or OCIEnvNlsCreate() with mode set to OCI_EVENTS | OCI_USE_LDAP.
    

    2. 

  2. 

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

      

    • 

      OCI_ATTR_LDAP_HOST: the host name on which the LDAP server resides
      

      • 

    • 

      OCI_ATTR_LDAP_PORT: the port on which the LDAP server is listening
      

      • 

    • 

      OCI_ATTR_BIND_DN: the distinguished name to log in to the LDAP server, usually the DN of the enterprise user
      

      • 

    • 

      OCI_ATTR_LDAP_CRED: the credential used to authenticate the client, for example, the password for simple authentication (user name and password)
      

      • 

    • 

      OCI_ATTR_WALL_LOC: for SSL authentication, the location of the client wallet
      

      • 

    • 

      OCI_ATTR_LDAP_AUTH: the authentication method code
      

      

      See Also:
      
"Environment Handle Attributes" for a complete list of authentication modes
      

      • 

    • 

      OCI_ATTR_LDAP_CTX: the administrative context for Oracle Database in the LDAP server
      

      • 

    

    3. 

  3. 

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

    4. 

  4. 

    Call OCIArrayDescriptorAlloc() with descriptor type OCI_DTYPE_SRVDN, to allocate a server DN descriptor.
    

    5. 

  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. 

  6. 

    Call 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_RECPTRES - presentation to receive notification with
      

      • 

    • 

      OCI_ATTR_SUBSCR_QOSFLAGS - QOS (quality of service) levels
      

      • 

    • 

      OCI_ATTR_SUBSCR_TIMEOUT - Registration timeout interval in seconds. The default is 0 if a timeout is not set.
      

      • 

    • 

      OCI_ATTR_SUBSCR_SERVER_DN - the descriptor handles you populated in Step 5
      

      • 

    

    7. 

  7. 

    The values of QOS, timeout interval, namespace, and port are set. See "Setting QOS, Timeout Interval, Namespace, Client Address, and Port Number".
    

    8. 

  8. 

    Call OCISubscriptionRegister() to register the subscriptions. The registration takes effect when the database accesses LDAP to pick up new registrations. The frequency of pickups is determined by the value of LDAP_REG_SYNC_INTERVAL.
    

    9. 









Setting QOS, Timeout Interval, Namespace, Client Address, and Port Number


You can set QOSFLAGS to the following QOS levels using OCIAttrSet():


    

  • 

    OCI_SUBSCR_QOS_RELIABLE - Reliable notification persists across instance and database restarts. Reliability is of the server only and is only for persistent queues or buffered messages. This option describes the persistence of the notifications. Registrations are persistent by default.
    

    • 

  • 

    OCI_SUBSCR_QOS_PURGE_ON_NTFN - Once notification is received, purge registration on first notification. (Subscription is unregistered.)
    

    • 



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

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

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



The registration is purged when the timeout is exceeded, and a notification is sent to the client, so that the client can invoke its callback and take any necessary action. For client failure before the timeout, the registration is purged.


You can set the port number on the environment handle, which is important if the client is on a system behind a firewall that can receive notifications only on certain ports. Clients can specify the port for the listener thread before the first registration, using an attribute in the environment handle. The thread is started the first time OCISubscriptionRegister() is called. If available, this specified port number is used. An error is returned if the client tries to start another thread on a different port using a different environment handle.


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



If instead, the port is determined automatically, you can get the port number at which the client thread is listening for notification by obtaining the attribute from the environment handle.


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



Example to set client address:


text ipaddr[16] = "10.177.246.40";
(void)(OCIAttrSet((dvoid *) envhp, (ub4) OCI_HTYPE_ENV,
       (dvoid *) ipaddr, (ub4) strlen((const char *)ipaddr),
       (ub4) OCI_ATTR_SUBSCR_IPADDR, errhp));





See Also:

"OCI_ATTR_SUBSCR_IPADDR"







OCI Functions Used to Manage Publish-Subscribe Notification


Table 9-13 lists the functions that are used to manage publish-subscribe notification.




Table 9-13 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 in OCI


The client must 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:

"Subscription Handle Attributes"


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


typedef ub4 (*OCISubscriptionNotify) ( void            *pCtx,
                                       OCISubscription *pSubscrHp,
                                       void            *pPayload,
                                       ub4             iPayloadLen,
                                       void            *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. Currently, 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 (AQ), the descriptor is OCI_DTYPE_AQNFY. For the AQ namespace, the count of notifications received in the group is provided in the notification descriptor. The attributes of pDescriptor are:






    

  • 

    Notification flag (regular = 0, timeout = 1, or grouping notification = 2) - OCI_ATTR_NFY_FLAGS
    

    • 

  • 

    Queue name - OCI_ATTR_QUEUE_NAME
    

    • 

  • 

    Consumer name - OCI_ATTR_CONSUMER_NAME
    

    • 

  • 

    Message ID - OCI_ATTR_NFY_MSGID
    

    • 

  • 

    Message properties - OCI_ATTR_MSG_PROP
    

    • 

  • 

    Count of notifications received in the group - OCI_ATTR_AQ_NTFN_GROUPING_COUNT
    

    • 

  • 

    The group, an OCI collection - OCI_ATTR_AQ_NTFN_GROUPING_MSGID_ARRAY
    

    • 





See Also:







iMode (IN)




Call-specific mode. The only valid value is OCI_DEFAULT. This value executes the default call.






Example 9-24 shows how to use AQ grouping notification attributes in a notification callback.




Example 9-24 Using AQ Grouping Notification Attributes in an OCI Notification Callback


ub4 notifyCB1(void *ctx, OCISubscription *subscrhp, void *pay, ub4 payl,
              void *desc, ub4 mode)
{
 oratext            *subname;
 ub4                 size;
 OCIColl            *msgid_array = (OCIColl *)0;
 ub4                 msgid_cnt = 0;
 OCIRaw             *msgid;
 void              **msgid_ptr;
 sb4                 num_msgid = 0;
 void               *elemind = (void *)0;
 boolean             exist;
 ub2                 flags;
 oratext            *hexit = (oratext *)"0123456789ABCDEF";
 ub4                 i, j;
 
 /* get subscription name */
 OCIAttrGet(subscrhp, OCI_HTYPE_SUBSCRIPTION, (void *)&subname, &size,
            OCI_ATTR_SUBSCR_NAME,ctxptr->errhp);
 
 /* print subscripton name */
 printf("Got notification for %.*s\n", size, subname);
 fflush((FILE *)stdout);
 
 /* get the #ntfns received in this group */
 OCIAttrGet(desc, OCI_DTYPE_AQNFY, (void *)&msgid_cnt, &size,
            OCI_ATTR_AQ_NTFN_GROUPING_COUNT, ctxptr->errhp);
 
 /* get the group - collection of msgids */
 OCIAttrGet(desc, OCI_DTYPE_AQNFY, (void *)&msgid_array, &size,
            OCI_ATTR_AQ_NTFN_GROUPING_MSGID_ARRAY, ctxptr->errhp);
 
 /* get notification flag - regular, timeout, or grouping notification? */
 OCIAttrGet(desc, OCI_DTYPE_AQNFY, (void *)&flags, &size,
            OCI_ATTR_NFY_FLAGS, ctxptr->errhp);
 
 /* print notification flag */
 printf("Flag: %d\n", (int)flags);
 
 /* get group (collection) size */
 if (msgid_array)
   checkerr(ctxptr->errhp,
        OCICollSize(ctxptr->envhp, ctxptr->errhp,
        CONST OCIColl *) msgid_array, &num_msgid),
        "Inside notifyCB1-OCICollSize");
 else
   num_msgid =0;
 
 /* print group size */
 printf("Collection size: %d\n", num_msgid);
 
 /* print all msgids in the group */
 for(i = 0; i < num_msgid; i++)
 {
   ub4  rawSize;                                             /* raw size    */
   ub1 *rawPtr;                                              /* raw pointer */
     /* get msgid from group */
   checkerr(ctxptr->errhp,
        OCICollGetElem(ctxptr->envhp, ctxptr->errhp,
               (OCIColl *) msgid_array, i, &exist,
               (void **)(&msgid_ptr), &elemind),
        "Inside notifyCB1-OCICollGetElem");
   msgid = *msgid_ptr;
   rawSize = OCIRawSize(ctxptr->envhp, msgid);
   rawPtr = OCIRawPtr(ctxptr->envhp, msgid);
 
   /* print msgid size */
   printf("Msgid size: %d\n", rawSize);
 
   /* print msgid in hexadecimal format */
   for (j = 0; j < rawSize; j++)
   {                                           /* for each byte in the raw */
     printf("%c", hexit[(rawPtr[j] & 0xf0) >> 4]);
     printf("%c", hexit[(rawPtr[j] & 0x0f)]);
   }
   printf("\n");
 }
 
 /* print #ntfns received in group */
 printf("Notification Count: %d\n", msgid_cnt);
 printf("\n");
 printf("***********************************************************\n");
 fflush((FILE *)stdout);
 return 1;
}









Notification Procedure


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


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




See Also:











Publish-Subscribe Direct Registration Example


Example 9-25 shows how system events, client notification, and Advanced Queuing work together to implement publish subscription notification.


The PL/SQL code in Example 9-25 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. Connect as pubsub before running Example 9-25.




Example 9-25 Implementing a Publish Subscription Notification


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

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




After the subscriptions are created, the client must register for notification using callback functions. Example 9-26 shows sample code that performs the necessary steps for registration. The initial steps of allocating and initializing session handles are omitted here for clarity.




Example 9-26 Registering for Notification Using Callback Functions


...
static ub4 namespace = OCI_SUBSCR_NAMESPACE_AQ;

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

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

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

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

  if (status == OCI_SUCCESS) return;

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

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

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

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

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

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

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

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

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

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

}


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

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

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

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

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

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

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

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

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

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

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




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








Publish-Subscribe LDAP Registration Example


Example 9-27 shows a code fragment that illustrates how to do LDAP registration. Please read all the program comments.




Example 9-27 LDAP Registration


...

  /* To use the LDAP registration feature, OCI_EVENTS | OCI_EVENTS |OCI_USE_LDAP*/
  /*   must be set in OCIEnvCreate or OCIEnvNlsCreate */
  /*     (Note: OCIInitialize is deprecated): */
  (void) OCIInitialize((ub4) OCI_EVENTS|OCI_OBJECT|OCI_USE_LDAP, (void *)0,
                       (void * (*)(void *, size_t)) 0,
                       (void * (*)(void *, void *, size_t))0,
                       (void (*)(void *, void *)) 0 );

...

  /* set LDAP attributes in the environment handle */

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

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

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

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

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

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

...

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

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

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

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

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

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

  /* client authentication method */
  (void) OCIAttrGet((void *)envhp, OCI_HTYPE_ENV, (void *)&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((void *)envhp, (void **)dn, 
                         (ub4) OCI_DTYPE_SRVDN, (size_t)0, (void **)0);

  /* now *dn is the server DN descriptor, add the DN of the first database 
     that you want to register */
  (void) OCIAttrSet((void *)*dn, (ub4) OCI_DTYPE_SRVDN, 
                    (void *)"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((void *)*dn, (ub4) OCI_DTYPE_SRVDN, 
                    (void *)"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((void *) *subhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
                 (void *) *dn, (ub4)0, (ub4) OCI_ATTR_SERVER_DNS, errhp);

  ...

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

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

  /* allocate an array of char * to hold server DN pointers returned by
     an Oracle database*/
    if (intval)
    {
      arr = (char **)malloc(intval*sizeof(char *));
      (void) OCIAttrGet((void *)*dn, (ub4)OCI_DTYPE_SRVDN, (void *)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. However,
     if mode OCI_REG_LDAPONLY is used, the registration request
     will be directly sent to LDAP. This mode should be used when there is 
     more than one server DN in the server DN descriptor or you are sure
     that a database connection is not available.

     In this example, two DNs are entered, so you should use mode 
     OCI_REG_LDAPONLY in LDAP registration. */
  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);
}
...