Release 8.0
A58234-01
 Library |
 Product |
 Contents |
 Index |
| Oracle Call Interface Programmer's Guide, Volumes 1 & 2 Release 8.0 A58234-01 |
|
The following topics are covered in this chapter:
Note: for information on using the OCI to manipulate objects in an Oracle8 server, see Chapter 8, "OCI Object-Relational Programming".
Chapter 2 introduced the basic concepts of OCI programming. This chapter is designed to introduce more advanced concepts, including the following:
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.
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.
This section describes OCI support for thread safety and multithreaded application development.
Inserting, updating, and fetching data in a piecewise fashion is described in this section.
This section describes OCI functions available for operating on LOBs and FILEs.
This section contains a pointer to information about writing external subroutines.
This section discusses how to write and use application failover callback functions.
This section covers the OCI functions related to Oracle8's Advanced Queueing feature.
This section contains a pointer to information on writing Oracle Security Services Applications.
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.
The OCI supports three levels of transaction complexity. Each level is described in one of the following sections.
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:
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.
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 are necessary only in more sophisticated transaction-processing applications.
This section provides some background about global transactions, and then gives specific information about using OCI calls to process global transactions.
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 |
|---|---|
|
|
|
|
|
|
|
|
|
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.
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
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
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:
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.
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.
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.
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:
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.
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 |
Starts new read-write transaction |
|
2 |
SQL UPDATE |
|
|
Update rows |
|
3 |
OCITransCommit |
|
|
Commit succeeds |
Two initialization parameters relate to the use of global transaction branches and migratable open connections:
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.
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.
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.
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.
The implementation of thread safety in the Oracle Call Interface provides the following benefits and advantages:
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.
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.
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.
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.
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.
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.
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.
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-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.
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:
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.
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.
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.
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-17.
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.
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.
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
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:
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.
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.
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.
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.
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:
For more information about object operations, such as marking, flushing, and refreshing, refer to Chapter 8, "OCI Object-Relational Programming".
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.
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 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.
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.
For a table showing the number of server roundtrips required for individual OCI LOB functions, refer to Appendix E, "OCI Function Server Roundtrips".
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.
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.
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.
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.
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 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.
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.
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.
The first parameter, svchp, is the service context handle. It is of type dvoid *.
The second parameter, envhp, is the OCI environment handle. It is of type dvoid *.
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 *.
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:
The last parameter is the failover event. This indicates to the callback why it is being called. It has several possible values:
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.
The following code shows an example of a simple user-defined callback function definition and registration.
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; }
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); }
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.
The OCI library includes two functions related to advanced queueing:
Chapter 13, "OCI Relational Functions", contains complete descriptions of these functions and their parameters.
The following descriptors are used by OCI AQ operations:
You can allocate these descriptors with respect to the service handle using the standard OCIDescriptorAlloc() call. The following code shows examples of this:
OCIDescriptorAlloc(svch, &enqueue_options, OCI_DTYPE_AQENQ_OPTIONS, 0, 0 ); OCIDescriptorAlloc(svch, &dequeue_options, OCI_DTYPE_AQDEQ_OPTIONS, 0, 0 ); OCIDescriptorAlloc(svch, &message_properties, OCI_DTYPE_AQMSG_PROPERTIES, 0, 0); OCIDescriptorAlloc(svch, &agent, OCI_DTYPE_AQAGENT, 0, 0 );
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.
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() |
| PL/SQL Agent Parameter | OCIAQAgent Attribute |
|---|---|
|
name |
OCI_ATTR_AGENT_NAME |
|
address |
OCI_ATTR_AGENT_ADDRESS |
|
protocol |
OCI_ATTR_AGENT_PROTOCOL |
| PL/SQL Enqueue Option | OCIAQEnqOptions Attribute |
|---|---|
|
visibility |
OCI_ATTR_VISIBILITY |
|
relative_msgid |
OCI_ATTR_RELATIVE_MSGID |
|
sequence_deviation |
OCI_ATTR_SEQUENCE_DEVIATION |
For information about writing C applications using the Oracle Security Services Toolkit, refer to the Oracle Security Server Guide.
