8 Managing Scalable Platforms

This chapter contains these topics:

OCI Support for Transactions

OCI has a set of API calls to support operations on both local and global transactions. These calls include object support, so that if an OCI application is running in object mode, the commit and rollback calls will synchronize the object cache with the state of the transaction.

The functions listed later perform transaction operations. Each call takes a service context handle that must be initialized with the proper server context and user session handle. The transaction handle is the third element of the service context; it stores specific information related to a transaction. When a SQL statement is prepared, it is associated with a particular service context. When the statement is executed, its effects (query, fetch, insert) become part of the transaction that is currently associated with the service context.

  • OCITransStart() - marks the start of a transaction

  • OCITransDetach() - detaches a transaction

  • OCITransCommit() - commits a transaction

  • OCITransRollback() - rolls back a transaction

  • OCITransPrepare() - prepares a transaction to be committed in a distributed processing environment

  • OCITransMultiPrepare() - prepares a transaction with multiple branches in a single call.

  • OCITransForget() - causes the server to forget a heuristically completed global transaction.

Depending on the level of transactional complexity in your application, you may need all or only a few of these calls. The following section discusses this in more detail.

Levels of Transactional Complexity

The OCI supports several levels of transaction complexity.

Simple Local Transactions

Many applications work with only simple local transactions. In these applications, an implicit transaction is created when the application makes database changes. The only transaction-specific calls needed by such applications are:

  • OCITransCommit() - to commit the transaction

  • OCITransRollback() - to roll back the transaction

As soon as one transaction has been committed or rolled back, the next modification to the database creates a new implicit transaction for the application.

Only one implicit transaction can be active at any time on a service context. Attributes of the implicit transaction are opaque to the user.

If an application creates multiple sessions, each one can have an implicit transaction associated with it.

See Also:

"OCITransCommit()" for sample code showing the use of simple local transactions. See new flags introduced in Oracle Database 10g Release 2: OCI_TRANS_WRITEBATCH and OCI_TRANS_WRITENOWAIT

Serializable or Read-Only Local Transactions

Applications requiring serializable or read-only transactions require an additional OCI OCITransStart() call to start the transaction.

The OCITransStart() call must specify OCI_TRANS_SERIALIZABLE or OCI_TRANS_READONLY, as appropriate, for the flags parameter. If no flag is specified, the default value is OCI_TRANS_READWRITE for a standard read/write transaction.

Specifying the read-only option in the OCITransStart() call saves the application from performing a server round trip to execute a SET TRANSACTION READ ONLY statement.

Global Transactions

Global transactions are necessary only in more sophisticated transaction-processing applications.

Transaction Identifiers

Three-tier applications such as transaction processing (TP) monitors create and manage global transactions. They supply a global transaction identifier (XID), that a server then associates with a local transaction.

A global transaction has one or more branches. Each branch is identified by an XID. The XID consists of a global transaction identifier (gtrid) and a branch qualifier (bqual). This structure is based on the standard XA specification.

For example, the following is the structure for one possible XID of 1234:

Table 8-1 Global Transaction Identifier

Component Value







See Also:

Oracle Database Heterogeneous Connectivity Administrator's Guide for more information about transaction identifiers

The transaction identifier used by OCI transaction calls is set in the OCI_ATTR_XID attribute of the transaction handle, using OCIAttrSet(). Alternately, the transaction can be identified by a name set in the OCI_ATTR_TRANS_NAME attribute.


When setting this attribute in a transaction handle, the length of the name can be at most 64 bytes. The formatid of the XID is 0 and the branch qualifier is 0.

When retrieving this attribute from a transaction handle, the returned transaction name is the global transaction identifier. The size is the length of the global transaction identifier.

Transaction Branches

Within a single global transaction, Oracle supports both tightly coupled and loosely coupled relationships between a pair of branches.

  • Tightly coupled branches share the same local transaction. In this case, the gtrid references a unique local transaction, and multiple branches point to that same transaction. The owner of the transaction is the branch that was created first.

  • Loosely coupled branches use different local transactions. In this case the gtrid and bqual together map to a unique local transaction. Each branch points to a different transaction.

The flags parameter of OCITransStart() allows applications to pass OCI_TRANS_TIGHT or OCI_TRANS_LOOSE to specify the type of coupling.

A session corresponds to a user session, created with OCISessionBegin().

Figure 8-1 illustrates tightly coupled branches within an application. S1 and S2, are sessions, B1 and B2 are branches, and T is a transaction. The XIDs of the two branches share the same gtrid, because they are operating on the same transaction, but they have a different bqual, because they are on separate branches

Figure 8-1 Multiple Tightly Coupled Branches

Description of Figure 8-1 follows
Description of "Figure 8-1 Multiple Tightly Coupled Branches"

Figure 8-2 illustrates how a single session operates on different branches. The gtrid component of the XIDs are different, because they represent separate global transactions

Figure 8-2 Session Operating on Multiple Branches

Description of Figure 8-2 follows
Description of "Figure 8-2 Session Operating on Multiple Branches"

It is possible for a single session to operate on multiple branches that share the same transaction, but this scenario does not have much practical value.

See Also:

"OCITransStart()" for sample code demonstrating this scenario

Branch States

Transaction branches are classified into two states: active branches and inactive branches.

A branch is active if a server process is executing requests on the branch. A branch is inactive if no server processes are executing requests in the branch. In this case, no session is the parent of the branch, and the branch becomes owned by the PMON process in the server.

Detaching and Resuming Branches

A branch becomes inactive when an OCI application detaches it, using the OCITransDetach() call. The branch can be made active again by resuming it with a call to OCITransStart() with the flags parameter set to OCI_TRANS_RESUME.

When an application detaches a branch with OCITransDetach(), it uses the value specified in the timeout parameter of the OCITransStart() call that created the branch. The timeout specifies the number of seconds the transaction can remain dormant as a child of PMON before being deleted.

When an application wants to resume a branch, it calls OCITransStart(), specifying the XID of the branch as an attribute of the transaction handle, OCI_TRANS_RESUME for the flags parameter, and a different timeout parameter. This timeout value for this call specifies the length of time that the session will wait for the branch to become available if it is currently in use by another process. If no other processes are accessing the branch, it can be resumed immediately. A transaction can be resumed by a different process than the one that detached it, as long as that process has the same authorization as the one that detached the transaction.

Setting Client Database Name

The server handle has OCI_ATTR_EXTERNAL_NAME and OCI_ATTR_INTERNAL_NAME attributes. These attributes set the client database name recorded when performing global transactions. The name can be used by the database administrator to track transactions that may be pending in a prepared state because of failures.


An OCI application sets these attributes, using OCIAttrSet() before logging on and using global transactions.

One-Phase Versus Two-Phase Commit

Global transactions may be committed in one or two phases. The simplest situation is when a single transaction is operating against a single database. In this case, the application can perform a one-phase commit of the transaction by calling OCITransCommit() because the default value of the call is for one-phase commit.

The situation is more complicated if the application is processing transactions against multiple databases or multiple Oracle servers. In this case, a two-phase commit is necessary. A two-phase commit consists of these steps:

  1. Prepare - The application issues OCITransPrepare() call against each transaction. The transactions return a value indicating whether it is able to commit its current work (OCI_SUCCESS) or not (OCI_ERROR).

  2. Commit - If each OCITransPrepare() call returns a value of OCI_SUCCESS, the application can issue a OCITransCommit() call to each transaction. The flags parameter of the commit call must be explicitly set to OCI_TRANS_TWOPHASE for the appropriate behavior, since the default for this call is for a one-phase commit.


    The OCITransPrepare() call can also return OCI_SUCCESS_WITH_INFO if a transaction needs to indicate that it is read-only. This means that a commit is neither appropriate nor necessary.

An additional call, OCITransForget(), indicates that a database "forgets" a completed transaction. This call is for situations in which a problem has occurred that requires that a two-phase commit be aborted. When a server receives a OCITransForget() call, it removes all information about the transaction.

Preparing Multiple Branches in a Single Message

There are times when multiple applications will be using different branches of a global transaction against the same Oracle database. Before such a transaction can be committed, all branches must be prepared.

Most often, the applications using the branches are responsible for preparing their own branches. However, some architectures turn this responsibility over to an external transaction service. This external transaction service must then prepare each branch of the global transaction. Using the traditional OCITransPrepare() call becomes inefficient as each branch must be individually prepared. The number of messages sent from the client to the server can be greatly reduced by using the OCITransMultiPrepare() call, that prepares multiple branches involved in the same global transaction in one round trip.

Transaction Examples

Here is how to use the transaction OCI calls:

The following tables show series of OCI calls and other actions, along with their resulting behavior. For the sake of simplicity, not all parameters to these calls are listed; rather, the flow of calls which is being demonstrated.

The OCI Action column indicates what the OCI application is doing, or what call it is making. The XID column lists the transaction identifier, when necessary. The Flags column lists the values passed in the flags parameter. The Result column describes the result of the call.

Initialization Parameters

Two initialization parameters relate to the use of global transaction branches and migratable open connections:

  • TRANSACTIONS - This parameter specifies the maximum number of global transaction branches in the entire system. In contrast, the maximum number of branches on a single global transaction is 8.

  • OPEN_LINKS_PER_INSTANCE - This parameter specifies the maximum number of migratable open connections. Migratable open connections are used by global transactions to cache connections after committing a transaction.Contrast this with the OPEN_LINKS parameter, that controls the number of connections from a session and is not applicable to applications that use global transactions.

Update Successfully, One-Phase Commit

Here is a list of the steps:

Table 8-2 One-Phase Commit

Step OCI Action XID Flags Result





Starts new read/write transaction





Update rows





Commit succeeds

Start a Transaction, Detach, Resume, Prepare, Two-Phase Commit

Here is a list of the steps:

Table 8-3 Two-Phase Commit

Step OCI Action XID Flags Result





Starts new read-only transaction





Update rows





Transaction is detached





Transaction is resumed










Transaction prepared for two-phase commit





Transaction is committed.

In step 4, the transaction can be resumed by a different process, as long as it had the same authorization.

Read-Only Update Fails

Here is a list of the steps:

Table 8-4 Read-Only Update Fails

Step OCI Action XID Flags Result






Starts new read-only transaction





Update fails, because transaction is read-only





Commit has no effect

Start a Read-Only Transaction, Select and Commit

Here is a list of the steps:

Table 8-5 Read-Only Transaction

Step OCI Action XID Flags Result






Starts new read-only transaction





Query database





No effect — transaction is read-only, no changes made

Password and Session Management

The OCI has the ability to authenticate and maintain multiple users.

OCI Authentication Management

The OCISessionBegin() call authenticates a user against the server set in the service context handle. It must be the first call for any given server handle. OCISessionBegin() authenticates the user for access to the Oracle server specified by the server handle and the service context of the call: after OCIServerAttach() initializes a server handle, OCISessionBegin() must be called to authenticate the user for that server.

When OCISessionBegin() is called for the first time on a server handle, the user session may not be created in migratable mode (OCI_MIGRATE). After OCISessionBegin() has been called for a server handle, the application may call OCISessionBegin() again to initialize another user session handle with different or the same credentials and different or the same operation modes. If an application wants to authenticate a user in OCI_MIGRATE mode, the service handle must already be associated with a non-migratable user handle. The userid of that user handle becomes the ownership ID of the migratable user session. Every migratable session must have a non-migratable parent session.

  • If the OCI_MIGRATE mode is not specified, then the user session context can be used only with the server handle used with the OCISessionBegin().

  • If OCI_MIGRATE mode is specified, then the user authentication can be set with other server handles. However, the user session context may only be used with server handles that resolve to the same database instance. Security checking is performed during session switching.

A migratable session can switch to a different server handle only if the ownership ID of the session matches the userid of a non-migratable session currently connected to that same server.

OCI_SYSDBA, OCI_SYSOPER, and OCI_PRELIM_AUTH settings can only be used with a primary user session context.

A migratable session can be switched, or migrated, to a server handle within an environment represented by an environment handle. It can also migrate or be cloned, to a server handle in another environment in the same process, or in a different process in a different mode. To perform this migration, or cloning, you need to do the following:

  1. Extract the session id from the session handle using OCI_ATTR_MIGSESSION. This is an array of bytes that must not be modified by the caller.

  2. Transport this session id to another process.

  3. In the new environment, create a session handle and set the session id using OCI_ATTR_MIGSESSION.

  4. Execute OCISessionBegin(). The resulting session handle is fully-authenticated.

To provide credentials for a call to OCISessionBegin(), you must provide a valid user name and password pair for database authentication in the user session handle parameter. This involves using OCIAttrSet() to set the OCI_ATTR_USERNAME and OCI_ATTR_PASSWORD attributes on the user session handle. Then OCISessionBegin() is called with OCI_CRED_RDBMS.

When the user session handle is terminated using OCISessionEnd(), the user name and password attributes are changed and thus cannot be re-used in a future call to OCISessionBegin(). They must be reset to new values before the next OCISessionBegin() call.

Or, you can supply external credentials. No attributes need to be set on the user session handle before calling OCISessionBegin(). The credential type is OCI_CRED_EXT. If values have been set for OCI_ATTR_USERNAME and OCI_ATTR_PASSWORD, then these are ignored if OCI_CRED_EXT is used.

OCI Password Management

The OCIPasswordChange() call enables an application to modify a user's database password as necessary. This is particularly useful if a call to OCISessionBegin() returns an error message or warning indicating that a user's password has expired.

Applications can also use OCIPasswordChange() to establish a user authentication context, and to change the password. If OCIPasswordChange() is called with an uninitialized service context, it establishes a service context and authenticates the user's account using the old password, and then changes the password to the new password. If the OCI_AUTH flag is set, it leaves the user session initialized. Otherwise, the user session is cleared.

If the service context passed to OCIPasswordChange() is already initialized, then OCIPasswordChange() authenticates the given account using the old password and changes the password to the new password. In this case, no matter how the flag is set, the user session remains initialized.

Secure External Password Store

As an alternative for large-scale deployments where applications use password credentials to connect to databases, it is possible to store such credentials in a client-side Oracle wallet. An Oracle wallet is a secure software container that is used to store authentication and signing credentials.

Storing database password credentials in a client-side Oracle wallet eliminates the need to embed user names and passwords in application code, batch jobs, or scripts. This reduces the risk of exposing passwords in the clear in scripts and application code, and simplifies maintenance because you need not change your code each time user names and passwords change. In addition, not having to change application code also makes it easier to enforce password management policies for these user accounts.

When you configure a client to use the external password store, applications can use the following syntax to connect to databases that use password authentication:

CONNECT /@database_alias

Note that you need not specify database login credentials in this CONNECT statement. Instead your system looks for database login credentials in the client wallet.

See Also:

Oracle Database Security Guide for information about configuring your client to use secure external password store and for information about managing credentials in it.

OCI Session Management

Transaction servers that actively balance user load by multiplexing user sessions over a few server connections must group these connections into a server group. Oracle uses server groups to identify these connections so that sessions can be managed effectively and securely.

The attribute OCI_ATTR_SERVER_GROUP must be defined to specify the server group name using the OCIAttrSet() call:

OCIAttrSet ((dvoid *) srvhp, (ub4) OCI_HTYPE_SERVER, (dvoid *) group_name, 
            (ub4) strlen ((CONST char *) group_name), 
            (ub4) OCI_ATTR_SERVER_GROUP, errhp);

The server group name is an alphanumeric string not exceeding 30 characters. OCI_ATTR_SERVER_GROUP attribute must be set in the server context prior to creating the first non-migratable session that uses that context. After the session is created successfully and the connection to the server is established, the server group name cannot be changed.

All migratable sessions created on servers within a server group can only migrate to other servers in the same server group. Servers that terminate will get removed from the server group. New servers may be created within an existing server group at any time.

The use of server groups is optional. If no server group is specified, the server is created in a server group called DEFAULT.

The owner of the first non-migratable session created in a nondefault server group becomes the owner of the server group. All subsequent non-migratable sessions for any server in this server group must be created by the owner of the server group.

The server group feature is useful when dedicated servers are used. It has no effect on shared servers. All shared servers effectively belong to the server group DEFAULT.

Middle-Tier Applications in OCI

A middle-tier application receives requests from browser clients and determines database access and whether to generate an HTML page. Applications can have multiple lightweight user sessions within a single database session. These lightweight sessions allow each user to be authenticated, without the overhead of a separate database connection, and preserve the identity of the real user through the middle tier.

As long as the client authenticates itself with the middle tier and the middle tier authenticates itself with the database, and the middle tier is authorized to act on behalf of the client by the administrator, client identities can be maintained all the way into the database without compromising the security of the client.

The design of a secure three-tier architecture is developed around a set of three trust zones.

The first is the client trust zone.Clients connecting to a Web application server are authenticated by the middle tier using any means: password, cryptographic token, or another. This method may be entirely different from the method used to establish the other trust zones.

The second trust zone is the application server. The data server verifies the identity of the application server and trusts it to pass the correct identity of the client.

The third trust zone is the data server interaction with the authorization server to obtain the roles assigned to the client and the application server.

The application server creates a primary session for itself once it connects to a server. It authenticates itself in the normal manner to the database, creating the application server trust zone. The application server identity is now well known and trusted by the data server.

When the application verifies the identity of a client connecting to the application server, it creates the first trust zone. The application server now needs a session handle for the client so that it can service client requests. The middle-tier process allocates a session handle and then sets the following attributes of the client using OCIAttrSet():

  • OCI_ATTR_USERNAME to set the database user name of the client.

  • OCI_ATTR_PROXY_CREDENTIALS to indicate the authenticated application making the proxy request.

If the application server wants to specify a list of roles activated after it connects as the client, it can call OCIAttrSet() with the attribute OCI_ATTR_INITIAL_CLIENT_ROLES and an array of strings that contains the list of roles prior to OCISessionBegin(). Then the role is established and proxy capability verified in one round trip. If the application server is not allowed to act on behalf of the client or if the application server is not allowed to activate the specified roles, the OCISessionBegin() call will fail.

OCI Attributes for Middle-Tier Applications

The following attributes allow you to specify the external name and initial privileges of a client. These credentials are used by applications as alternative means of identifying or authenticating the client.


Use OCI_CRED_PROXY as the value passed in the credt parameter of OCISessionBegin() when an application server starts a session on behalf of a client, rather than OCI_CRED_RDBMS (database user name and password required) or OCI_CRED_EXT (externally provided credentials).


Use this attribute to specify the credentials of the application server in client authentication. You can code the following declarations and OCIAttrSet() call:

OCISession *session_handle;
OCISvcCtx  *application_server_session_handle;
OCIError   *error_handle;
OCIAttrSet((dvoid *)session_handle, (ub4) OCI_HTYPE_SESSION, 
           (dvoid *)application_server_session_handle, (ub4) 0, 
           OCI_ATTR_PROXY_CREDENTIALS, error_handle);


Your applications can use the distinguished name contained within a X.509 certificate as the login name of the client, instead of the database user name.

To pass the distinguished name of the client, the middle-tier server calls OCIAttrSet(), passing OCI_ATTR_DISTINGUISHED_NAME:

/* Declarations */
OCIAttrSet((dvoid *)session_handle, (ub4) OCI_HTYPE_SESSION,
           (dvoid *)distinguished_name, (ub4) 0,
           OCI_ATTR_DISTINGUISHED_NAME, error_handle);


Certificate-based proxy authentication using OCI_ATTR_CERTIFICATE will not be supported in future Oracle Database releases. Use OCI_ATTR_DISTINGUISHED_NAME or OCI_ATTR_USERNAME attribute instead. This method of authentication is similar to the use of distinguished name. The entire X.509 certificate is passed by the middle-tier server to the database.

To pass over the entire certificate, the middle tier calls OCIAttrSet(), passing OCI_ATTR_CERTIFICATE:

OCIAttrSet((dvoid *)session_handle, (ub4) OCI_HTYPE_SESSION, 
           (dvoid *)certificate, ub4 certificate_length, 
           OCI_ATTR_CERTIFICATE, error_handle);


Use the OCI_ATTR_INITIAL_CLIENT_ROLES attribute to specify the roles the client is to possess when the application server connects to the Oracle server. To enable a set of roles, the function OCIAttrSet() is called with the attribute, an array of NULL-terminated strings and the number of strings in the array:

OCIAttrSet((dvoid *)session_handle, (ub4) OCI_HTYPE_SESSION, 
           (dvoid *)role_array, (ub4) number_of_strings,
           OCI_ATTR_INITIAL_CLIENT_ROLES, error_handle);


Many middle tier applications connect to the database as an application, and rely on the middle-tier to track end user identity. To integrate tracking of these end users in various database components, the database client can set the client identifier (a predefined attribute from the application context namespace USERENV) in the session handle at any time. Use the OCI attribute OCI_ATTR_CLIENT_IDENTIFIER in the call to OCIAttrSet(). On the next request to the server, the information is propagated and stored in the server session.

To support the global application context, the client can set the CLIENT_IDENTIFIER (a predefined attribute from the application context namespace USERENV) in the session handle at any time. Use the OCI attribute OCI_ATTR_CLIENT_IDENTIFIER in the call to OCIAttrSet(). On the next request to the server, the information is propagated and stored in the server session.

OCIAttrSet((dvoid *)session_handle, (ub4) OCI_HTYPE_SESSION, 
           (dvoid *)"janedoe", (ub4)strlen("janedoe"),
           OCI_ATTR_CLIENT_IDENTIFIER, error_handle);

When a client has multiple sessions, execute OCIAttrSet() for each session using the same client identifier. OCIAttrSet() must be executed automatically for each process when the session is reconnected in the event that sessions:

  • are migrated across instances

  • span instances using dblinks

  • are pre-connected using TAF PRECONNECT

  • use TAF BASIC

The client identifier is found in V$SESSION as a CLIENT_IDENTIFIER column or through the system context with this SQL statement:

SELECT sys_context('userenv', 'client_identifier') FROM dual;

See Also:

Oracle Database Security Guide the chapter on "Preserving User Identity in Multi-tiered Environments"


A middle-tier can ask the database server to authenticate a client on its behalf by validating the password of the client rather than doing the authentication itself. While it appears that this is the same as a client/server connection, the client does not have to have Oracle software installed on the client's system to be able to perform database operations. To use the password of the client, the application server supplies OCIAttrSet() with the authentication data, using the existing attribute OCI_ATTR_PASSWORD:

OCIAttrSet((dvoid *)session_handle, (ub4) OCI_HTYPE_SESSION, (dvoid *)password,
           (ub4)0, OCI_ATTR_PASSWORD, error_handle);

OCI Middle-Tier Example

Here is a middle-tier example:

*OCIEnv *environment_handle; 
OCIServer *data_server_handle; 
OCIError *error_handle; 
OCISvcCtx *application_server_service_handle; 
OraText *client_roles[2]; 
OCISession *first_client_session_handle, second_client_session_handle; 
** General initialization and allocation of contexts. 
(void) OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0,
                     (dvoid * (*)(dvoid *, size_t)) 0, 
                     (dvoid * (*)(dvoid *, dvoid *, size_t))0, 
                     (void (*)(dvoid *, dvoid *)) 0 ); 
(void) OCIEnvInit( (OCIEnv **) &environment_handle, OCI_DEFAULT, (size_t) 0,
                  (dvoid **) 0 ); 
(void) OCIHandleAlloc( (dvoid *) environment_handle, (dvoid **) &error_handle,
     OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0); 
** Allocate and initialize the server and service contexts used by the
** application server. 

(void) OCIHandleAlloc( (dvoid *) environment_handle, 
     (dvoid **)&data_server_handle, OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0); 
(void) OCIHandleAlloc( (dvoid *) environment_handle, (dvoid **)
     &application_server_service_handle, OCI_HTYPE_SVCCTX, (size_t) 0, 
     (dvoid **) 0);  
(void) OCIAttrSet((dvoid *) application_server_service_handle,
     OCI_HTYPE_SVCCTX, (dvoid *) data_server_handle, (ub4) 0, OCI_ATTR_SERVER,
** Authenticate the application server. In this case, external authentication is
** being used. 

(void) OCIHandleAlloc((dvoid *) environment_handle, 
     (dvoid **)&application_server_session_handle, (ub4) OCI_HTYPE_SESSION,
     (size_t) 0, (dvoid **) 0); 
checkerr(error_handle, OCISessionBegin(application_server_service_handle,
     error_handle, application_server_session_handle, OCI_CRED_EXT,
** Authenticate the first client 
** Note that no password is specified by the 
** application server for the client as it is trusted. 

(void) OCIHandleAlloc((dvoid *) environment_handle, 
     (dvoid **)&first_client_session_handle, (ub4) OCI_HTYPE_SESSION, 
     (size_t) 0,(dvoid **) 0); 
(void) OCIAttrSet((dvoid *) first_client_session_handle, 
     (ub4) OCI_HTYPE_SESSION, (dvoid *) "jeff", (ub4) strlen("jeff"),
     OCI_ATTR_USERNAME, error_handle); 
** In place of specifying a password, pass the session handle of the application
** server instead. 

(void) OCIAttrSet((dvoid *) first_client_session_handle, 
     (ub4) OCI_HTYPE_SESSION, (dvoid *) application_server_session_handle, 
     (ub4) 0, OCI_ATTR_PROXY_CREDENTIALS, error_handle); 
(void) OCIAttrSet((dvoid *) first_client_session_handle, 
     (ub4) OCI_HTYPE_SESSION, (dvoid *) "jeff@VeryBigBank.com", 
     (ub4) strlen("jeff@VeryBigBank.com"), OCI_ATTR_EXTERNAL_NAME,
** Establish the roles that the application server can use as the client. 
client_roles[0] = (OraText *) "TELLER"; 
client_roles[1] = (OraText *) "SUPERVISOR";
(void) OCIAttrSet((dvoid *) first_client_session_handle, 
     OCI_ATTR_INITIAL_CLIENT_ROLES, error_handle); 
checkerr(error_handle, OCISessionBegin(application_server_service_handle,
     error_handle, first_client_session_handle, OCI_CRED_PROXY, OCI_DEFAULT)); 
** To start a session as another client, the application server does the 
** following. It must be 
** noted this code is unchanged from the current way of doing session switching. 

(void) OCIHandleAlloc((dvoid *) environment_handle, 
     (dvoid **)&second_client_session_handle, (ub4) OCI_HTYPE_SESSION, 
     (size_t) 0, (dvoid **) 0); 
(void) OCIAttrSet((dvoid *) second_client_session_handle, 
     (ub4) OCI_HTYPE_SESSION, (dvoid *) "mutt", (ub4) strlen("mutt"),
     OCI_ATTR_USERNAME, error_handle); 
(void) OCIAttrSet((dvoid *) second_client_session_handle, 
     (ub4) OCI_HTYPE_SESSION, (dvoid *) application_server_session_handle, 
     (ub4) 0, OCI_ATTR_PROXY_CREDENTIALS, error_handle); 
(void) OCIAttrSet((dvoid *) second_client_session_handle, 
     (ub4) OCI_HTYPE_SESSION, (dvoid *) "mutt@VeryBigBank.com", 
     (ub4) strlen("mutt@VeryBigBank.com"), OCI_ATTR_EXTERNAL_NAME,
** Note that the application server has not specified any initial roles to have
** as the second client. 
checkerr(error_handle, OCISessionBegin(application_server_service_handle,
     error_handle, second_client_session_handle, OCI_CRED_PROXY, OCI_DEFAULT)); 
** To switch to the first user, the application server applies the session
** handle obtained by the first 
** OCISessionBegin() call. This is the same as is currently done. 

(void) OCIAttrSet((dvoid *)application_server_service_handle, 
     (ub4) OCI_HTYPE_SVCCTX, (dvoid *)first_client_session_handle, 
     (ub4)0, (ub4)OCI_ATTR_SESSION, error_handle); 
** After doing some operations, the application server can switch to
** the second client. That 
** is be done by the following call: 

(void) OCIAttrSet((dvoid *)application_server_service_handle, 
     (ub4) OCI_HTYPE_SVCCTX, 
     (dvoid *)second_client_session_handle, (ub4)0, (ub4)OCI_ATTR_SESSION,
** and then do operations as that client 

End-to-End Application Tracing

Use the following attributes to measure server call time, not including server round trips. These attributes can also be set using the PL/SQL package DBMS_APPLICATION_INFO which incurs one round trip to the server. Using OCI to set the attributes does not incur a round trip.


Set a boolean variable to TRUE or FALSE. Then, after you set this attribute by calling OCIAttrSet(), the server measures each call time. All server times between setting the variable to TRUE and setting it to FALSE are measured.


The elapsed time, in milliseconds, of the last server call is returned in a ub8 variable by calling OCIAttrGet() with this attribute. The following code snippet shows how to do this:

boolean enable_call_time;
ub8 call_time;
enable_call_time = TRUE;
OCIAttrSet(session, OCI_HTYPE_SESSION, (dvoid *)&enable_call_time,
           (ub4)0, OCI_ATTR_COLLECT_CALL_TIME,
           (OCIError *)error_handle);
OCIAttrGet(session, OCI_HTYPE_SESSION, (dvoid *)&call_time,
           (ub4)0, OCI_ATTR_CALL_TIME, 
           (OCIError *)error_handle);

Attributes for End-to-end Application Tracing

Set these attributes for tracing and debugging applications:

  • OCI_ATTR_MODULE - name of the current module in the client application

  • OCI_ATTR_ACTION -name of the current action within the current module. Set to NULL if you do not want to specify an action.

  • OCI_ATTR_CLIENT_INFO - Client application additional information.

Externally Initialized Context in OCI

An externally initialized context is an application context where attributes can be initialized from OCI. Use the SQL statement CREATE CONTEXT to create a context namespace in the server with the option INITIALIZED EXTERNALLY.

Then, you can initialize an OCI interface when establishing a session using OCIAttrSet() and OCISessionBegin(). Issue subsequent commands to write to any attributes inside the namespace only with the PL/SQL package designated in the CREATE CONTEXT statement.

You are able to set default values and other session attributes through the OCISessionBegin() call, thus reducing server round trips.

See Also:

Externally Initialized Context Attributes in OCI

The client applications you develop can set application contexts explicitly in the session handle before authentication, using the following attributes in OCI functions:


Use this to initialize the context array size with the desired number of context attributes in the OCIAttrSet() call.

OCIAttrSet(session, (ub4) OCI_HTYPE_SESSION, 
           (dvoid *)&size, (ub4)0, OCI_ATTR_APPCTX_SIZE, error_handle); 


Use this attribute to get a handle on the application context list descriptor for the session in the OCIAttrGet() call. (The parameter ctxl_desc must be of datatype OCIParam *).

OCIAttrGet(session, (ub4) OCI_HTYPE_SESSION, 
           (dvoid *)&ctxl_desc, (ub4)0, OCI_ATTR_APPCTX_LIST, error_handle);

Use the application context list descriptor to obtain an individual descriptor for the i-th application context in a call to OCIParamGet():

OCIParamGet(ctxl_desc, OCI_DTYPE_PARAM, error_handle,(dvoid **)&ctx_desc, i);

Session Handle Attributes Used to Set an Externally Initialized Context

Set the appropriate values of the application context using these attributes:

  • OCI_ATTR_APPCTX_NAME to set the namespace of the context, which must be a valid SQL identifier.

  • OCI_ATTR_APPCTX_ATTR to set an attribute name in the given context, a non-case sensitive string of up to 30 bytes.

  • OCI_ATTR_APPCTX_VALUE to set the value of an attribute in the given context.

Each namespace can have many attributes, each of which has one value. Here are the calls you can use to set them:

OCIAttrSet(ctx_desc, OCI_DTYPE_PARAM,
     (dvoid *)ctx_name, sizeof(ctx_name), OCI_ATTR_APPCTX_NAME, error_handle);
OCIAttrSet(ctx_desc, OCI_DTYPE_PARAM,
     (dvoid *)attr_name, sizeof(attr_name), OCI_ATTR_APPCTX_ATTR, error_handle);  
OCIAttrSet(ctx_desc, OCI_DTYPE_PARAM,
     (dvoid *)value, sizeof(value), OCI_ATTR_APPCTX_VALUE, error_handle);  

Note that only character type is supported, because application context operations are based on the VARCHAR2 datatype.

Using OCISessionBegin() with an Externally initialized Context

When you call OCISessionBegin(), the context set in the session handle will be pushed to the server. No additional contexts are propagated to the server session. Here is a code example to illustrate use of these calls and attributes:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>

static OraText *username = (OraText *) "HR";
static OraText *password = (OraText *) "HR";

static OCIEnv *envhp;
static OCIError *errhp;

int main(/*_ int argc, char *argv[] _*/);

static sword status;

int main(argc, argv)
int argc;
char *argv[];

  OCISession *authp = (OCISession *) 0;
  OCIServer *srvhp;
  OCISvcCtx *svchp;
  OCIDefine *defnp = (OCIDefine *) 0;
  dvoid     *parmdp;
  ub4        ctxsize;
  OCIParam  *ctxldesc;
  OCIParam  *ctxedesc;

  OCIEnvCreate(&envhp, OCI_DEFAULT, (dvoid *)0, 0, 0, 0,
                            (size_t)0, (dvoid *)0);

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

  /* server contexts */
  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp, OCI_HTYPE_SERVER,
                   (size_t) 0, (dvoid **) 0);

  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, OCI_HTYPE_SVCCTX,
                   (size_t) 0, (dvoid **) 0);

  (void) OCIServerAttach( srvhp, errhp, (OraText *)"", strlen(""), 0);

  /* set attribute server context in the service context */
  (void) OCIAttrSet( (dvoid *) svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp,
                    (ub4) 0, OCI_ATTR_SERVER, (OCIError *) errhp);

  (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **)&authp,
                        (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0);
  /* set app ctx size to 2 because we want to set up 2 application contexts */
  ctxsize = 2;

  /* set up app ctx buffer */
  (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) &ctxsize, (ub4) 0,
                 (ub4) OCI_ATTR_APPCTX_SIZE, errhp);

  /* retrieve the list descriptor */
  (void) OCIAttrGet((dvoid *)authp, (ub4) OCI_HTYPE_SESSION,
                    (dvoid *)&ctxldesc, 0, OCI_ATTR_APPCTX_LIST, errhp );

  /* retrieve the 1st ctx element descriptor */
  (void) OCIParamGet(ctxldesc, OCI_DTYPE_PARAM, errhp, (dvoid**)&ctxedesc, 1);

  (void) OCIAttrSet((dvoid *) ctxedesc, (ub4) OCI_DTYPE_PARAM,
                 (dvoid *) "HR", (ub4) strlen((char *)"HR"),
                 (ub4) OCI_ATTR_APPCTX_NAME, errhp);

  (void) OCIAttrSet((dvoid *) ctxedesc, (ub4) OCI_DTYPE_PARAM,
                 (dvoid *) "ATTR1", (ub4) strlen((char *)"ATTR1"),
                 (ub4) OCI_ATTR_APPCTX_ATTR, errhp);

  (void) OCIAttrSet((dvoid *) ctxedesc, (ub4) OCI_DTYPE_PARAM,
                 (dvoid *) "VALUE1", (ub4) strlen((char *)"VALUE1"),
                 (ub4) OCI_ATTR_APPCTX_VALUE, errhp);

  /* set second context */
  (void) OCIParamGet(ctxldesc, OCI_DTYPE_PARAM, errhp, (dvoid**)&ctxedesc, 2);

  (void) OCIAttrSet((dvoid *) ctxedesc, (ub4) OCI_DTYPE_PARAM,
                 (dvoid *) "HR", (ub4) strlen((char *)"HR"),
                 (ub4) OCI_ATTR_APPCTX_NAME, errhp);

  (void) OCIAttrSet((dvoid *) ctxedesc, (ub4) OCI_DTYPE_PARAM,
                 (dvoid *) "ATTR2", (ub4) strlen((char *)"ATTR2"),
                 (ub4) OCI_ATTR_APPCTX_ATTR, errhp);

  (void) OCIAttrSet((dvoid *) ctxedesc, (ub4) OCI_DTYPE_PARAM,
                 (dvoid *) "VALUE2", (ub4) strlen((char *)"VALUE2"),
                 (ub4) OCI_ATTR_APPCTX_VALUE, errhp);
  (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) username, (ub4) strlen((char *)username),
                 (ub4) OCI_ATTR_USERNAME, errhp);

  (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) password, (ub4) strlen((char *)password),
                 (ub4) OCI_ATTR_PASSWORD, errhp);

  OCISessionBegin ( svchp,  errhp, authp, OCI_CRED_EXT, (ub4) OCI_DEFAULT);


Client Application Context

Application context enables database clients (such as mid-tier applications) to set and send arbitrary session data to the server with each executed statement in only one round trip. The server stores this data in the session context before statement execution, from which it can be used to restrict queries or DML operations. All database features such as views, triggers, VPD policies, or PL/SQL stored procedures can use session data to constrain their operations.

A public writable namespace, nm, is created:

CREATE CONTEXT nm USING hr.package1;

In order to modify the data grouped in that namespace, users need to execute the designated PL/SQL package, hr.package1. However, no privilege is needed to query this information in a user session.

The variable length application context data that is stored in the user session is in the form of an attribute and value pair grouped under the context namespace.

For example, if a human resources application wants to store an end-user's responsibility information in the user session, then it could create an nm namespace and an attribute called 'responsibility' which can be assigned a value such as 'manager' or 'accountant'. This is referred to as the set operation in this document.

If the application decides to clear the value of the 'responsibility' attribute in the nm namespace, then it could set it to NULL or an empty string. This is referred to as the clear operation in this document.

If the application wants to clear all information in the nm namespace, then it could send the namespace information as a part of the clear-all operation to the server. This is referred to as the clear-all operation in a namespace in this document.

If there is no package-security defined for a namespace, then this namespace is deemed to be a client namespace, and any OCI client will be able to transport data for that namespace to the server. No privilege or package security check is done.

Network transport of application context data is done in a single round trip to the server.

Multiple SET Operations

Use OCIAppCtxSet() to perform a series of set operations on 'responsibility' attribute in the CLIENTCONTEXT namespace. When this information is sent to the server, the latest value will prevail for that particular attribute in a namespace. To change the value of the 'responsibility' attribute in the CLIENTCONTEXT namespace from 'manager' to 'vp', use the code snippet shown next, on the client side. When this information is transported to the server, the server will show the latest value 'vp' for the 'responsibility' attribute in the CLIENTCONTEXT namespace.

err = OCIAppCtxSet((void *) sesshndl,(dvoid *)"CLIENTCONTEXT",(ub4) 13,
                   (dvoid *)"responsibility", 14
                   (dvoid *)"manager", 7, errhp, OCI_DEFAULT);
err = OCIAppCtxSet((void *) sesshndl, (dvoid*)"CLIENTCONTEXT", 13,
                   (dvoid *)"responsibility", 14,(dvoid *)"vp",2, errhp,

You can clear specific attribute information in a client namespace. This can be done by setting the value of an attribute to NULL or an empty string, using the OCIAppCtxSet() function:

(void) OCIAppCtxSet((void *) sesshndl, (dvoid *)"CLIENTCONTEXT", 13,
                    (dvoid *)"responsibility", 14, (dvoid *)0, 0,errhp,


(void) OCIAppCtxSet((void *) sesshndl, (dvoid *)"CLIENTCONTEXT", 13
                    (dvoid *)"responsibility", 14, (dvoid *)"", 0,errhp,

CLEAR-ALL Operations Between SET Operations

You can clear all the context information in a specific client namespace, using the OCIAppCtxClearAll() function and it will also be cleared on the server-side user session, during the next network transport.

If the client application performs a clear-all operation in a namespace after a couple of set operations, then values of all attributes in that namespace that were set prior to this clear-all operation are cleaned up on the client side and the server side. Only the subsequent set operations that were done after the clear-all operation, are reflected on the server side. On the client side:

err = OCIAppCtxSet((void *) sesshndl,(dvoid *)"CLIENTCONTEXT", 13,
                   (dvoid *)"responsibility", 14,
                   (dvoid *)"manager", 7,errhp, OCI_DEFAULT);
err = OCIAppCtxClearAll((void *) sesshndl, (dvoid *)"CLIENTCONTEXT", 13, errhp,
err = OCIAppCtxSet((void *) sesshndl, (dvoid*)"CLIENTCONTEXT",13
                   (dvoid *)"office",6, (dvoid *)"2op123", 5, errhp, OCI_DEFAULT);

The clear-all operation will clear up any information set by earlier operations in the namespace CLIENTCONTEXT: 'responsibility' = 'manager' is cleaned up. The information that was set subsequently will be reflected on the server side.

Network Transport and PL/SQL on Client Namespace

It is possible that an application could send application context information on a OCIStmtExecute() call to the server, and also attempt to change the same context information during that call by executing the DBMS_SESSION package.

In general, on the server side, the transported information is processed first and the main call is processed later. This behavior will apply to the application context network transports as well.

If they are both writing to the same client namespace and attribute set, then the main call's information will overwrite the information set by the fast network transport mechanism. Please note that in case of an error in the network transport call, the main call will not be executed.

Note that an error in the main call will not affect the processing of the network transport call. Once the network transport is processed then there is no way to undo it, based on errors in the main call. When the error is reported to the caller (by an OCI function), it is reported as a generic ORA error. Currently, there is no easy way to distinguish an error in the network transport from an error in the main call. The client should not make any assumptions that an error from main call will undo the round trip network processing and implement appropriate exception-handling mechanisms to prevent any inconsistencies.