Programmer's Guide to the Oracle Call Interface
Release 8.0

A54656_01

Library

Product

Contents

Index

Prev Next

7
OCI Programming Advanced Topics

The following topics are covered in this chapter:

Note: for information relating to the use of the Oracle8 OCI for dealing with objects in an Oracle8 Server, see Chapter 8, "Object-Relational Programming with the OCI".

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.

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.

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 need to 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 an application are:

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.

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

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:

Table 7-1 Components of an XID
Component   Value  

gtrid  

12  

bqual  

34  

gtrid+bqual=XID  

1234  

See Also: For more information about transaction identifiers, refer to the Oracle8 Server Distributed 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.

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, both the gtrid and bqual components of the branch XIDs would be different, because they are separate branches operating on different transactions

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

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.

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

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  

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 UPDATE  

 

 

Update fails, because transaction is read-only  

3  

OCITransCommit  

 

 

Commit has no effect  

Read-Only Update Fails

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  

Update Successfully, One-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.  

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

Related Initialization Parameters

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

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:

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:
    1. 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.
    2. 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:
    1. 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.
    2. 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-18 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:

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:

Some LOB/FILE operations also provide piecewise semantics for reading or writing data. See the descriptions of OCILobWrite() on page 13-75 and OCILobRead() on page 13-71 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:

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 Figure 7-5 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:

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

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-64, and "Example 6, LOB Buffering" on page D-82.

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 create an internal LOB locator with a call to OCIDescriptorAlloc() by passing OCI_DTYPE_LOB as the descriptor type. To create an external LOB (FILE) locator, pass OCI_DTYPE_FILE.

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 - 75.
For more information about LOBs, locators, and read-consistent LOBs, see the Oracle8 Server 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-26.

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

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 Server Application Developer's Guide.

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:

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

LOB/FILE Functions

The functions in Table 7-2 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 Server Application Developer's Guide.
Table 7-2 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 write data or handle read data. 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 an decompression algorithm for reading it.

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.

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

svchp

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

envhp

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

fo_ctx

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

fo_type

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

fo_event

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

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

Writing Oracle Security Services Applications

For information about writing C applications using the Oracle Security Services Toolkit, refer to the Programmer's Guide to the Oracle Security Service Toolkit.




Prev

Next
Oracle
Copyright © 1997 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index