Oracle Call Interface Programmer's Guide, Volumes 1 & 2 Release 8.0 A58234-01 |
Library | Product | Contents | Index

# 7
OCI Programming Advanced Topics

The following topics are covered in this chapter:

- Overview
- Transactions
- User Authentication and Password Management
- Thread Safety
- Run Time Data Allocation and Piecewise Operations
- LOB and FILE Operations
- OCI Callbacks From External Procedures
- Application Failover Callbacks
- OCI and Advanced Queueing
- Writing Oracle Security Services Applications

  Note: for information on using the OCI to manipulate objects in an Oracle8 server, see Chapter 8, "OCI Object-Relational Programming".

## Overview

Chapter 2 introduced the basic concepts of OCI programming. This chapter is designed to introduce more advanced concepts, including the following:

##### Transactions

Chapter 2 described how a simple transaction can be committed or rolled back. This section talks about different levels of transaction complexity, including global transactions, and the operations that are possible through OCI calls.

##### User Authentication and Password Management

Chapter 2 talked about the OCISessionBegin() call as part of OCI initialization. This section describes additional options available with OCISessionBegin(). It also describes user authentication and password management using the OCIPasswordChange() call.

##### Thread Safety

This section describes OCI support for thread safety and multithreaded application development.

##### Run Time Data Allocation and Piecewise Operations

Inserting, updating, and fetching data in a piecewise fashion is described in this section.

##### LOB and FILE Operations

This section describes OCI functions available for operating on LOBs and FILEs.

##### OCI Callbacks From External Procedures

This section contains a pointer to information about writing external subroutines.

##### Application Failover Callbacks

This section discusses how to write and use application failover callback functions.

##### OCI and Advanced Queueing

This section covers the OCI functions related to Oracle8's Advanced Queueing feature.

##### Writing Oracle Security Services Applications

This section contains a pointer to information on writing Oracle Security Services Applications.

## Transactions

Release 8.0 of the Oracle Call Interface provides 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 below perform transaction operations. Each call takes a service context handle that should 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
- 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.

  See Also: For more specific information about these calls, refer to the function descriptions in Chapter 10.

### Levels of Transactional Complexity

The OCI supports three levels of transaction complexity. Each level is described in one of the following sections.

1. Simple Local Transactions
2. Serializable or Read-Only Local Transactions
3. Global Transactions

#### 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 authorizations, each one can have an implicit transaction associated with it.

For sample code showing the use of simple local transactions, refer to the example on page 13-150.

#### Serializable or Read-Only Local Transactions

Applications requiring serializable or read-only transactions require an additional OCI call beyond those needed by applications operating on simple local transactions. To initiate a serializable or read-only transactions, the application must create the transaction by calling OCITransStart() to start the transaction.

The call to OCITransStart() should 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.

  Note: Users not operating in distributed or global transaction environments may skip this section.

This section provides some background about global transactions, and then gives specific information about using OCI calls to process global transactions.

##### Transaction Identifiers

Three-tiered applications such as transaction processing (TP) monitors create and manage global transactions. They supply a global transaction identifier (XID), which 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:

Component | Value
--- | ---
`gtrid` | `12`
`bqual` | `34`
`gtrid+bqual=XID` | `1234`

  See Also: For more information about transaction identifiers, refer to the Oracle8 Distributed Database Systems manual.

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.

##### Transaction Branches

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

- Tightly coupled branches are different branches that 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 are different branches that 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.

In the Oracle8 OCI, a session corresponds to a user session, created with OCISessionBegin().

The following figure illustrates tightly coupled branches within an application. In the figure, S1 and S2, are sessions, B1 and B2 are branches, and T is a transaction. In this first example, the XIDs of the two branches would share the same gtrid, because they are operating on the same transaction, but they would have a different bqual, because they are separate branches

#### Figure 7-1 Multiple Tightly Coupled Branches

It is also possible for a single session to operate on different branches. In this case, illustrated in the next figure, gtrid component of the XIDs would be different, because they are separate global transactions

#### Figure 7-2 Session Operating on Multiple Branches

For sample code demonstrating this scenario, refer to the example on page 13-158.

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. Sample code demonstrating this scenario can be found in the example on page 13-161.

The following figure illustrates loosely coupled branches:

#### Figure 7-3 Loosely Coupled Branches

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

  Note: 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 associated with it. These attributes set the client database name that will be recorded when performing global transactions. The name can be used by the DBA to track transactions that may be pending in a prepared state due to failures.

  Warning: An OCI application should set 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 a prepare call, OCITransPrepare() against each transaction. The transaction returns a value indicating whether it is able to commit its current work (OCI_SUCCESS) or not (OCI_ERROR).
2. Commit - If each prepare call returns a value of OCI_SUCCESS, the application can issue a commit call, OCITransCommit() to each transaction. The flags parameter of the commit call must be explicitly set to OCI_TRANS_TWOPHASE for the appropriate behavior. The default for this call is for a one-phase commit.

   Note: The prepare call can also return OCI_SUCCESS_WITH_INFO if a transaction needs to indicate that it is read-only, so that a commit is neither appropriate nor necessary.

An additional call, OCITransForget() indicates that a database should forget a heuristically 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 "forgets" all information about the transaction.

  See Also: For more information about two-phase commit, refer to the Oracle8 Distributed Database Systems manual.

### Transaction Examples

This section provides examples of how to use the transaction OCI calls. The following tables provide 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 value(s) passed in the flags parameter. The "Result" column describes the result of the call.

#### Update Successfully, One-Phase Commit

Step | OCI Action | XID | Flags | Result
--- | --- | --- | --- | ---
1 | OCITransStart | 1234 | OCI_TRANS_NEW | Starts new read-write transaction
2 | SQL UPDATE |  |  | Update rows
3 | OCITransCommit |  |  | Commit succeeds

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

Step | OCI Action | XID | Flags | Result
--- | --- | --- | --- | ---
1 | OCITransStart | 1234 | OCI_TRANS_NEW | Starts new read-only transaction
2 | SQL UPDATE |  |  | Update rows
3 | OCITransDetach |  |  | Transaction is detached
4 | OCITransStart | 1234 | OCI_TRANS_RESUME | Transaction is resumed
5 | SQL UPDATE |  |  |
6 | OCITransPrepare |  |  | Transaction prepared for two-phase commit
7 | OCITransCommit |  | OCI_TRANS_TWOPHASE | Transaction is committed.
Note: In step 4, above, the transaction could have been resumed by a different process, as long as it had the same authorization.

#### Read-Only Update Fails

Step | OCI Action | XID | Flags | Result
--- | --- | --- | --- | ---
1 | OCITransStart | 1234 | OCI_TRANS_NEW | OCI_TRANS_READONLY | Starts new read-only transaction
2 | SQL UPDATE |  |  | Update fails, because transaction is read-only
3 | OCITransCommit |  |  | Commit has no effect

#### Start a Read-Only Transaction, Select and Commit

Step | OCI Action | XID | Flags | Result
--- | --- | --- | --- | ---
1 | OCITransStart | 1234 | OCI_TRANS_NEW | OCI_TRANS_READONLY | Starts new read-only transaction
2 | SQL SELECT |  |  | Query database
3 | OCITransCommit |  |  | No effect - transaction is read-only, no changes made

### Related 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, MAX_TRANSACTION_BRANCHES specifies the number of branches on a single global transaction.
- OPEN_LINKS_PER_INSTANCE - This parameter specifies the maximum number of migratable open connections. Migratable open connections are used by global transactions so that connections are cached after a transaction is committed. This is different from the OPEN_LINKS parameter, which is the number of connections from a section (and is not applicable to applications that use global transactions).

## User Authentication and Password Management

Beginning with release 8.0, the OCI provides the ability to authenticate and maintain multiple users in an OCI application. There is also a new OCI call which allows the application to update a user's password. This is particularly helpful if an expired password message is returned by an authentication attempt.

### Authentication

The OCISessionBegin() call is used to authenticate a user against the server set in the service context handle.

For Oracle8, OCISessionBegin() must be called for any given server handle before requests can be made against it. Also, OCISessionBegin() only supports authenticating the user for access to the Oracle server specified by the server handle in the service context. In other words, after OCIServerAttach() is called to initialize a server handle, OCISessionBegin() must be called to authenticate the user for that given server.

When OCISessionBegin() is called for the first time for a given server handle, the user session may not be created in migratable (OCI_MIGRATE) mode.

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 user ID 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 only ever be used with the same server handle set in svchp. If OCI_MIGRATE mode is specified, then the user authentication may be set with different server handles. However, the user session context may only be used with server handles which resolve to the same database instance. Security checking is done during session switching. A process or circuit is allowed to switch to a migratable session only if the ownership ID of the session matches the user ID of a non-migratable session currently connected to that same process or circuit, unless it is the creator of the session.

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

To provide credentials for a call to OCISessionBegin(), one of two methods are supported. The first is to provide a valid username and password pair for database authentication in the user session handle passed to OCISessionBegin(). 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.

  Note: When the user session handle is terminated using OCISessionEnd(), the username and password attributes remain unchanged and thus can be re-used in a future call to OCISessionBegin(). Otherwise, they must be reset to new values before the next OCISessionBegin() call.

The second type of credentials supported are external credentials. No attributes need to be set on the user session handle before calling OCISessionBegin(). The credential type is OCI_CRED_EXT. This is equivalent to the Oracle7 `connect /' syntax. If values have been set for OCI_ATTR_USERNAME and OCI_ATTR_PASSWORD, then these are ignored if OCI_CRED_EXT is used.

### Password Management

The release 8.0 OCI provides the OCIPasswordChange() to allow an OCI 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, as well as to change password, if appropriate flags are set. 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.

## Thread Safety

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

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

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

### Advantages of OCI Thread Safety

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

- Multiple threads of execution can make OCI calls with the same result as successive calls made by a single thread.
- When multiple threads make OCI calls, there are no side effects between threads.
- Users who do not write multithreaded programs do not pay a performance penalty for using thread-safe OCI calls.
- Use of multiple threads can improve program performance. Gains may be seen on multiprocessor systems where threads run concurrently on separate processors, and on single processor systems where overlap can occur between slower operations and faster operations.

### Thread Safety and Three-Tier Architectures

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

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

### Basic Concepts of Multi-threaded Development

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

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

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

### Implementing Thread Safety with OCI 8.0

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

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

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

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

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

1. If an application has multiple environment handles, but each only has one thread (one session exists per environment handle), no mutexing is required.
2. If an application (running in OCI_THREADED mode) maintains multiple environment handles, each of which has one connection which can spawn multiple threads, the programmer has the following options:
   - Pass a value of OCI_NO_MUTEX for the mode of OCIEnvInit(). In this case the application must mutex OCI calls made on the same environment handle by itself. This has the advantage that the mutexing scheme can be optimized based on the application design. The programmer must also insure that only one OCI call is in process on the environment handle connection at any given time.
   - Pass a value of OCI_DEFAULT to OCIEnvInit(). In this case, the OCI library automatically gets a mutex on every OCI call on the environment handle.
3. If an application (running in OCI_THREADED mode) maintains one or more environment handles, each of which has multiple connections, it also has the following options:
   - Pass a value of OCI_NO_MUTEX for the mode of OCIEnvInit(). In this case the application must mutex OCI calls by made on the same environment handle itself. This has the advantage that the mutexing scheme can be optimized based on the application design. The programmer must also insure that only one OCI call is in process on the environment handle connection at any given time.
   - Pass a value of OCI_DEFAULT to OCIEnvInit(). In this case, the OCI library automatically gets a mutex on every OCI call on the same environment handle.

   In this case, however, the programmer should be aware that if the application has two calls on the same environment handle, and one call operating on the server is mutexed, application performance can degrade if the mutexed call is long-running, thus tying up the server connection.

#### Mixing 7.x and 8.0 OCI calls

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

## Run Time Data Allocation and Piecewise Operations

You can use the OCI to perform piecewise inserts and updates, and fetches of data. You can also use the OCI to provide data dynamically in the case of array inserts or updates, instead of providing a static array of bind values. You can insert or retrieve a very large column as a series of chunks of smaller size, minimizing client-side memory requirements.

The size of individual pieces is determined at run time by the application. Each piece may be of the same size as other pieces, or it may be of a different size.

The OCI's piecewise functionality can be particularly useful when you are performing operations on extremely large blocks of string or binary data (for example, operations involving database columns that store LOB, LONG or LONG RAW data). See the section "Valid Datatypes for Piecewise Operations" on page 7-17 for information about which datatypes are valid for piecewise operations.

Figure 2 - 8 shows a single long column being inserted piecewise into a database table through a series of insert operations (i1, i2, i3...in). In this example the inserted pieces are of varying sizes.

#### Figure 7-4 Piecewise Insert of a LONG Column

You can perform piecewise operations in two ways:

- Use calls provided in the OCI library to execute piecewise operations under a polling paradigm, as in release 7.3.
- Employ user-defined callback functions to provide the necessary information and data blocks.

When you set the mode parameter of an OCIBindByPos() or OCIBindByName() call to OCI_DATA_AT_EXEC, this indicates that an OCI application will be providing data for an INSERT or UPDATE dynamically at run time.

Similarly, when you set the mode parameter of an OCIDefineByPos() call to OCI_DYNAMIC_FETCH, this indicates that an application will dynamically provide allocation space for receiving data at the time of the fetch.

In each case, you can provide the run-time information for the INSERT, UPDATE, or FETCH in one of two ways: through callback functions, or by using piecewise operations. If callbacks are desired, an additional bind or define call is necessary to register the callbacks.

The following sections give specific information about run-time data allocation and piecewise operations for inserts, updates, and fetches.

  Note: In addition to SQL statements, piecewise operations are also valid for PL/SQL blocks.

#### Valid Datatypes for Piecewise Operations

Only some datatypes can be manipulated in pieces. OCI applications can perform piecewise fetches, inserts, or updates of the following data types:

- VARCHAR2
- STRING
- LONG
- LONG RAW

Some LOB/FILE operations also provide piecewise semantics for reading or writing data. See the descriptions of OCILobWrite() on page 13-112 and OCILobRead() on page 13-107 for more information about these operations.

Another way of using this feature for all datatypes is to provide data dynamically for array inserts or updates. Note, however, that the callbacks should always specify OCI_ONE_PIECE for the piecep parameter of the callback for datatypes that do not support piecewise operations.

### Providing INSERT or UPDATE Data at Run Time

When you specify the OCI_DATA_AT_EXEC mode in a call to OCIBindByPos() or OCIBindByName(), the value_sz parameter defines the total size of the data that can be provided at run time. The application must be ready to provide to the OCI library the run-time IN data buffers on demand as many times as is necessary to complete the operation. When the allocated buffers are not required any more, they should be freed by the client.

Run-time data is provided in one of the two ways:

- You can define a callback using the OCIBindDynamic() function which when called at run time returns a piece or the whole data.
- If no callbacks are defined, the call to OCIStmtExecute() to process the SQL statement returns the OCI_NEED_DATA error code. The client application then provides the IN/OUT data buffer or piece using the OCIStmtSetPieceInfo() call. OCIStmtGetPieceInfo() provides information about which bind and which piece are being used.

#### Performing a Piecewise Insert

Once the OCI environment has been initialized, and a database connection and session have been established, a piecewise insert begins with calls to prepare a SQL or PL/SQL statement and to bind input values. Piecewise operations using standard OCI calls, rather than user-defined callbacks, do not require a call to OCIBindDynamic().

  Note: Additional bind variables in the statement that are not part of piecewise operations may require additional bind calls, depending on their datatypes.

Following the statement preparation and bind, the application performs a series of calls to OCIStmtExecute(), OCIStmtGetPieceInfo() and OCIStmtSetPieceInfo() to complete the piecewise operation. Each call to OCIStmtExecute() returns a value that determines what action should be performed next. In general, the application retrieves a value indicating that the next piece needs to be inserted, populates a buffer with that piece, and then executes an insert. When the last piece has been inserted, the operation is complete.

Keep in mind that the insert buffer can be of arbitrary size and is provided at run time. In addition, each inserted piece does not need to be of the same size. The size of each piece to be inserted is established by each OCIStmtSetPieceInfo() call.

  Note: If the same piece size is used for all inserts, and the size of the data being inserted is not evenly divisible by the piece size, the final inserted piece will be smaller than the pieces that preceded it. For example, if a data value 10,050,036 bytes long is inserted in chunks of 500 bytes each, the last remaining piece will be only 36 bytes. The programmer must account for this by indicating the smaller size in the final OCIStmtSetPieceInfo() call.

The following steps outline the procedure involved in performing a piecewise insert. The procedure is illustrated in on the following page.

  Step 1. Initialize the OCI environment, allocate the necessary handles, connect to a server, authorize a user, and prepare a statement request. These steps are described in the section "OCI Programming Steps" on page 2-16.

  Step 2. Bind a placeholder using OCIBindByName() or OCIBindByPos(). At this point you do not need to specify the actual size of the pieces you will use, but you must provide the total size of the data that can be provided at run time.

    7.x Upgrade Note: The context pointer that was formerly part of the obindps() and ogetpi() routines does not exist in release 8.0. Clients wishing to provide their own context can use the callback method.

  Step 3. Call OCIStmtExecute() for the first time. At this point no data is actually inserted, and the OCI_NEED_DATA error code is returned to the application.

  If any other value is returned, it indicates that an error occurred.

  Step 4. Call OCIStmtGetPieceInfo() to retrieve information about the piece that needs to be inserted. The parameters of OCIStmtGetPieceInfo() include a pointer that returns a value indicating whether the required piece is the first piece (OCI_FIRST_PIECE) or a subsequent piece (OCI_NEXT_PIECE).

  Step 5. The application populates a buffer with the piece of data to be inserted and calls OCIStmtSetPieceInfo(). The parameters passed to OCIStmtSetPieceInfo() include a pointer to the piece, a pointer to the length of the piece, and a value indicating whether this is the first piece (OCI_FIRST_PIECE), an intermediate piece (OCI_NEXT_PIECE) or the last piece (OCI_LAST_PIECE).

  Step 6. Call OCIStmtExecute() again. If OCI_LAST_PIECE was indicated in Step 5 and OCIStmtExecute() returns OCI_SUCCESS, all pieces were inserted successfully. If OCIStmtExecute() returns OCI_NEED_DATA, go back to Step 3 for the next insert. If OCIStmtExecute() returns any other value, an error occurred.

The piecewise operation is complete when the final piece has been successfully inserted. This is indicated by the OCI_SUCCESS return value from the final OCIStmtExecute() call.

#### Figure 7-5 Steps for Performing Piecewise Insert

Piecewise updates are performed in a similar manner. In a piecewise update operation the insert buffer is populated with the data that is being updated, and OCIStmtExecute() is called to execute the update.

  Note: For additional important information about piecewise operations, see the section "Additional Information About Piecewise Operations with No Callbacks" on page 7-23.

### Piecewise Operations With PL/SQL

An OCI application can perform piecewise operations with PL/SQL for IN, OUT, and IN/OUT bind variables in a method similar to that outlined above. Keep in mind that all placeholders in PL/SQL statements are bound, rather than defined. The call to OCIBindDynamic() specifies the appropriate callbacks for OUT or IN/OUT parameters.

### Providing FETCH Information at Run Time

When a call is made to OCIDefineByPos() with the mode parameter set to OCI_DYNAMIC_FETCH, an application can specify information about the data buffer at the time of fetch. The user also may need to call OCIDefineDynamic() to set up the callback function that will be invoked to get information about the user's data buffer.

Run-time data is provided in one of the two ways:

- You can define a callback using the OCIDefineDynamic() call. The value_sz parameter defines the maximum size of the data that will be provided at run time. When the client library needs a buffer to return the fetched data, the callback will be invoked to provide a run-time buffer into which a piece or the whole data will be returned.
- If no callbacks are defined, the OCI_NEED_DATA error code is returned and the OUT data buffer or piece can then be provided by the client application using OCIStmtSetPieceInfo() call. The OCIStmtGetPieceInfo() call provides Information about which define and which piece are involved.

  See Also: For information about which datatypes are valid for piecewise operations, refer to the section "Valid Datatypes for Piecewise Operations" on page 7-17.

#### Performing a Piecewise Fetch

Once the OCI environment has been initialized, and a database connection and session have been established, a piecewise fetch begins with calls to prepare a SQL or PL/SQL statement and to define output variables. Piecewise operations using standard OCI calls, rather than user-defined callbacks, do not require a call to OCIDefineDynamic().

Following the statement preparation and define, the application performs a series of calls to OCIStmtFetch(), OCIStmtGetPieceInfo(), and OCIStmtSetPieceInfo() to complete the piecewise operation. Each call to OCIStmtFetch() returns a value that determines what action should be performed next. In general, the application retrieves a value indicating that the next piece needs to be fetched, and then fetches that piece into a buffer. When the last piece has been fetched, the operation is complete.

Keep in mind that the fetch buffer can be of arbitrary size. In addition, each fetched piece does not need to be of the same size. The only requirement is that the size of the final fetch must be exactly the size of the last remaining piece. The size of each piece to be fetched is established by each OCIStmtSetPieceInfo() call.

The following steps outline the method for fetching a row piecewise.

  Step 1. Initialize the OCI environment, allocate necessary handles, connect to a database, authorize a user, prepare a statement, and execute the statement. These steps are described on page 2-16.

  Step 2. Define an output variable using OCIDefineByPos(), with mode set to OCI_DYNAMIC_FETCH. At this point you do not need to specify the actual size of the pieces you will use, but you must provide the total size of the data that will be fetched at run time.

    7.x Upgrade Note: The context pointer that was part of the odefinps() and ogetpi() routines does not exist in release 8.0. Clients wishing to provide their own context can use the callback method.

  Step 3. Call OCIStmtFetch() for the first time. At this point no data is actually retrieved, and the OCI_NEED_DATA error code is returned to the application.

  If any other value is returned, an error occurred.

  Step 4. Call OCIStmtGetPieceInfo() to obtain information about the piece to be fetched. The piecep parameter indicates whether it is the first piece (OCI_FIRST_PIECE), a subsequent piece (OCI_NEXT_PIECE), or the last piece (OCI_LAST_PIECE).

  Step 5. Call OCIStmtSetPieceInfo() to specify the buffer into which you wish to fetch the piece.

  Step 6. Call OCIStmtFetch() again to retrieve the actual piece. If OCIStmtFetch() returns OCI_SUCCESS, all the pieces have been fetched successfully. If OCIStmtFetch() returns OCI_NEED_DATA, return to Step 4 to process the next piece. If any other value is returned, an error occurred.

The piecewise fetch is complete when the final OCIStmtFetch() call returns a value of OCI_SUCCESS.

#### Figure 7-6 Steps for Performing Piecewise Fetch

### Additional Information About Piecewise Operations with No Callbacks

In both the piecewise fetch and insert, it is important to understand the sequence of calls necessary for the operation to complete successfully. In particular, keep in mind that for a piecewise insert you must call OCIStmtExecute() one time more than the number of pieces to be inserted (if callbacks are not used). This is because the first time OCIStmtExecute() is called, it merely returns a value indicating that the first piece to be inserted is required. As a result, if you are inserting n pieces, you must call OCIStmtExecute() a total of n+1 times.

Similarly, when performing a piecewise fetch, you must call OCIStmtFetch() once more than the number of pieces to be fetched.

Users who are binding to PL/SQL tables can retrieve a pointer to the current index of the table during the OCIStmtGetPieceInfo() calls.

## LOB and FILE Operations

The Oracle8 OCI includes a set of functions for performing operations on large objects (LOBs) in a database. Internal LOBs (BLOBs, CLOBs, NCLOBs) are stored in the database tablespaces in a way that optimizes space and provides efficient access. These LOBs have the full transactional support of the database server. External LOBs (FILEs) are large data objects stored in the server's operating system files outside the database tablespaces.

The maximum length of a LOB/FILE is 4 gigabytes.

FILE functionality is read-only. Oracle8 currently supports only binary files (BFILEs).

  See Also: For code samples showing the use of LOB operations, refer to "Example 5, CLOB/BLOB Operations" on page D-76, and "Example 6, LOB Buffering" on page D-96.

  Customers who are interested in using the dbms_lob package to work with LOBs should refer to the Oracle8 Application Developer's Guide

### LOBs and LOB Locators

A database table stores a LOB locator which points to the LOB data. When an OCI application issues a SQL query that includes a LOB column in its select-list, fetching the result(s) of the query returns the locator, rather than the actual LOB value. In the OCI, the LOB locator maps to the datatype OCILobLocator.

  Note: The LOB value can be stored inline in a database table if it is less than approximately 4,000 bytes.

Internal LOBs have copy semantics. Thus, if a LOB in one row is copied to a LOB in another row, the actual LOB value is copied, and a new LOB locator is created for the copied LOB.

The OCI functions for LOBs take LOB locators as their arguments. The OCI functions assume that the LOB to which the locator points has already been created, whether or not the LOB contains some value.

An application first fetches the locator using SQL, and then performs further operations using the locator. The OCI functions never take the actual LOB value as a parameter. It is good practice to use a locator in a LOB modification call if and only if its snapshot is recent enough that it sees the current value of the LOB data, since it is the current value that gets modified.

You allocate memory for an internal LOB locator with a call to OCIDescriptorAlloc() by passing OCI_DTYPE_LOB as the descriptor type. To allocate memory for an external LOB (FILE) locator, pass OCI_DTYPE_FILE.

Once you have allocated the LOB locator memory, you must initialize it before passing it to any OCI LOB routines. You can accomplish this by any of the following methods:

1. SELECTing the LOB from the database (which contains a valid LOB locator) into the LOB locator you have just allocated.
2. Using the locator in the RETURNING clause of a SQL INSERT or UPDATE statement.
3. Assigning a different, already initialized LOB locator to the newly allocated LOB locator.

You can also initialize a LOB locator to empty by calling OCIAttrSet() on the locator's OCI_ATTR_LOBEMPTY attribute. A locator initialized in this way may only be used to create an empty LOB in the database. Thus, it can only be used in the VALUES clause of a SQL INSERT statement, or as the source of the SET clause of a SQL UPDATE statement.

  Warning: Locators for LOB and FILE operations are not interchangeable. Locators for LOB operations must be allocated as type OCI_DTYPE_LOB, and locators for FILE operations must be allocated as type OCI_DTYPE_FILE. An internal LOB locator may not be assigned to an external LOB (FILE) locator, and vice versa.

  See Also: For more information about locators, including the LOB locator, see the section "Descriptors and Locators" on page 2-12.

  For sample code showing the use of OCI LOB calls, refer to Example 3 in Appendix B, and the description of OCILobWrite() on page 13-112.

  For more information about LOBs, locators, and read-consistent LOBs, see the Oracle8 Application Developer's Guide.

### FILEs

A FILE locator may be considered to be a pointer to a file on the server's file system. Oracle does not provide any transactional semantics on FILEs, and Oracle8 currently supports only read-only operations on binary FILEs (BFILEs).

Since operations on both internal LOBs and FILEs are similar, all OCI LOB/FILE functions expect a LOB locator as an input to all operations. The only difference is in the way the FILE locator is allocated. When allocating a locator for FILEs, you must pass OCI_DTYPE_FILE as the descriptor type in the OCIDescriptorAlloc() call.

  Warning: Locators for LOB and FILE operations are not interchangeable. Locators for LOB operations must be allocated as type OCI_DTYPE_LOB, and locators for FILE operations must be allocated as type OCI_DTYPE_FILE. An internal LOB locator may not be assigned to an external LOB (FILE) locator, and vice versa.

  See Also: For information about associating a BFILE with an OS file, see the section "Associating a FILE in a Table with an OS File" on page 7-27.

### Creating and Modifying Internal LOBs

You create a new internal LOB by initializing a new LOB locator using OCIDescriptorAlloc(), calling OCIAttrSet() to set it to empty (using the OCI_ATTR_LOBEMPTY attribute), and then binding the locator to a placeholder in an INSERT statement. Doing so inserts the empty locator into a table with a LOB column or attribute. You can then SELECT...FOR UPDATE this row to get the locator, and then write to it using one of the OCI LOB functions.

  Note: Whenever you want to modify a LOB column or attribute (write, copy, trim, and so forth), you must lock the row containing the LOB. One way to do this is to use a SELECT...FOR UPDATE statement to select the locator before performing the operation.

For any LOB write command to be successful, a transaction must be open. This means that if you commit a transaction before writing the data, then you must relock the row (by reissuing the SELECT...FOR UPDATE, for example), because the commit closes the transaction.

  Note: LOB reads and writes are not allowed from within a trigger.

  See Also: For information about binding LOB locators to placeholders, and using them in INSERT statements, refer to the section "Binding LOBs" on page 5-10.

### Associating a FILE in a Table with an OS File

The BFILENAME() function can be used in an INSERT statement to associate an external server-side (OS) file with a BFILE column/attribute in a table. Using BFILENAME() in an UPDATE statement associates the BFILE column or attribute with a different OS file.

  See Also: For more information about the BFILENAME() function, please refer to the Oracle8 Application Developer's Guide.

### Writing to a LOB Attribute of an Object

It is possible to use the OCI to create a new persistent object with a LOB attribute and write to that LOB attribute. The application would follow these steps:

1. Call OCIObjectNew() to create a persistent object with a LOB attribute.
2. Mark the object as dirty.
3. Flush the object, thereby inserting a row into the table
4. Repin the latest version of the object (or refresh the object), thereby retrieving the object from the database and acquiring a valid locator for the LOB
5. Call OCILobWrite() using the LOB locator in the object to write the data.

For more information about object operations, such as marking, flushing, and refreshing, refer to Chapter 8, "OCI Object-Relational Programming".

### Transient Objects with LOB Attributes

An application can call OCIObjectNew() and create a transient object with an internal LOB (BLOB, CLOB, NCLOB) attribute. However, the user cannot perform any operations (e.g., read or write) on the LOB attribute because transient LOBs are not currently supported. Calling OCIObjectNew() to create a transient internal LOB type will not fail, but the application cannot use any LOB operations with the transient LOB.

An application can, however, create a transient object with a FILE attribute and use the FILE attribute to read data from the file stored in the server's file system. The application can also call OCIObjectNew() to create a transient FILE and use that FILE to read from the server's file.

### LOB Buffering

The Oracle8 OCI provides several calls for controlling LOB buffering for small reads and writes of internal LOB values:

- OCILobEnableBuffering()
- OCILobDisableBuffering()
- OCILobFlushBuffer()

These functions provide performance improvements by allowing applications using internal LOBs (BLOB, CLOB, NCLOB) to buffer small reads and writes of LOBs in client-side buffers. This reduces the number of network roundtrips and LOB versions, thereby improving LOB performance significantly for small reads and writes.

  See Also: For more information on LOB buffering, refer to the chapter on LOBs in the Oracle8 Application Developer's Guide, and the LOB buffering code example in Appendix D of this guide.

  For a code sample showing the use of LOB buffering, refer to "Example 6, LOB Buffering" on page D-96.

### LOB/FILE Functions

The functions in Table 7-1 are available to operate on LOBs and FILEs. More detailed information about each function is found in Chapter 13.

These LOB/FILE calls are not valid when an application is connected to an Oracle7 Server.

  Note: In all LOB operations that involve offsets into the data, the offset begins at 1. BLOB and BFILE offsets and amounts are in terms of bytes. CLOB and NCLOB offsets and amounts are in terms of characters.

  See Also: For more information about FILEs, refer to the description of BFILEs in the Oracle8 Application Developer's Guide.

  ##### Table 7-1 OCI LOB and FILE Functions

  Function | Restrictions | Purpose
  --- | --- | ---
  OCILobAppend() | Internal LOBs only | This function appends data from one internal LOB onto another internal LOB. The source and the destination LOBs must already exist. The destination LOB is extended to accommodate the newly written data if it extends beyond the current length of the destination LOB. It is an error to extend the destination LOB beyond the maximum length allowed (4 gigabytes) or to try to append from a NULL LOB.
  OCILobAssign() |  | Assigns one LOB/FILE locator to another.
  OCILobCharSetForm() |  | Gets the character set form of a CLOB/NCLOB.
  OCILobCharSetId() |  | Gets the character set ID of a CLOB/NCLOB.
  OCILobCopy() | Internal LOBs only | This function copies a portion of an internal LOB into another internal LOB. The source and destination LOBs must already exist. If data already exists at the destination's start position, it is overwritten with the source data. If the destination's start position is beyond the end of the current value, zero-byte fillers (BLOBs) or spaces (CLOBs/NCLOBs) are placed in the LOB from the end of the destination value to the beginning of the newly written data from the source. The destination LOB is extended to accommodate the newly written data if it extends beyond the current length of the destination LOB. It is an error to extend the destination LOB beyond the maximum length allowed (4 gigabytes). LOB copy operations must be performed on LOBS of the same type; i.e., one CLOB can be copied to another CLOB, and one BLOB can be copied to another BLOB, but a CLOB cannot be copied to a BLOB, and vice versa.
  OCILobDisableBuffering() | Internal LOBs only | Disables LOB buffering for a given internal locator.
  OCILobEnableBuffering() | Internal LOBs only | Enables LOB buffering for a given internal locator.
  OCILobErase() | Internal LOBs only | Erases a specified portion of the internal LOB value starting at a specified offset. The actual number of characters/bytes erased is returned. The actual number of characters/bytes and the requested number of characters/bytes will differ if the end of the LOB data is reached before erasing the requested number of characters/bytes. If the LOB is NULL, this routine shows that 0 characters/bytes were erased.
  OCILobFileClose(), OCILobFileCloseAll() |  | Closes a previously opened FILE, or all open FILEs. It is an error if this function is called for an internal LOB. No error is returned if the FILE exists but is not opened.
  OCILobFileExists() |  | Tests to see if a FILE exists on the server.
  OCILobFileGetName() |  | Gets the name and the directory alias of a FILE.
  OCILobFileIsOpen() |  | Tests to see if a FILE has been opened with the input locator.
  OCILobFileOpen() |  | Opens a FILE. The FILE can be opened for read-only access. It is an error if this call is made on an internal LOB.
  OCILobFileSetName() |  | Sets the name and the directory alias of a FILE.
  OCILobFlushBuffer() | Internal LOBs only | Flushes the LOB buffer.
  OCILobGetLength() |  | This function gets the length of a LOB/FILE. If the LOB/FILE is NULL, the length is undefined. Empty internal LOBs have a length of zero.
  OCILobIsEqual() |  | Tests to see if two LOB/FILE locators are equal. Two locators are equal if and only if they both refer to the same LOB/FILE value.
  OCILobLoadFromFile() |  | Populates all or part of a LOB with data from a FILE.
  OCILobLocatorIsInit() |  | Tests to see if a LOB/FILE locator is initialized.
  OCILobRead() |  | This function reads a portion of the LOB/FILE value into a buffer. It is an error to try to read from a NULL LOB/FILE.
  OCILobTrim() | Internal LOBs only | This function truncates a LOB, trimming the LOB value to a specified smaller length.
  OCILobWrite() | Internal LOBs only | This function writes data from a buffer into an internal LOB. If data already exists in the LOB, it is overwritten with the data stored in the buffer.

### Server Roundtrips for LOB Functions

For a table showing the number of server roundtrips required for individual OCI LOB functions, refer to Appendix E, "OCI Function Server Roundtrips".

### LOB Read/Write Callbacks

The OCI LOB read and write functions provide the ability to define callback functions which can be used to provide data to be written or handle data that was read. This allows the client application to perform optional processing on the data. One example usage of this would be to use the callbacks to implement a compression algorithm for writing the data and a decompression algorithm for reading it.

  Note: The LOB read/write streaming callbacks provides a fast method for using reading/writing large amounts of LOB data.

The following sections describe the use of callbacks in more detail.

### The Callback Interface for Streaming

Your application can use user-defined read and write callback functions to insert data into or retrieve data from a LOB. This provides an alternative to the polling method for streaming data into a LOB and retrieving data from a LOB. The user-defined callbacks have a specific prototype which is described below. These functions are implemented by the user and registered with OCI through the OCILobRead() and OCILobWrite() calls. The callback functions are called by OCI whenever required.

### Reading LOBs using Callbacks

The user-defined read callback function is registered through the OCILobRead() function. The callback function should have the following prototype:

    <CallbackFunctionName> ( dvoid *ctxp, CONST dvoid *bufp, ub4 len, ub1 piece)

The first parameter, ctxp, is the context of the callback that is passed to OCI in the OCILobRead() function call. When the callback function is called, the information provided by the user in ctxp is passed back to the user (the OCI does not use this information on the way IN). The bufp parameter is the pointer to the storage where the LOB data is returned and bufl is the length of this buffer. It tells the user how much data has been read into the buffer provided by the user.

If the buffer length provided by the user in the original OCILobRead() call is insufficient to store all the data returned by the server, then the user-defined callback is called. In this case the piece parameter indicates to the user whether the information returned in the buffer in the first, next or last piece.

The following is a code fragment of a typical way to implement read callback functions.

Assume here that lobl is a valid locator that has been previously selected, svchp is a valid service handle and errhp is a valid error handle.

    ...
    ub4   offset = 1;
    ub4   loblen = 0;
    ub1   bufp[MAXBUFLEN];
    ub4   amtp = 0;

    sword retval;

    amtp = 4294967295;             /* 4 gigabytes */

    if (retval = OCILobRead(svchp, errhp, lobl, &amtp, offset, (dvoid *) bufp,
            (ub4) MAXBUFLEN, (dvoid *) bufp, cbk_read_lob,
            (ub2) 0, (ub1) SQLCS_IMPLICIT))
        {
          (void) printf("ERROR: OCILobRead() LOB.\n");
          report_error();
        }
    ...
    sb4 cbk_read_lob(ctxp, bufxp, lenp, piece)
    dvoid *ctxp;
    CONST dvoid *bufxp;
    ub4 lenp;
    ub1 piece;

    {
    static ub4 piece_count = 0;

    piece_count++;

    switch (piece)
    {
      case OCI_LAST_PIECE:

        /* process buffer bufxp */
        --- buffer processing code goes here ---

        (void) printf("callback read the %d th piece\n\n", piece_count);

        piece_count = 0;

        break;

      case OCI_FIRST_PIECE:
      case OCI_NEXT_PIECE:

        /* process buffer bufxp */
        --- buffer processing code goes here ---

        (void) printf("callback read the %d th piece\n", piece_count);

        break;

      default:

        (void) printf("callback read error: unkown piece = %d.\n", piece);

      return OCI_ERROR;
      }
      return OCI_CONTINUE;
    }

In the above example the user defined function cbk_read_lob is repeatedly called until all the LOB data has been read by the user.

### Writing LOBs using Callbacks

Similar to read callbacks, the user-defined write callback function is registered through the OCILobWrite() function. The callback function should have the following prototype:

     <CallbackFunctionName> ( dvoid *ctxp, dvoid *bufp, ub4 *len, ub1 *piece)

The first parameter, ctxp, is the context of the callback that is passed to OCI in the OCILobWrite() function call. The information provided by the user in ctxp, is passed back to the user when the callback function is called by the OCI (the OCI does not use this information on the way IN). The bufp parameter is the pointer to a storage area that contains the LOB data to be inserted, and bufl is the length of this storage area. The user provides this pointer in the call to OCILobWrite(). After inserting the data provided in the call to OCILobWrite() if there is more to write, then the user defined callback is called. In the callback the user should provide the data to insert in the storage indicated by bufp and also specify the length in bufl. The user should also indicate whether it is the next (OCI_NEXT_PIECE) or the last (OCI_LAST_PIECE) piece using the piece parameter. Note that the user is completely responsible for the storage pointer the application provides and should make sure that it does not write more than the allocated size of the storage.

The following is a code fragment of a typical way to implement write callback functions.

Assume here that lobl is a valid locator that has been locked for updating, svchp is a valid service handle and errhp is a valid error handle

    ...

    ub4   offset = 1;
    ub1   bufp[MAXBUFLEN];
    ub4   amtp = MAXBUFLEN * 20;
    ub4   nbytes = MAXBUFLEN;

    /* Fill bufp with some data */

    -- code to fill bufp with data goes here. nbytes should reflect the size and
    should be less than or equal to MAXBUFLEN --

    		 if (retval = OCILobWrite(svchp, errhp, lobl, &amtp, offset, (dvoid*)
            bufp,(ub4)nbytes, OCI_FIRST_PIECE, (dvoid *)0, cbk_write_lob,
            (ub2) 0, (ub1) SQLCS_IMPLICIT))

      {
        (void) printf("ERROR: OCILobWrite().\n");
        report_error();
        return;
      }
     ...

    sb4 cbk_write_lob(ctxp, bufxp, lenp, piece)
    dvoid *ctxp;
    dvoid *bufxp;
    ub4 *lenp;
    ub1 *piece;

    {
      /* Fill bufxp with  data */

      -- code to fill bufxp with data goes here. *lenp should reflect the size
      and should be less than or equal to MAXBUFLEN --

      if (this is the last data buffer)

        *piecep = OCI_LAST_PIECE;

      else

        *piecep = OCI_NEXT_PIECE;;

      return OCI_CONTINUE;
    }

In the above example, the user defined function cbk_write_lob is repeatedly called until the user indicates that the application is providing the last piece using the piecep parameter.

## OCI Callbacks From External Procedures

There are four OCI functions that can be used as callbacks from external procedures. These functions are listed in Chapter 16, "OCI External Procedure Functions".

For information about writing C subroutines that can be called from
PL/SQL code, including a list of which OCI calls can be used, and some example code, refer to the PL/SQL User's Guide and Reference.

## Application Failover Callbacks

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

  Note: To use application failover you must be using the Oracle8 Enterprise Edition with the Parallel Server Option.

  See Also: For more detailed information about application failover, refer to the Oracle8 Parallel Server Concepts and Administration manual.

### Failover Callback Overview

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

The first call to the callback function occurs when Oracle first detects an instance connection loss. This callback is intended to allow the application to inform the user of an upcoming delay. If failover is successful, a second call to the callback function occurs when the connection is reestablished and usable. At this time the client may wish to replay ALTER SESSION commands and inform the user that failover has happened. If failover is unsuccessful, then the callback is called to inform the application that failover will not take place. Additionally, the callback is called each time a user handle besides the primary handle is reauthenticated on the new connection. Since each user handle represents a server-side session, the client may wish to replay ALTER SESSION commands for that session.

### Failover Callback Structure and Parameters

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

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

Each of the parameters is described below, and an example is provided in the section "Failover Callback Example" on page 7-38.

##### svchp

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

##### envhp

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

##### fo_ctx

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

##### fo_type

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

- OCI_FO_SESSION, which indicates that the user has requested only session failover, and
- OCI_FO_SELECT, which indicates that the user has requested select failover as well.

##### fo_event

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

- OCI_FO_BEGIN indicates that failover has detected a lost connection and failover is starting.
- OCI_FO_END indicates successful completion of failover.
- OCI_FO_ABORT indicates that failover was unsuccessful.
- OCI_FO_REAUTH indicates that a user handle has been reauthenticated. To find out which, the application should check the OCI_ATTR_SESSION attribute of the service context handle (which is the first parameter).

### Failover Callback Registration

For the failover callback to be used, it must be registered on the server context handle. This registration is done by creating a callback definition structure and setting the OCI_ATTR_FOCBK attribute of the server handle to this structure. The callback definition structure must be of type OCIFocbkStruct. It has two fields: callback_function, which contains the address of the function to call, and fo_ctx which contains the address of the client context.

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

### Failover Callback Example

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

#### Part 1, Failover Callback Definition

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

#### Part 2, Failover Callback Registration

    int register_callback(svrh, errh)
    dvoid *svrh;					/* the server handle */
    OCIError *errh;						 /* the error handle */
    {
      OCIFocbkStruct failover;                 /*  failover callback structure */

      /* allocate memory for context */
      if (!(failover.fo_ctx = (dvoid *)malloc(strlen("my context."))))
        return(1);

      /* initialize the context. */
      strcpy((char *)failover.context_function, "my context.");

      failover.callback_function = &callback_fn;

      /* do the registration */
      if (OCIAttrSet(srvh, (ub4) OCI_HTYPE_SRV,
                    (dvoid *) &failover, (ub4) 0,
                    (ub4) OCI_ATTR_FOCBK, errh)  != OCI_SUCCESS)
        return(2);

      /* successful conclusion */
      return (0);
    }

## OCI and Advanced Queueing

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

  Note: In order to use advanced queueing, you must be using the Oracle8 Enterprise Edition. To use AQ with queues of datatypes other than RAW, you must also have purchased the Objects Option.

  See Also: For detailed information about AQ, including concepts, features, and examples, refer to the chapter on Advanced Queueing in the Oracle8 Application Developer's Guide.

  For example code demonstrating the use of the OCI with AQ, refer to the description of OCIAQEnq() on page 13-11.

### OCI Advanced Queueing Functions

The OCI library includes two functions related to advanced queueing:

- OCIAQEnq()
- OCIAQDeq()

Chapter 13, "OCI Relational Functions", contains complete descriptions of these functions and their parameters.

### OCI Advanced Queueing Descriptors

The following descriptors are used by OCI AQ operations:

- OCIAQEnqOptions - equivalent to dbms_aq.enqueue_options_t
- OCIAQDeqOptions - equivalent to dbms_aq.dequeue_options_t
- OCIAQMsgProperties - equivalent to dbms_aq.message_properties_t
- OCIAQAgent - equivalent to sys.aq$_agent

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

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

As with other OCI descriptors, the structure of these descriptors is opaque to the user. Each descriptor has a variety of attributes which can be set and/or read. These attributes are described in more detail in "Advanced Queueing Descriptor Attributes" on page B-28.

### Advanced Queueing in OCI vs. PL/SQL

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

PL/SQL Function | OCI Function
--- | ---
DBMS_AQ.ENQUEUE | OCIAQEnq()
DBMS_AQ.DEQUEUE | OCIAQDeq()

DBMS_AQ.ENQUEUE Parameter | OCIAQEnq() Parameter
--- | ---
queue_name | queue_name
enqueue_options | enqueue_options
message_properties | message_properties
payload | payload
msgid | msgid
Note: OCIAQEnq() also requires the following additional parameters: svch, errh, payload_tdo, payload_ind, and flags

DBMS_AQ.DEQUEUE Parameter | OCIAQDeq() Parameter
--- | ---
queue_name | queue_name
dequeue_options | dequeue_options
message_properties | message_properties
payload | payload
msgid | msgid
Note: OCIAQDeq() also requires the following additional parameters: svch, errh, payload_tdo, payload_ind, and flags

PL/SQL Agent Parameter | OCIAQAgent Attribute
--- | ---
name | OCI_ATTR_AGENT_NAME
address | OCI_ATTR_AGENT_ADDRESS
protocol | OCI_ATTR_AGENT_PROTOCOL

PL/SQL Message Property | OCIAQMsgProperties Attribute
--- | ---
priority | OCI_ATTR_PRIORITY
delay | OCI_ATTR_DELAY
expiration | OCI_ATTR_EXPIRATION
correlation | OCI_ATTR_CORRELATION
attempts | OCI_ATTR_ATTEMPTS
recipient_list | OCI_ATTR_RECIPIENT_LIST
exception_queue | OCI_ATTR_EXCEPTION_QUEUE
enqueue_time | OCI_ATTR_ENQ_TIME
state | OCI_ATTR_MSG_STATE

PL/SQL Enqueue Option | OCIAQEnqOptions Attribute
--- | ---
visibility | OCI_ATTR_VISIBILITY
relative_msgid | OCI_ATTR_RELATIVE_MSGID
sequence_deviation | OCI_ATTR_SEQUENCE_DEVIATION

PL/SQL Dequeue Option | OCIAQDeqOptions Attribute
--- | ---
consumer_name | OCI_ATTR_CONSUMER_NAME
dequeue_mode | OCI_ATTR_DEQ_MODE
navigation | OCI_ATTR_NAVIGATION
visibility | OCI_ATTR_VISIBILITY
wait | OCI_ATTR_WAIT
msgid | OCI_ATTR_DEQ_MSGID
correlation | OCI_ATTR_CORRELATION

## Writing Oracle Security Services Applications

For information about writing C applications using the Oracle Security Services Toolkit, refer to the Oracle Security Server Guide.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index