| Oracle® Call Interface Programmer's Guide 10g Release 1 (10.1) Part Number B10779-01 |
|
|
View PDF |
| Oracle® Call Interface Programmer's Guide 10g Release 1 (10.1) Part Number B10779-01 |
|
|
View PDF |
This chapter introduces you to the basic concepts involved in programming with the OCI.
This chapter contains these topics:
This chapter provides an introduction to the concepts and procedures involved in developing an OCI application. After reading this chapter, you should have most of the tools necessary to understand and create a basic OCI application.
This chapter is broken down into the following major sections:
New users should pay particular attention to the information presented in this chapter, because it forms the basis for the rest of the material presented in this guide. The information in this chapter is supplemented by information in later chapters.
See Also:
|
The general goal of an OCI application is to operate on behalf of multiple users. In an n-tiered configuration, multiple users are sending HTTP requests to the client application. The client application may need to perform some data operations that include exchanging data and performing data processing.
OCI uses the following basic program structure:
Figure 2-1, "Basic OCI Program Flow" illustrates the flow of steps in an OCI application. Each step is described in more detail in the section "OCI Programming Steps".
Text description of the illustration lnoci017.gif
Keep in mind that the diagram and the list of steps present a simple generalization of OCI programming steps. Variations are possible, depending on the functionality of the program. OCI applications that include more sophisticated functionality, such as managing multiple sessions and transactions and using objects, require additional steps.
All OCI function calls are executed in the context of an environment. There can be multiple environments within an OCI process. If an environment requires any process-level initialization, then it is performed automatically.
| See Also:
For information about accessing and manipulating objects, see Chapter 10, "OCI Object-Relational Programming" and the subsequent chapters |
Handles and descriptors are opaque data structures which are defined in OCI applications. They can be allocated directly, through specific allocate calls, or they can be implicitly allocated by OCI functions.
|
7.x Upgrade Note: Programmers who have previously written 7.x OCI applications need to become familiar with these new data structures which are used by most OCI calls. |
Handles and descriptors store information pertaining to data, connections, or application behavior. Handles are defined in more detail in the next section:
| See Also:
Descriptors are discussed in the section "OCI Descriptors" |
Almost all OCI calls include in their parameter list one or more handles. A handle is an opaque pointer to a storage area allocated by the OCI library. You use a handle to store context or connection information, (for example, an environment or service context handle), or it may store information about OCI functions or data (for example, an error or describe handle). Handles can make programming easier, because the library, rather than the application, maintains this data.
Most OCI applications need to access the information stored in handles. The get and set attribute OCI calls, OCIAttrGet() and OCIAttrSet(), access and set this information.
Table 2-1 lists the handles defined for the OCI. For each handle type, the C datatype and handle type constant used to identify the handle type in OCI calls are listed.
Your application allocates all handles (except the bind, define, and thread handles) with respect to a particular environment handle. You pass the environment handle as one of the parameters to the handle allocation call. The allocated handle is then specific to that particular environment.
The bind and define handles are allocated with respect to a statement handle, and contain information about the statement represented by that handle.
|
Note: The bind and define handles are implicitly allocated by the OCI library, and do not require user allocation. |
Figure 2-2, "Hierarchy of Handles" illustrates the various types of handles.
Text description of the illustration lnoci038.gif
The environment handle is allocated and initialized with a call to OCIEnvCreate() or to OCIEnvNlsCreate(), one of which is required by all OCI applications.
All user-allocated handles are initialized using the OCI handle allocation call, OCIHandleAlloc().
The thread handle is allocated with the OCIThreadHndInit() call.
An application must free all handles when they are no longer needed. The OCIHandleFree() function frees all handles.
Handles lessen the need for global variables. Handles also make error reporting easier. An error handle is used to return errors and diagnostic information.
| See Also:
For sample code demonstrating the allocation and use of OCI handles, see the example programs listed in Appendix B, "OCI Demonstration Programs" |
The environment handle defines a context in which all OCI functions are invoked. Each environment handle contains a memory cache, which enables fast memory access. All memory allocation under the environment handle is done from this cache. Access to the cache is serialized if multiple threads try to allocate memory under the same environment handle. When multiple threads share a single environment handle, they may block on access to the cache.
The environment handle is passed as the parent parameter to the OCIHandleAlloc() call to allocate all other handle types. Bind and define handles are allocated implicitly.
The error handle is passed as a parameter to most OCI calls. The error handle maintains information about errors that occur during an OCI operation. If an error occurs in a call, the error handle can be passed to OCIErrorGet() to obtain additional information about the error that occurred.
Allocating the error handle is one of the first steps in an OCI application because most OCI calls require an error handle as one of its parameters.
A service context handle defines attributes that determine the operational context for OCI calls to a server. The service context contains three handles as its attributes, that represent a server connection, a user session, and a transaction. These attributes are illustrated in Figure 2-3, "Components of a Service Context":
Text description of the illustration lnoci019.gif
Breaking the service context down in this way provides scalability and enables programmers to create sophisticated multitiered applications and transaction processing (TP) monitors for execute requests on behalf of multiple users on multiple application servers and different transaction contexts.
You must allocate and initialize the service context handle with OCIHandleAlloc() or OCILogon() before you can use it. The service context handle is allocated explicitly by OCIHandleAlloc(). It can be initialized using OCIAttrSet() with the server, session, and transaction handle. If the service context handle is allocated implicitly using OCILogon(), it is already initialized.
Applications maintaining only a single user session for each database connection at any time can call OCILogon() to get an initialized service context handle.
In applications requiring more complex session management, the service context must be explicitly allocated, and the server and user session handles must be explicitly set into the service context. OCIServerAttach() and OCISessionBegin() calls initialize the server and user session handle respectively.
An application will only define a transaction explicitly if it is a global transaction or there are multiple transactions active for sessions. It will be able to work correctly with the implicit transaction created automatically by OCI when the application makes changes to the database.
See Also:
|
A statement handle is the context that identifies a SQL or PL/SQL statement and its associated attributes.
Text description of the illustration lnoci041.gif
Information about input and output bind variables is stored in bind handles. The OCI library allocates a bind handle for each placeholder bound with the OCIBindByName() or OCIBindByPos() function. The user does not need to allocate bind handles. They are implicitly allocated by the bind call.
Fetched data returned by a query (select statement) is converted and retrieved according to the specifications of the define handles. The OCI library allocates a define handle for each output variable defined with OCIDefineByPos(). The user does not need to allocate define handles. They are implicitly allocated by the define call.
Bind and define handles are freed when the statement handle is freed or when a new statement is prepared on the statement handle.
The describe handle is used by the OCI describe call, OCIDescribeAny(). This call obtains information about schema objects in a database (for example, functions, procedures). The call takes a describe handle as one of its parameters, along with information about the object being described. When the call completes, the describe handle is populated with information about the object. The OCI application can then obtain describe information through the attributes of parameter descriptors.
| See Also:
Chapter 6, "Describing Schema Metadata", for more information about using the |
The complex object retrieval (COR) handle is used by some OCI applications that work with objects in an Oracle database server. This handle contains COR descriptors, which provide instructions for retrieving objects referenced by another object.
For information about the thread handle, which is used in multithreaded applications:
The subscription handle is used by an OCI client application that registers and subscribes to receive notifications of database events or events in the AQ namespace. The subscription handle encapsulates all information related to a registration from a client.
The direct path handles are necessary for an OCI application that uses the direct path load engine in the Oracle database server. The direct path load interface enables the application to access the direct block formatter of the Oracle server.
Text description of the illustration lnoci042.gif
The connection pool handle is used for applications that pool physical connections into virtual connections by calling specific OCI functions.
.All OCI handles have attributes that represent data stored in that handle. You can read handle attributes using the attribute get call, OCIAttrGet(), and you can change them with the attribute set call, OCIAttrSet().
For example, the following statements set the user name in the session handle by writing to the OCI_ATTR_USERNAME attribute:
text username[] = "hr"; err = OCIAttrSet ((dvoid*) mysessp, OCI_HTYPE_SESSION, (dvoid*)username, (ub4) strlen((char *)username), OCI_ATTR_USERNAME, (OCIError *) myerrhp);
Some OCI functions require that particular handle attributes be set before the function is called. For example, when OCISessionBegin() is called to establish a user's login session, the user name and password must be set in the user session handle before the call is made.
Other OCI functions provide useful return data in handle attributes after the function completes. For example, when OCIStmtExecute() is called to execute a SQL query, describe information relating to the select-list items is returned in the statement handle.
ub4 parmcnt; /* get the number of columns in the select list */ err = OCIAttrGet ((dvoid *)stmhp, (ub4)OCI_HTYPE_STMT, (dvoid *) &parmcnt, (ub4 *) 0, (ub4)OCI_ATTR_PARAM_COUNT, errhp);
See Also:
|
OCI descriptors and locators are opaque data structures that maintain data-specific information. Table 2-2 lists them, along with their C datatype, and the OCI type constant that allocates a descriptor of that type in a call to OCIDescriptorAlloc(). The OCIDescriptorFree() function frees descriptors and locators.
The main purpose of each descriptor type is listed here, and each descriptor type is described in the following sections:
OCISnapshot - used in statement executionOCILOBLocator - used for LOB (OCI_DTYPE_LOB) or FILE (OCI_DTYPE_FILE) callsOCIParam - used in describe callsOCIRowid - used for binding or defining ROWID valuesOCIDateTime and OCIInterval - used for datetime and interval datatypesOCIComplexObjectComp - used for complex object retrievalOCIAQEnqOptions, OCIAQDeqOptions, OCIAQMsgProperties, OCIAQAgent - used for Advanced QueuingOCIAQNotify - used for publish-subscribe notificationOCIServerDNs - used for LDAP-based publish-subscribe notificationThe snapshot descriptor is an optional parameter to the execute call, OCIStmtExecute(). It indicates that a query is being executed against a particular database snapshot which represents the state of a database at a particular point in time.
Allocate a snapshot descriptor with a call to OCIDescriptorAlloc(), by passing OCI_DTYPE_SNAP as the type parameter.
| See Also:
For more information about |
A large object (LOB) is an Oracle datatype that can hold binary (BLOB) or character (CLOB) data. In the database, an opaque data structure called a LOB locator is stored in a LOB column of a database row, or in the place of a LOB attribute of an object. The locator serves as a pointer to the actual LOB value, which is stored in a separate location.
The OCI LOB locator is used to perform OCI operations against a LOB (BLOB or CLOB) or FILE (BFILE). OCILobXXX functions take a LOB locator parameter instead of the LOB value. OCI LOB functions do not use actual LOB data as parameters. They use the LOB locators as parameters and operate on the LOB data referenced by them.
The LOB locator is allocated with a call to OCIDescriptorAlloc(), by passing OCI_DTYPE_LOB as the type parameter for BLOBs or CLOBs, and OCI_DTYPE_FILE for BFILEs.
An OCI application can retrieve a LOB locator from the server by issuing a SQL statement containing a LOB column or attribute as an element in the select list. In this case, the application would first allocate the LOB locator and then use it to define an output variable. Similarly, a LOB locator can be used as part of a bind operation to create an association between a LOB and a placeholder in a SQL statement.
OCI applications use parameter descriptors to obtain information about select-list columns or schema objects. This information is obtained through a describe operation.
The parameter descriptor is the only descriptor type that is not allocated using OCIDescriptorAlloc(). You can obtain it only as an attribute of a describe handle, statement handle, or through a complex object retrieval handle by specifying the position of the parameter using an OCIParamGet() call.
| See Also:
Chapter 6, "Describing Schema Metadata", and "Describing Select-list Items" for more information about obtaining and using parameter descriptors |
The ROWID descriptor, OCIRowid, is used by applications that need to retrieve and use Oracle ROWIDs. To work with a ROWID using OCI release 8 or later, an application can define a ROWID descriptor for a rowid position in a SQL select-list, and retrieve a ROWID into the descriptor. This same descriptor can later be bound to an input variable in an INSERT statement or WHERE clause.
ROWIDs are also redirected into descriptors using OCIAttrGet() on the statement handle following an execute.
These descriptors are used by applications which use the date, datetime, or interval datatypes (OCIDate, OCIDateTime, and OCIInterval). These descriptors can be used for binding and defining, and are passed as parameters to the functions OCIDescAlloc() and OCIDescFree() to allocate and free memory.
See Also:
|
Application performance when dealing with objects may be increased through the use of complex object retrieval (COR).
| See Also:
For information about the complex object descriptor and its use, refer to "Complex Object Retrieval". |
Oracle AQ provides message queuing as an integrated part of the Oracle server.
The OCIDescriptorAlloc() call has an xtramem_sz parameter in its parameter list. This parameter is used to specify an amount of user memory which should be allocated along with a descriptor or locator.
Typically, an application uses this parameter to allocate an application-defined structure that has the same lifetime as the descriptor or locator. This structure maybe used for application bookkeeping or storing context information.
Using the xtramem_sz parameter means that the application does not need to explicitly allocate and deallocate memory as each descriptor or locator is allocated and deallocated. The memory is allocated along with the descriptor or locator, and freeing the descriptor or locator (with OCIDescriptorFree()) frees the user's data structures as well.
The OCIHandleAlloc() call has a similar parameter for allocating user memory which has the same lifetime as the handle.
The OCIEnvCreate() and OCIEnvInit() calls have a similar parameter for allocating user memory which has the same lifetime as the environment handle.
Each of the steps in developing an OCI application is described in detail in the following sections. Some of the steps are optional. For example, you do not need to describe or define select-list items if the statement is not a query.
See Also:
|
The following sections describe the steps that are required of an OCI application:
Application-specific processing will also occur in between any and all of the OCI function steps.
This section describes how to initialize the OCI environment, establish a connection to a server, and authorize a user to perform actions against the database.
First, the three main steps in initializing the OCI environment are described in the following sections:
Each OCI function call is executed in the context of an environment that is created with the OCIEnvCreate() call. This call must be invoked before any other OCI call is executed. The only exception is the setting of a process-level attribute for the OCI shared mode.
The mode parameter of OCIEnvCreate() specifies whether the application calling the OCI library functions will:
mode = OCI_THREADED).mode = OCI_OBJECT).mode = OCI_EVENTS).The mode can be set independently in each environment.
It is necessary to initialize in object mode if the application binds and defines objects, or if it uses the OCI's object navigation calls. The program may also choose to use none of these features (mode = OCI_DEFAULT) or some combination of them, separating the options with a vertical bar. For example if mode = (OCI_THREADED | OCI_OBJECT), then the application runs in a threaded environment and uses objects.
You can specify user-defined memory management functions for each OCI environment.
See Also:
|
Oracle provides OCI functions to allocate and deallocate handles and descriptors. You must allocate handles using OCIHandleAlloc() before passing them into an OCI call, unless the OCI call, such as OCIBindByPos(), allocates the handles for you.
You can allocate the types of handles listed in Table 2-1, "OCI Handle Types"with OCIHandleAlloc() Depending on the functionality of your application, it needs to allocate some or all of these handles.
An application must call OCIEnvCreate() to initialize the OCI environment handle.
Following this step, the application has two options for establishing a server connection and beginning a user session: Single User, Single Connection; or Multiple Sessions or Connections.
|
Note:
|
This option is the simplified logon method, which can be used if an application maintains only a single user session for each database connection at any time.
When an application calls OCILogon(), the OCI library initializes the service context handle that is passed to it, and creates a connection to the specified server for the user making the request.
The following is an example of what a call to OCILogon() might look like:
OCILogon(envhp, errhp, &svchp, (text *)"hr", nameLen, (text *)"hr", passwdLen, (text *)"oracledb", dbnameLen);
The parameters to this call include the service context handle (which are initialized), the user name, the user's password, and the name of the database that are used to establish the connection. The server and user session handles are implicitly allocated by this function.
If an application uses this logon method, the service context, server, and user session handles will all be read-only; the application cannot switch session or transaction by changing the appropriate attributes of the service context handle by means of an OCIAttrSet() call.
An application that initializes its session and authorization using OCILogon() must terminate them using OCILogoff().
This option uses explicit attach and begin session calls to maintain multiple user sessions and connections on a database connection.Specific calls to attach to the server and begin sessions are:
OCIServerAttach()- creates an access path to the data server for OCI operations.OCISessionBegin()- establishes a session for a user against a particular server. This call is required for the user to execute operations on the server.A subsequent call to OCISessionBegin() using different service context and session context handles logs off the previous user and causes an error, To run two simultaneous non-migratable sessions, a second OCISessionBegin() call must be made with the same service context handle and a new session context handle.
These calls set up an operational environment that enables you to execute SQL and PL/SQL statements against a database.
See Also:
|
The following example demonstrates the use of creating and initializing an OCI environment.
#include <oci.h> ... main() { ... OCIEnv *myenvhp; /* the environment handle */ OCIServer *mysrvhp; /* the server handle */ OCIError *myerrhp; /* the error handle */ OCISession *myusrhp; /* user session handle */ OCISvcCtx *mysvchp; /* the service handle */ .. /* initialize the mode to be the threaded and object environment */ (void) OCIEnvCreate(&myenvhp, OCI_THREADED|OCI_OBJECT, (dvoid *)0, 0, 0, 0, (size_t) 0, (dvoid **)0); /* allocate a server handle */ (void) OCIHandleAlloc ((dvoid *)myenvhp, (dvoid **)&mysrvhp, OCI_HTYPE_SERVER, 0, (dvoid **) 0); /* allocate an error handle */ (void) OCIHandleAlloc ((dvoid *)myenvhp, (dvoid **)&myerrhp, OCI_HTYPE_ERROR, 0, (dvoid **) 0); /* create a server context */ (void) OCIServerAttach (mysrvhp, myerrhp, (text *)"inst1_alias", strlen ("inst1_alias"), OCI_DEFAULT); /* allocate a service handle */ (void) OCIHandleAlloc ((dvoid *)myenvhp, (dvoid **)&mysvchp, OCI_HTYPE_SVCCTX, 0, (dvoid **) 0); /* set the server attribute in the service context handle*/ (void) OCIAttrSet ((dvoid *)mysvchp, OCI_HTYPE_SVCCTX, (dvoid *)mysrvhp, (ub4) 0, OCI_ATTR_SERVER, myerrhp); /* allocate a user session handle */ (void) OCIHandleAlloc ((dvoid *)myenvhp, (dvoid **)&myusrhp, OCI_HTYPE_SESSION, 0, (dvoid **) 0); /* set user name attribute in user session handle */ (void) OCIAttrSet ((dvoid *)myusrhp, OCI_HTYPE_SESSION, (dvoid *)"hr", (ub4)strlen("hr"), OCI_ATTR_USERNAME, myerrhp); /* set password attribute in user session handle */ (void) OCIAttrSet ((dvoid *)myusrhp, OCI_HTYPE_SESSION, (dvoid *)"hr", (ub4)strlen("hr"), OCI_ATTR_PASSWORD, myerrhp); (void) OCISessionBegin ((dvoid *) mysvchp, myerrhp, myusrhp, OCI_CRED_RDBMS, OCI_DEFAULT); /* set the user session attribute in the service context handle*/ (void) OCIAttrSet ( (dvoid *)mysvchp, OCI_HTYPE_SVCCTX, (dvoid *)myusrhp, (ub4) 0, OCI_ATTR_SESSION, myerrhp); ... }
The demonstration program cdemo81.c in the demo directory illustrates this process, with error-checking.
A chapter of this manual outlines the specific steps involved in processing SQL statements in OCI.
An application commits changes to the database by calling OCITransCommit(). This call uses a service context as one of its parameters. The transaction is associated with the service context whose changes are committed. This transaction can be explicitly created by the application or implicitly created when the application modifies the database.
|
Note: Using the OCI_COMMIT_ON_SUCCESS mode of the |
To roll back a transaction, use the OCITransRollback() call.
If an application disconnects from Oracle in some way other than a normal logoff, such as losing a network connection, and OCITransCommit() has not been called, all active transactions are rolled back automatically.
An OCI application should perform the following three steps before it terminates:
OCISessionEnd() for each session.OCIServerDetach() for each source.OCIHandleFree() for each handle.The calls to OCIServerDetach() and OCISessionEnd() are not mandatory, but are recommended. If the application terminates, and OCITransCommit() (transaction commit) has not been called, any pending transactions are automatically rolled back
| See Also:
For an example showing handles being freed at the end of an application, refer to the first sample program in Appendix B, "OCI Demonstration Programs" |
OCI function calls have a set of return codes, listed in Table 2-3, "OCI Return Codes", which indicate the success or failure of the call, such as OCI_SUCCESS or OCI_ERROR, or provide other information that may be required by the application, such as OCI_NEED_DATA or OCI_STILL_EXECUTING. Most OCI calls return one of these codes.
To verify that the connection to the server is not terminated by the OCI_ERROR, an application can check the value of the attribute OCI_ATTR_SERVER_STATUS in the server handle. If the value of the attribute is OCI_SERVER_NOT_CONNECTED, then the connection to the server and the user session must be re-established.
See Also:
|
If the return code indicates that an error has occurred, the application can retrieve Oracle-specific error codes and messages by calling OCIErrorGet(). One of the parameters to OCIErrorGet() is the error handle passed to the call that caused the error.
In Table 2-4, the OCI return code, error number, indicator variable, and column return code are specified when the data fetched is normal, null, or truncated.
| See Also:
"Indicator Variables" for a discussion of indicator variables. |
For truncated data, data_len is the actual length of the data that has been truncated if this length is less than or equal to SB2MAXVAL. Otherwise, the indicator is set to -2.
Some functions return values other than the OCI error codes listed in Table 2-3. When using these function be aware that they return values directly from the function call, rather than through an OUT parameter. More detailed information about each function and its return values is listed in the reference chapters.
This section explains some additional issues when coding OCI applications.
OCI functions take a variety of different types of parameters, including integers, handles, and character strings. Special considerations must be taken into account for some types of parameters, as described in the following sections.
| See Also:
"Connect, Authorize, and Initialize Functions" for more information about parameter datatypes and parameter passing conventions. |
Address parameters are used to pass the address of the variable to Oracle. You should be careful when developing in C, since it normally passes scalar parameters by value.
Binary integer and short binary integer parameters are numbers whose size is system-dependent. See your Oracle system-specific documentation for the size of these integers on your system.
Character strings are a special type of address parameter. Each OCI routine that enables a character string to be passed as a parameter also has a string length parameter. The length parameter should be set to the length of the string.
|
7.x Upgrade Note: Unlike earlier versions of the OCI, you do not pass -1 for the string length parameter of a null-terminated string. |
You can insert a null into a database column in several ways.
NULL in the text of an INSERT or UPDATE statement. For example, the SQL statement
INSERT INTO emp1 (ename, empno, deptno) VALUES (NULL, 8010, 20)
makes the ENAME column null.
NULL is to set the buffer length and maximum length parameters both to zero on a bind call.
Each bind and define OCI call has a parameter that associates an indicator variable, or an array of indicator variables, with a DML statement, a PL/SQL statement, or a query.
The C language does not have the concept of null values; therefore you associate indicator variables with input variables to specify whether the associated placeholder is a NULL. When data is passed to Oracle, the values of these indicator variables determine whether or not a NULL is assigned to a database field.
For output variables, indicator variables determine whether the value returned from Oracle is a NULL or a truncated value. In the case of a NULL fetch in an OCIStmtFetch() call, or a truncation in an OCIStmtExecute() call, the OCI call returns OCI_SUCCESS. The output indicator variable is set. If the application returns a code variable in the subsequent OCIDefineByPos() call, the OCI assigns a value of ORA-01405 (for NULL fetch) or ORA-01406 (for truncation) to the return code variable.
The datatype of indicator variables is sb2. In the case of arrays of indicator variables, the individual array elements should be of type sb2.
For input host variables, the OCI application can assign the following values to an indicator variable:
Table 2-5 Input Indicator Values| Input Indicator Value | Action Taken by Oracle |
|---|---|
|
-1 |
Oracle assigns a |
|
>=0 |
Oracle assigns the value of the input variable to the column. |
On output, Oracle can assign the following values to an indicator variable:
Table 2-6 Output Indicator ValuesIndicator variables for most datatypes introduced after release 8.0 behave as described earlier. The only exception is SQLT_NTY (a named datatype). For data of type SQLT_NTY, the indicator variable must be a pointer to an indicator structure. Data of type SQLT_REF uses a standard scalar indicator, just like other variable types.
When database types are translated into C struct representations using the Object Type Translator (OTT), a null indicator structure is generated for each object type. This structure includes an atomic null indicator, plus indicators for each object attribute.
See Also:
|
On most operating systems, you can cancel long-running or repeated OCI calls, by entering the operating system's interrupt character (usually CTRL-C) from the keyboard.
|
Note: This is not to be confused with cancelling a cursor, which is accomplished by calling |
When you cancel the long-running or repeated call using the operating system interrupt, the error code ORA-01013 ("user requested cancel of current operation") is returned.
Given a particular service context pointer or server context pointer, the OCIBreak() function performs an immediate (asynchronous) stop of any currently executing OCI function associated with the server. It is normally used to stop a long-running OCI call being processed on the server. The OCIReset() function is necessary to perform a protocol synchronization on a nonblocking connection after an OCI application stops a function with OCIBreak().
The status of potentially long-running calls can be monitored through the use of nonblocking calls.
You can use the ROWID associated with a SELECT...FOR UPDATE OF... statement in a later UPDATE or DELETE statement. The ROWID is retrieved by calling OCIAttrGet() on the statement handle to retrieve the handle's OCI_ATTR_ROWID attribute.
For example, for a SQL statement such as
SELECT ename FROM emp1 WHERE empno = 7499 FOR UPDATE OF sal
when the fetch is performed, the ROWID attribute in the handle contains the row identifier of the selected row. You can retrieve the ROWID into a buffer in your program by calling OCIAttrGet() as follows:
OCIRowid *rowid; /* the rowid in opaque format */ /* allocate descriptor with OCIDescriptorAlloc() */ status = OCIDescriptorAlloc ((dvoid *) envhp, (dvoid **) &rowid, (ub4) OCI_DTYPE_ROWID, (size_t) 0, (dvoid **) 0); status = OCIAttrGet ((dvoid*) mystmtp, OCI_HTYPE_STMT, (dvoid*) rowid, (ub4 *) 0, OCI_ATTR_ROWID, (OCIError *) myerrhp);
You can then use the saved ROWID in a DELETE or UPDATE statement. For example, if rowid is the buffer in which the row identifier has been saved, you can later process a SQL statement such as
UPDATE emp1 SET sal = :1 WHERE rowid = :2
by binding the new salary to the :1 placeholder and rowid to the :2 placeholder. Be sure to use datatype code 104 (ROWID descriptor) when binding rowid to :2.
Using prefetching, an array of ROWIDs can be selected for use in subsequent batch updates.
Some words are reserved by Oracle. That is, they have a special meaning to Oracle and cannot be redefined. For this reason, you cannot use them to name database objects such as columns, tables, or indexes.
| See Also:
To view the lists of the Oracle keywords or reserved words for SQL and PL/SQL, see the Oracle Database SQL Reference and the PL/SQL User's Guide and Reference |
Table 2-7, "Oracle Reserved Namespaces" contains a list of namespaces that are reserved by Oracle. The initial characters of function names in Oracle libraries are restricted to the character strings in this list. Because of potential name conflicts, do not use function names that begin with these characters.
For a complete list of functions within a particular namespace, refer to the document that corresponds to the appropriate Oracle library.
The OCI provides the ability to establish a server connection in blocking mode or nonblocking mode. When a connection is made in blocking mode, an OCI call returns control to an OCI client application only when the call completes, either successfully or in error. With the nonblocking mode, control is immediately returned to the OCI program if the call could not complete, and the call returns a value of OCI_STILL_EXECUTING.
In nonblocking mode, an application must test the return code of each OCI function to see if it returns OCI_STILL_EXECUTING. If it does, the OCI client can continue to process program logic while waiting to retry the OCI call to the server. This mode is particularly useful in Graphical User Interface (GUI) applications, real-time applications, and in distributed environments.
The nonblocking mode is not interrupt-driven. Rather, it is based on a polling paradigm, which means that the client application has to check whether the pending call is finished at the server by executing the call again with the exact same parameters.
|
Note: While waiting to retry nonblocking OCI call, the application may not issue any other OCI calls, or an ORA-03124 error will occur. The only exceptions to this rule are |
You can modify or check an application's blocking status by calling OCIAttrSet() to set the status, or OCIAttrGet() to read the status on the server context handle with the attrtype parameter set to OCI_ATTR_NONBLOCKING_MODE.
|
Note: Only functions that have server context or a service context handle as a parameter may return OCI_STILL_EXECUTING. |
You can cancel a long-running OCI call by using the OCIBreak() function while the OCI call is in progress. You must then issue an OCIReset() call to reset the asynchronous operation and protocol.
The following code is an example of nonblocking mode.
int main (int argc, char **argv) { sword retval; if (retval = InitOCIHandles()) /* initialize all handles */ { printf ("Unable to allocate handles..\n"); exit (EXIT_FAILURE); } if (retval = logon()) /* log on */ { printf ("Unable to log on...\n"); exit (EXIT_FAILURE); } if (retval = AllocStmtHandle ()) /* allocate statement handle */ { printf ("Unable to allocate statement handle...\n"); exit (EXIT_FAILURE); } /* set non-blocking on */ if (retval = OCIAttrSet ((dvoid *) srvhp, (ub4) OCI_HTYPE_SERVER, (dvoid *) 0, (ub4) 0, (ub4) OCI_ATTR_NONBLOCKING_MODE, errhp)) { printf ("Unable to set non-blocking mode...\n"); exit (EXIT_FAILURE); } while ((retval = OCIStmtExecute (svchp, stmhp, errhp, (ub4)0, (ub4)0, (OCISnapshot *) 0, (OCISnapshot *)0, OCI_DEFAULT)) == OCI_STILL_EXECUTING) printf ("."); printf ("\n"); if (retval != OCI_SUCCESS || retval != OCI_SUCCESS_WITH_INFO) { printf("Error in OCIStmtExecute...\n"); exit (EXIT_FAILURE); } if (retval = logoff ()) /* log out */ { printf ("Unable to logout ...\n"); exit (EXIT_FAILURE); } cleanup(); return (int)OCI_SUCCESS; } ...
PL/SQL is Oracle's procedural extension to the SQL language. PL/SQL supports tasks that are more complicated than simple queries and SQL data manipulation language (DML) statements. PL/SQL lets you group a number of constructs into a single block and execute it as a unit. These constructs include:
IF...THEN...ELSE statements and loopsYou can use PL/SQL blocks in your OCI program to perform the following operations:
CURSOR FOR loops, and exception handling| See Also:
PL/SQL User's Guide and Reference for information about coding PL/SQL blocks |
The following sections introduce OCI functions that can be used for globalization purposes, such as deriving locale information, manipulating strings, character set conversion, and OCI messaging. These functions are also described in detail in other chapters of this guide because they have multiple purposes and functionality.
The function OCIEnvNlsCreate() enables you to set character set information in applications, independently from NLS_LANG and NLS_NCHAR settings. One application can have several environment handles initialized within the same system environment using different client side character set IDs and national character set IDs.
OCIEnvNlsCreate(OCIEnv **envhp, ..., csid, ncsid);
where csid is the value for character set ID, and ncsid is the value for national character set ID. Either can be 0 or OCI_UTF16ID. If both are 0, this is equivalent to using OCIEnvCreate() instead. The other arguments are the same as for the OCIEnvCreate() call.
OCIEnvNlsCreate() is an enhancement for programmatic control of character sets, because it validates OCI_UTF16ID.
When character set IDs are set through the function OCIEnvNlsCreate(), they will replace the settings in NLS_LANG and NLS_NCHAR. In addition to all character sets supported by NLSRTL, OCI_UTF16ID is also allowed as a character set ID in the OCIEnvNlsCreate() function, although this ID is not valid in NLS_LANG or NLS_NCHAR.
Any Oracle character set ID, except AL16UTF16, can be specified through the OCIEnvNlsCreate() function to specify the encoding of metadata, SQL CHAR data, and SQL NCHAR data.
You can retrieve character sets in NLS_LANG and NLS_NCHAR through another function, OCINlsEnvironmentVariableGet().
For a pseudocode fragment that illustrates a sample usage of these calls:
OCINlsGetInfo() returns information about OCI_UTF16ID if this value has been used in OCIEnvNlsCreate().
OCIAttrGet() returns the character set ID and national character set ID that were passed into OCIEnvNlsCreate(). This is used to get OCI_ATTR_ENV_CHARSET_ID and OCI_ATTR_ENV_NCHARSET_ID. This includes the value OCI_UTF16ID.
If both charset and ncharset parameters were set to NULL by OCIEnvNlsCreate(), the character set IDs in NLS_LANG and NLS_NCHAR will be returned.
OCIAttrSet() sets character IDs as the defaults if OCI_ATTR_CHARSET_FORM is reset through this function. The eligible character set IDs include OCI_UTF16ID if OCIEnvNlsCreate() has it passed as charset or ncharset.
OCIBindByName() and OCIBindByPos() bind variables with default character set in the OCIEnvNlsCreate() call, including OCI_UTF16ID. The actual length and the returned length are always in bytes if OCIEnvNlsCreate() is used.
OCIDefineByPos() defines variables with the value of charset in OCIEnvNlsCreate(), including OCI_UTF16ID, as the default. The actual length and returned length are always in bytes if OCIEnvNlsCreate() is used. This behavior for bind and define handles is different from that when OCIEnvCreate() is used and OCI_UTF16ID is the character set ID for the bind and define handles.
OCI works as a translator between server and client, and passes around character information for constraint checking.
There are two kinds of character sets, variable-width and fixed-width, as a single byte character set is just a special case of a fixed-width character set where each byte stands for one character.
For fixed-width character sets, constraint checking is easier as number of bytes is simply equal to a multiple of number of characters. Therefore, no scanning of the entire string is needed to determine the number of characters for fixed-width character sets. However, for variable-width ones, complete scanning is needed to determine the number of characters.
See "Character Length Semantics Support in Describing" and "Character Conversion in OCI Binding and Defining" for a complete discussion.
Many globalization support functions accept either the environment handle or the user session handle. The OCI environment handle is associated with the client NLS environment variables. This environment does not change when ALTER SESSION statements are issued to the server. The character set associated with the environment handle is the client character set. The OCI session handle (returned by OCISessionBegin()) is associated with the server session environment. The NLS settings change when the session environment is modified with an ALTER SESSION statement. The character set associated with the session handle is the database character set.
Note that the OCI session handle does not have NLS settings associated with it until the first transaction begins in the session. SELECT statements do not begin a transaction.
For complete details and discussions of the functions that follow:
An Oracle locale consists of language, territory, and character set definitions. The locale determines conventions such as day and month names, as well as date, time, number, and currency formats. A globalized application follows a user's locale setting and cultural conventions. For example, when the locale is set to German, users expect to see day and month names in German.
You can retrieve the following information with the OCINlsGetInfo() function:
This example code retrieves information and checks for errors.
sword MyPrintLinguisticName(envhp, errhp) OCIEnv *envhp; OCIError *errhp; { OraText infoBuf[OCI_NLS_MAXBUFSZ]; sword ret; ret = OCINlsGetInfo(envhp, /* environment handle */ errhp, /* error handle */ infoBuf, /* destination buffer */ (size_t) OCI_NLS_MAXBUFSZ, /* buffer size */ (ub2) OCI_NLS_LINGUISTIC_NAME); /* item */ if (ret != OCI_SUCCESS) { checkerr(errhp, ret, OCI_HTYPE_ERROR); ret = OCI_ERROR; } else { printf("NLS linguistic: %s\n", infoBuf); } return(ret); }
Multibyte strings and wide character strings are supported for string manipulation:
Multibyte strings are encoded in native Oracle character sets. Functions that operate on multibyte strings take the string as a whole unit with the length of the string calculated in bytes. Wide character string (wchar) functions provide more flexibility in string manipulation. They support character-based and string-based operations where the length the string calculated in characters.
The wide character datatype is Oracle-specific and should not be confused with the wchar_t datatype defined by the ANSI/ISO C standard. The Oracle wide character datatype is always 4 bytes in all operating systems, while the size of wchar_t depends on the implementation and the operating system. The Oracle wide character datatype normalizes multibyte characters so that they have a uniform fixed width for easy processing. This guarantees no data loss for round trip conversion between the Oracle wide character set and the native character set.
String manipulation can be classified into the following categories:
The following example shows a simple case of manipulating strings.
size_t MyConvertMultiByteToWideChar(envhp, dstBuf, dstSize, srcStr) OCIEnv *envhp; OCIWchar *dstBuf; size_t dstSize; OraText *srcStr; /* null terminated source string */ { sword ret; size_t dstLen = 0; size_t srcLen; /* get length of source string */ srcLen = OCIMultiByteStrlen(envhp, srcStr); ret = OCIMultiByteInSizeToWideChar(envhp, /* environment handle */ dstBuf, /* destination buffer */ dstSize, /* destination buffer size */ srcStr, /* source string */ srcLen, /* length of source string */ &dstLen); /* pointer to destination length */ if (ret != OCI_SUCCESS) { checkerr(envhp, ret, OCI_HTYPE_ENV); } return(dstLen); }
The OCI character classification functions are described in detail.
The following example shows how to classify characters in OCI.
boolean MyIsNumberWideCharString(envhp, srcStr) OCIEnv *envhp; OCIWchar *srcStr; /* wide char source string */ { OCIWchar *pstr = srcStr; /* define and init pointer */ boolean status = TRUE; /* define and initialize status variable */ /* Check input */ if (pstr == (OCIWchar*) NULL) return(FALSE); if (*pstr == (OCIWchar) NULL) return(FALSE); /* check each character for digit */ do { if (OCIWideCharIsDigit(envhp, *pstr) != TRUE) { status = FALSE; break; /* non-decimal digit character */ } } while ( *++pstr != (OCIWchar) NULL); return(status); }
Conversion between Oracle character sets and Unicode (16-bit, fixed-width Unicode encoding) is supported. Replacement characters are used if a character has no mapping from Unicode to the Oracle character set. Therefore, conversion back to the original character set is not always possible without data loss.
The following example shows a simple conversion into Unicode.
/* Example of Converting Character Sets in OCI --------------------------------------------*/ size_t MyConvertMultiByteToUnicode(envhp, errhp, dstBuf, dstSize, srcStr) OCIEnv *envhp; OCIError *errhp; ub2 *dstBuf; size_t dstSize; OraText *srcStr; { size_t dstLen = 0; size_t srcLen = 0; OraText tb[OCI_NLS_MAXBUFSZ]; /* NLS info buffer */ ub2 cid; /* OCIEnv character set id */ /* get OCIEnv character set */ checkerr(errhp, OCINlsGetInfo(envhp, errhp, tb, sizeof(tb), OCI_NLS_CHARACTER_SET)); cid = OCINlsCharSetNameToId(envhp, tb); if (cid == OCI_UTF16ID) { ub2 *srcStrUb2 = (ub2*)srcStr; while (*srcStrUb2++) ++srcLen; srcLen *= sizeof(ub2); } else srcLen = OCIMultiByteStrlen(envhp, srcStr); checkerr(errhp, OCINlsCharSetConvert( envhp, /* environment handle */ errhp, /* error handle */ OCI_UTF16ID, /* Unicode character set id */ dstBuf, /* destination buffer */ dstSize, /* size of destination buffer */ cid, /* OCIEnv character set id */ srcStr, /* source string */ srcLen, /* length of source string */ &dstLen)); /* pointer to destination length */ return dstLen/sizeof(ub2); }
The user message API provides a simple interface for cartridge developers to retrieve their own messages and Oracle messages.
This example creates a message handle, initializes it to retrieve messages from impus.msg, retrieves message number 128, and closes the message handle. It assumes that OCI environment handles, OCI session handles, product, facility, and cache size have been initialized properly.
OCIMsg msghnd; /* message handle */ /* initialize a message handle for retrieving messages from impus.msg*/ err = OCIMessageOpen(hndl,errhp, &msghnd, prod,fac,OCI_DURATION_SESSION); if (err != OCI_SUCCESS) /* error handling */ ... /* retrieve the message with message number = 128 */ msgptr = OCIMessageGet(msghnd, 128, msgbuf, sizeof(msgbuf)); /* do something with the message, such as display it */ ... /* close the message handle when there are no more messages to retrieve */
OCIMessageClose(hndl, errhp, msghnd);
The lmsgen utility converts text-based message files (.msg) into binary format (.msb) so that Oracle messages and OCI messages provided by the user can be returned to OCI functions in the desired language.
lmsgen text_file product facility [language]
where:
text_file is a message text file.product is the name of the product.facility is the name of the facility.language is the optional message language corresponding to the language specified in the NLS_LANG parameter. The language parameter is required if the message file is not tagged properly with language./" and "//" are treated as internal comments and are ignored.# CHARACTER_SET_NAME= Japanese_Japan.JA16EUC
message_number, warning_level, message_text
The following is an example of an Oracle message text file:
/ Copyright (c) 2001 by the Oracle Corporation. All rights reserved. / This is a test us7ascii message file # CHARACTER_SET_NAME= american_america.us7ascii / 00000, 00000, "Export terminated unsuccessfully\n" 00003, 00000, "no storage definition found for segment(%lu, %lu)"
The following table contains sample values for the lmsgen parameters:
| Parameter | Value |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
The text message file is found in the following location:
$HOME/myApp/mesg/impus.msg
One of the lines in the text message file is:
00128,2, "Duplicate entry %s found in %s"
The lmsgen utility converts the text message file (impus.msg) into binary format, resulting in a file called impus.msb:
% lmsgen impus.msg $HOME/myApplication imp AMERICAN
The following output results:
Generating message file impus.msg --> /home/scott/myApplication/mesg/impus.msb NLS Binary Message File Generation Utility: Version 9.2.0.0.0 -Production Copyright (c) Oracle Corporation 1979, 2001. All rights reserved. CORE 9.2.0.0.0 Production
|
 Copyright © 1996, 2003 Oracle Corporation All Rights Reserved. |
|