Release 8.1.5
A67846-01
 Library |
 Product |
 Contents |
 Index |
| Oracle Call Interface Programmer's Guide Release 8.1.5 A67846-01 |
|
This chapter introduces you to the basic concepts involved in programming with the Oracle Call Interface. This chapter covers the following 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. More specifically, after reading this chapter you may want to continue with any or all of the following:
See Also: For a a discussion of the OCI functions that apply to an NLS environment, see the Oracle8i National Language Support Guide. For a discussion of the OCI functions that apply to cartridge services, see the Oracle8i Data Cartridge Developer's Guide.
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.
While some flexibility exists in the order in which specific tasks can be performed, every OCI application needs to accomplish particular steps. The 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".
Keep in mind that the previous 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, as illustrated in Figure 2-2, "Multiple Environments Within an OCI Process". If an environment requires any process-level initialization then it is performed automatically.
Note: In previous releases, a separate explicit process-level initialization was required. This requirement has been simplified and no explicit process-level initialization is required.
Note: It is possible to have more than one active connection and statement in an OCI application.
See Also: For information about accessing and manipulating objects, see Chapter 10, "OCI Object-Relational Programming".
Handles and descriptors are opaque data structures which are defined in OCI applications and may be allocated directly, through specific allocate calls, or may be implicitly allocated by other OCI functions.
7.x Upgrade Note: Programmers who have previously written 7.x OCI applications will 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 following section. Descriptors are discussed in the section "Descriptors and Locators".
Almost all Oracle 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. A handle may be used to store context or connection information, (e.g., an environment or service context handle), or it may store information about other OCI functions or data (e.g., an error or describe handle). Handles can make programming easier, because the library, rather than the application, maintains this data.
Most OCI applications will need to access the information stored in handles. The get and set attribute OCI calls, OCIAttrGet() and OCIAttrSet(), access this information.
See Also: For more information about using handle attributes, see the section "Handle Attributes".
The following table 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 particular environment handle. You pass the environment handle as one of the parameters to the handle allocation call. The allocated handles 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-3, "Hierarchy of Handles:" illustrates the relationship between the various types of handles.
All user-allocated handles are allocated using the OCI handle allocation call, OCIHandleAlloc().
Note: The environment handle is allocated and initialized with a call to OCIEnvInit(), which is required by all OCI applications.
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 handles.
Note: When a parent handle is freed, all child handles associated with it are also freed, and may no longer be used. For example, when a statement handle is freed, any bind and define handles associated with it are also freed.
Handles obviate 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 various handle types are described in more detail in the following sections.
The environment handle defines a context in which all OCI functions are invoked. Each environment handle contains a memory cache, which allows for fast memory management in a threaded environment where each thread has its own environment. When multiple threads share a single environment, they may block on access to the cache.
The environment handle is passed as the parenth parameter to the OCIHandleAlloc() call to allocate all other handle types, except for the bind and define handles.
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.
A service context handle defines attributes that determine the operational context for OCI calls to a server. The service context contains three handles that represent a server connection, a user session, and a transaction. These attributes are illustrated in Figure 2-4, "Components of a Service Context"the following figure.
Breaking the service context down in this way provides scalability and enables programmers to create sophisticated three-tiered applications and transaction processing (TP) monitors to 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 per 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 handle and user session handle must be explicitly set into the service context by calling OCIServerAttach() and OCISessionBegin(), respectively.
An application may need to define a transaction explicitly if it is a global transaction or there are multiple transactions active for sessions. It also may be able to work with the implicit transaction created when the application makes changes to the database.
See Also: For more information about transactions, see the section "Transactions". For more information about establishing a server connection and user session, see the sections "Initialization, Connection, and Session Creation", and "Password and Session Management"
A statement handle is the context that identifies a SQL or PL/SQL statement and its associated attributes.
Information about input 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 is converted and stored 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 statement is prepared on the statement handle.
Statement context data, the data associated with a statement handle, can be shared. For information about OCI shared mode, see "Shared Data Mode".
The describe handle is used by the OCI describe call, OCIDescribeAny(). This call obtains information about schema objects in a database (e.g., 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: See Chapter 6, "Describing Schema Metadata", for more information about using the OCIDescribeAny() function.
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 about retrieving objects referenced by another object.
See Also: For information about complex object retrieval and the complex object retrieval handle, refer to "Complex Object Retrieval".
For information about the thread handle, refer to "The OCIThread Package".
The subscription handle is used by an OCI client application that is interested in registering for subscriptions 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.
See Also: For information about publish-subscribe and allocating the subscription handle, refer to "Publish-Subscribe Notification".
The direct path handles are necessary for an OCI application that utilizes the direct path load engine in the Oracle database server. The direct path load interface allows the application to access the direct block formatter of the Oracle server.
See Also: For information about direct path loading and allocating the direct path handles, refer to "Direct Path Loading" . For information about the handle attributes, refer to "Direct Path Loading Handle Attributes" .
The process handle is a specialized handle for OCI applications that utilize shared data structures mode to set global parameters. See "Shared Data Mode".
All OCI handles have attributes associated with them. These attributes 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 username in the session handle by writing to the OCI_ATTR_USERNAME attribute:
text username[] = "scott"; err = OCIAttrSet ((dvoid*) mysessp, OCI_HTYPE_SESSION, (dvoid*) username, (ub4) strlen(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 username 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);
For a list of all handle attributes, refer to Appendix A, "Handle and Descriptor Attributes".
See Also: See the description of OCIAttrGet() for an example showing the username and password handle attributes being set.
The OCIEnvInit() call, which initializes the environment handle, and the generic handle allocation (OCIHandleAlloc()) and descriptor/locator allocation (OCIDescriptorAlloc()) calls have an xtramem_sz parameter in their parameter list. This parameter is used to specify memory chunk size which is allocated along with that handle for the user.
Typically, an application uses this parameter to allocate an application-defined structure, such as for an application bookkeeping or storing context information, that has the same lifetime as the handle.
Using the xtramem_sz parameter means that the application does not need to explicitly allocate and deallocate memory as each handle is allocated and deallocated. The memory is allocated along with the handle, and freeing the handle frees up the user's data structures as well.
OCI descriptors and locators are opaque data structures that maintain data-specific information. The OCI has six descriptor and locator types. The following table 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.
Note: Although there is a single C type for OCILobLocator, this locator is allocated with a different OCI type constant for internal and external LOBs. The section below on LOB locators discusses this difference.
The main purpose of each descriptor type is listed here, and each descriptor type is described in the following sections:
The snapshot descriptor is an optional parameter to the execute call, OCIStmtExecute(). It indicates that a query is being executed against a particular database snapshot. A database snapshot represents the state of a database at a particular point in time.
You allocate a snapshot descriptor with a call to OCIDescriptorAlloc(), by passing OCI_DTYPE_SNAP as the type parameter.
See Also: For more information about OCIStmtExecute() and database snapshots, see the section "Execution Snapshots".
A LOB (large object) is an Oracle datatype that can hold up to 4 gigabytes of 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). OCI functions do not take actual LOB values as parameters; all OCI calls operate on the LOB locator. This descriptor--OCILobLocator--is also used for operations on FILEs.
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.
Warning: The two LOB locator types are not interchangeable. When binding or defining a BLOB or CLOB, the application must take care that the locator is properly allocated using OCI_DTYPE_LOB. Similarly, when binding or defining a BFILE, the application must be sure to allocate the locator using OCI_DTYPE_FILE.
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.
The LOB locator datatype (OCILobLocator) is not a valid datatype when connected to an Oracle7 Server.
See Also: For more information about OCI LOB operations, see Chapter 7, "LOB and FILE Operations".
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 one descriptor type that is not allocated using OCIDescriptorAlloc(). You can obtain it only as an attribute of a describe, statement, or complex object retrieval handle by specifying the position of the parameter using an OCIParamGet() call.
See Also: See 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. The size and structure of the ROWID has changed from Oracle release 7 to Oracle release 8, and is opaque to the user. To work with a ROWID using the Oracle OCI release 8, 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.
For information about the complex object descriptor and its use, refer to "Complex Object Retrieval".
For information about advanced queueing and its related descriptors, refer to "OCI and Advanced Queuing".
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 up the user's data structures as well.
The OCIHandleAlloc() call has a similar parameter for allocating user memory which will have the same lifetime as the handle.
The OCIEnvCreate() and OCIEnvInit() calls have a similar parameter for allocating user memory which will have the same lifetime as the environment handle.
Each of the steps that you perform in an OCI application is described in greater 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.
Note: For an example showing the use of OCI calls for processing SQL statements, see the first sample program in Appendix D.
The special case of dynamically providing data at run time is described in detail in the section "Run Time Data Allocation and Piecewise Operations".
Special considerations for operations involving arrays of structures are described in the section "Arrays of Structures".
Refer to the section "Error Handling" for an outline of the steps involved in processing a SQL statement within an OCI program.
For information on using the OCI to write multi-threaded applications, refer to "Thread Safety".
For more information about types of SQL statements, refer to the section "SQL Statements".
The following sections describe the steps that are required of a release 8.0 OCI application:
Application-specific processing will also occur in between any and all of the OCI function steps.
7.x Upgrade Note: OCI programmers should take note that OCI programs no longer require an explicit parse step. This means that 8.0 applications must issue an execute command for both DML and DDL statements.
This section describes how to initialize the Oracle OCI environment, establish a connection to a server, and authorize a user to perform actions against a database.
The three main steps in initializing the OCI environment are described in this section:
Additionally, this section describes connection modes for OCI applications.
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. The only exception is when setting a process-level attribute for the OCI shared mode. See "Shared Data Mode".
The mode parameter of OCIEnvCreate() specifies whether the application calling the OCI library functions will run in a threaded environment (mode = OCI_THREADED), whether or not it will use objects (mode = OCI_OBJECT), whether or not it will utilize shared data structures (mode=OCI_SHARED), and whether or not it will utilize subscriptions (mode=OCI_EVENTS). The mode can be set independently in each environment.
Initializing in object mode is necessary if the application will be binding and defining objects, or if the application will be using 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 will run in a threaded environment and use objects.
You can also specify user-defined memory management functions for each OCI environment.
Note: In previous releases, a separate explicit process-level initialization was required. This requirement has been simplified and no explicit process-level initialization is required.
See Also: See the description of OCIEnvCreate() and OCIInitialize() for more information about the initialization calls. For information about using the OCI to write multi-threaded applications, refer to "Thread Safety". For information about OCI programming with objects, refer to Chapter 10, "OCI Object-Relational Programming". For information about using the publish-subscribe feature, see "Publish-Subscribe Notification".
When a SQL statement is processed, certain underlying data is associated with the statement. This data includes information about statement text and bind data, as well as define and describe information for queries. For applications where the same set of SQL statements is executed on multiple instances of the application on the same host, the data can be shared.
When an OCI application is initialized in shared mode, common statement data is shared between multiple statement handles, thus providing memory savings for the application. This savings may be particularly valuable for applications which create multiple statement handles which execute the same SQL statement on different users' sessions but in the same schema, either on the same or multiple connections.
Without the shared mode feature, each execution of the query using an OCI statement handle would require its own memory for storing the metadata. The total amount of memory required would be roughly equal to the number of statements being executed in all the processes combined multiplied by the memory required for each statement handle. Because a large part of the common memory in a statement handle is shared among all the processes executing the same statement with the shared mode feature, the total amount of memory in all the processes combined would be much less than in the previous case for the same number of processes. The memory requirement per statement handle would be much smaller than in the case where there is no sharing, as the number of such statements increases to a large number.
Shared data structure mode might be useful in the following scenarios:
Note: Small applications, which execute single queries non-concurrently will not benefit from this feature.
There are several ways to use the shared OCI functionality. Existing applications can quickly examine the benefits of this feature without changing any code. These applications can trigger OCI shared mode by setting environment variables. New applications should use OCI API calls to trigger shared mode functionality.
To trigger OCI shared mode functionality, process handle parameters must be set and OCIInitialize() must be called with the mode flag set to OCI_SHARED. For example:
ub4 mode = OCI_SHARED | OCI_THREADED; OCIInitialize (mode, 0, 0, 0, 0);
The first application that initializes OCI in shared mode starts up the shared subsystem using the parameters set by that OCI application. When subsequent applications initialize using the shared mode, they use the previously started shared subsystem. For information on the parameters that can be set and read for the OCI shared mode system, see "Process Handle Attributes".
If an OCI application has been initialized in shared mode, all statements that are prepared and executed use the shared subsystem by default. If you do not want to use the shared subsystem to execute a specific SQL statement, then you can use the OCI_NO_SHARING flag in OCIStmtPrepare(). For example:
OCIStmtPrepare(stmthp, (CONST text *)createstmt, (ub4)strlen((char *)updstmt), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_NO_SHARING);
The OCI_NO_SHARING flag has no effect if the process has not been initialized in the shared mode. See OCIStmtPrepare().
To detach a process from the shared memory subsystem, use the OCITerminate() call. See OCITerminate().
The environmental variables OCI_SHARED_MODE and OCI_NUM_SHARED_PROCS can be used to set OCI shared mode functionality. However, this is not the recommended method. This procedure has been provided to quickly examine the benefits of using shared mode functionality in existing applications.
To trigger an OCI application to run in shared mode, set the environment variable OCI_SHARED_MODE before executing a OCI program. To set the variable, issue the command:
setenv OCI_SHARED_MODE number
where number is the size of the shared memory address space. For example:
setenv OCI_SHARED_MODE 20000000
If the shared subsystem is not already running, setting this variable launches the subsystem by creating a shared memory address space with the size specified. The size of the shared memory required is determined by the nature of the application and depends on the size and type of the SQL statement and the underlying table(s) that it accesses.
To set the maximum number of processes that can connect to the shared subsystem, set the environment variable ORA_OCI_NUM_SHARED_PROCS. To set this variable, issue the command:
setenv OCI_NUM_SHARED_PROCS number
where number is the maximum number of processes. For example:
setenv OCI_NUM_SHARED_PROCS 20
ORA_OCI_NUM_SHARED_PROCS is an initialization parameter for starting the shared subsystem. It has no effect if the shared subsystem is already running.
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 following types of handles with OCIHandleAlloc():
Depending on the functionality of your application, it will need 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: OCIEnvCreate() should be used instead of the OCIInitialize() and OCIEnvInit() calls. OCIInitialize() and OCIEnvInit() calls will be supported for backward compatibility.
This option is the simplified logon method.
If an application will maintain only a single user session per database connection at any time, the application can take advantage of the OCI's simplified logon procedure.
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 whose username and password are passed to the function.
The following is an example of what a call to OCILogon() might look like:
OCILogon(envhp, errhp, &svchp, "scott", nameLen, "tiger", passwdLen, "oracledb", dbnameLen);
The parameters to this call include the service context handle (which will be initialized), the username, the user's password, and the name of the database that will be used to establish the connection. The server and user session handles are also 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, which means that the application cannot switch session or transaction by changing the appropriate attributes of the service context handle, using OCIAttrSet().
An application that creates its session and authorization using OCILogon() should terminate them using OCILogoff().
This option uses explicit attach and begin session calls.
If an application needs to maintain multiple user sessions on a database connection, the application requires a different set of calls to set up the sessions and connections. This includes specific calls to attach to the server and begin sessions:
Note: See "Non-Blocking Mode" for information about specifying a blocking or non-blocking connection in the OCIServerAttach() call.
These calls set up an operational environment that allows you to execute SQL and PL/SQL statements against a database. The database must be up and running before the calls are made, or else they will fail.
These calls are described in more detail in Chapter 15, "OCI Relational Functions". Refer to Chapter 9, "OCI Programming Advanced Topics", for more information about maintaining multiple sessions, transactions, and connections.
The following example demonstrates the use of creating and initializing an OCI environment. In the example, a server context is created and set in the service handle. Then a user session handle is created and initialized using a database username and password. For the sake of simplicity, error checking is not included.
main() { OCIEnv *myenvhp; /* the environment handle */ OCIServer *mysrvhp; /* the server handle */ OCIError *myerrhp; /* the error handle */ OCISession *myusrhp; /* user session handle */ (/* initialize the mode to be the threaded and object environment */ ((void) OCIEnvCreate(&myenvhp, OCI_THREADED|OCI_OBJECT, (dvoid *)0, mymalloc, myrealloc, myfree, 0, (dvoid **)0) (void) OCIHandleAlloc ((dvoid *)myenvhp, (dvoid **)&mysrvhp, OCI_HTYPE_SVR, 0, (dvoid **) 0); /* allocate a server handle */ (void) OCIHandleAlloc ((dvoid *)myenvhp, (dvoid **)&myerrhp, OCI_HTYPE_ERROR, 0, (dvoid **) 0); /* allocate an error handle */ (void) OCIServerAttach (mysrvhp, myerrhp, (text *)"inst1_alias", strlen ("inst1_alias"), OCI_DEFAULT); /* create a server context */ (void) OCIAttrSet ((dvoid *)mysvchp, OCI_HTYPE_SVCCTX, (dvoid *)mysrvhp, (ub4) 0, OCI_ATTR_SERVER, myerrhp); /* set the server context in the service context */ (void) OCIHandleAlloc ((dvoid *)myenvhp, (dvoid **)&myusrhp, OCI_HTYPE_SESSION, 0, (dvoid **), 0); /* allocate a user session handle */ (void) OCIAttrSet ((dvoid *)myusrhp, OCI_HTYPE_SESSION, (dvoid *)"scott", (ub4)strlen("scott"), OCI_ATTR_USERNAME, myerrhp); /* set username attribute in user session handle */ (void) OCIAttrSet ((dvoid *)myusrhp, OCI_HTYPE_SESSION, (dvoid *)"tiger", (ub4)strlen("tiger"), OCI_ATTR_PASSWORD, myerrhp); /* set password attribute in user session handle */ (void) OCISessionBegin ((dvoid *) mysvchp, myerrhp, myusrhp, OCI_CRED_RDBMS, OCI_DEFAULT); (void) OCIAttrSet ( (dvoid *)mysvchp, OCI_HTYPE_SVCCTX, (dvoid *)myusrhp, (ub4) 0, OCI_ATTR_SESSION, myerrhp); /* set the user session in the service context */
For information about processing SQL statements, refer to Chapter 4, "SQL Statement Processing".
An application commits changes to the database by calling OCITransCommit(). This call takes a service context as one of its parameters. The transaction currently associated with the service context is the one whose changes are committed. This may be a transaction explicitly created by the application or the implicit transaction created when the application modifies the database.
Note: Using the OCI_COMMIT_ON_SUCCESS mode of the OCIExecute() call, the application can selectively commit transactions at the end of each statement execution, saving an extra roundtrip.
If you want to roll back a transaction, use the OCITransRollback() call.
If an application disconnects from Oracle in some way other than a normal logoff (for example, losing a network connection), and OCITransCommit() has not been called, all active transactions are rolled back automatically.
See Also: For more information about implicit transactions and transaction processing, see the section "Service Context and Associated Handles", and the section "Transactions".
An OCI application should perform the following three steps before it terminates:
Note: When a parent OCI handle is freed, any child handles associated with it are freed automatically.
The calls to OCIServerDetach() and OCISessionEnd() are not mandatory. If the application terminates, and OCITransCommit() (transaction commit) has not been called, any pending transactions are automatically rolled back
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".
Note: If the application has used the simplified logon method provided by OCILogon(), then a call to OCILogoff() will terminate the session, disconnect from the server, and free the service context and associated handles. The application is still responsible for freeing other handles it has allocated.
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. For exceptions, see "Functions Returning Other Values".
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.
Note: Multiple diagnostic records can be retrieved by calling OCIErrorGet() repeatedly until there are no more records (OCI_NO_DATA is returned). OCIErrorGet() returns at most a single diagnostic record at any time.
The following example code returns error information given an error handle and the return code from an OCI function call. If the return code is OCI_ERROR, the function prints out diagnostic information. OCI_SUCCESS results in no printout, and other return codes print the return code information.
STATICF void checkerr(errhp, status) OCIError *errhp; sword status; { text errbuf[512]; ub4 buflen; ub4 errcode; switch (status) { case OCI_SUCCESS: break; case OCI_SUCCESS_WITH_INFO: (void) printf("Error - OCI_SUCCESS_WITH_INFO\n"); break; case OCI_NEED_DATA: (void) printf("Error - OCI_NEED_DATA\n"); break; case OCI_NO_DATA: (void) printf("Error - OCI_NODATA\n"); break; case OCI_ERROR: (void) OCIErrorGet (errhp, (ub4) 1, (text *) NULL, &errcode, errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR); (void) printf("Error - %s\n", errbuf); break; case OCI_INVALID_HANDLE: (void) printf("Error - OCI_INVALID_HANDLE\n"); break; case OCI_STILL_EXECUTING: (void) printf("Error - OCI_STILL_EXECUTE\n"); break; default: break; } }
In Table 2-4, Table 2-5, and Table 2-6, the OCI return code, Oracle error number, indicator variable, and column return code are specified when the data fetched is null or truncated.
Table 2-4 Normal Data - Not Null and Not Truncated
In Table 2-6, 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 sure to take into account that they return a value directly from the function call, rather than through an OUT parameter. More detailed information about each function and its return values is listed in Volume II.
This section explains some additional factors to keep in mind when coding applications using the Oracle Call Interface.
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.
For more information about parameter datatypes and parameter passing conventions, refer to the introductory section in Chapter 15, "OCI Relational Functions", which covers the function calls for the OCI.
Address parameters pass the address of the variable to Oracle. You should be careful when developing in C, which normally passes scalar parameters by value, to make sure that the parameter is an address. In all cases, you should pass your pointers carefully.
Binary integer parameters are numbers whose size is system dependent. Short binary integer parameters are smaller numbers whose size is also 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. This section describes additional rules that apply to character string address parameters.
Each OCI routine that allows 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, in release 8.0 you should 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. One method is to use a literal NULL in the text of an INSERT or UPDATE statement. For example, the SQL statement
INSERT INTO emp (ename, empno, deptno) VALUES (NULL, 8010, 20)
makes the ENAME column null.
Another method is to use indicator variables in the OCI bind call. See the section "Indicator Variables" for more information.
One other method to insert a NULL is to set the buffer length and maximum length parameters both to zero on a bind call.
Note: Following SQL92 requirements, Oracle returns an error if an attempt is made to fetch a null select-list item into a variable that does not have an associated indicator variable specified in the define call.
Each bind and define OCI call has a parameter that allows you to associate an indicator variable, or an array of indicator variables if you are using arrays, with a DML statement, PL/SQL statement, or query.
Host languages do 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 (on OCIStmtFetch()) or a truncation (on OCIStmtExecute() or OCIStmtFetch()), the OCI call returns OCI_SUCCESS_WITH_INFO. The corresponding indicator variable is set to the appropriate value, as listed in Table 2-8, "Output Indicator Values". If the application provided a return code variable in the corresponding 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-7 Input Indicator Values
| Input Indicator Value | Action Taken by Oracle |
|---|---|
|
-1 |
Oracle assigns a NULL to the column, ignoring the value of the input variable. |
|
>=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-8 Output Indicator Values
Indicator variables for most new (release 8.0) datatypes function as described above. The only exception is SQLT_NTY (a named datatype). Data of type SQLT_REF uses a standard scalar indicator, just like other variable types. For data of type SQLT_NTY, the indicator variable must be a pointer to an indicator structure.
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: See the documentation for the OTT in Chapter 14, "Using the Object Type Translator", and the section "Nullness" of this manual for information about null indicator structures.
See the descriptions of OCIBindByName() and OCIBindByPos() in Chapter 15, and the sections "Information for Named Datatype and REF Binds", and "Information for Named Datatype and REF Defines, and PL/SQL OUT Binds", for more information about setting indicator parameters for named datatypes and REFs.
On most platforms, you can cancel a long-running or repeated OCI call. You do this 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 OCIStmtFetch() with the nrows parameter set to zero.
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) abort of any currently executing OCI function that is 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 non-blocking connection after an OCI application aborts a function with OCIBreak().
The status of potentially long-running calls can be monitored through the use of non-blocking calls. See the section "Non-Blocking Mode" for more information.
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 emp 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() */ err = 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 MY_ROWID is the buffer in which the row identifier has been saved, you can later process a SQL statement such as
UPDATE emp SET sal = :1 WHERE rowid = :2
by binding the new salary to the :1 placeholder and MY_ROWID to the :2 placeholder. Be sure to use datatype code 104 (ROWID descriptor) when binding MY_ROWID to :2.
Using prefetching, an array of ROWIDs can be selected for use in subsequent batch updates. For more information on ROWIDs, see "Universal ROWID" and "ROWID".
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. To view the lists of the Oracle keywords or reserved words for SQL and PL/SQL, see the Oracle8i SQL Reference and the PL/SQL User's Guide and Reference.
Table 2-9, "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 example, the SQL*Net Transparent Network Service functions all begin with the characters NS, so you need to avoid naming functions that begin with NS.
The list in Table 2-9, "Oracle Reserved Namespaces" is not a comprehensive list of all functions within the Oracle reserved namespaces. For a complete list of functions within a particular namespace, refer to the document that corresponds to the appropriate Oracle library.
When creating a user function in an OCI program, do not start the function name with OCI to avoid possible conflicts with the OCI functions.
For information about application linking modes, including Oracle support for non-deferred linking and single task linking in various versions of the OCI, please refer to "Application Linking Issues".
The Oracle OCI provides the ability to establish a server connection in blocking mode or non-blocking 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 non-blocking 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. The two modes are illustrated in Figure 2-7.
In non-blocking mode, an application must test the return code of each OCI function to see if it returns OCI_STILL_EXECUTING. In this case, the OCI client can continue to process program logic while waiting to retry the OCI call to the server.
The non-blocking mode returns control to an OCI program once a call has been made so that it may perform other computations while the OCI call is being processed by the server. This mode is particularly useful in Graphical User Interface (GUI) applications, real-time applications, and in distributed environments.
The non-blocking 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. The client application must check whether the pending call has finished at the server by executing the call again with the exact same parameters.
Note: While waiting to retry non-blocking 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 OCIBreak() and OCIReset(). See "Cancelling a Non-blocking Call" for more information on these calls.
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. See 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. After issuing an OCIBreak() while an OCI call is in progress, you must issue an OCIReset() call to reset the asynchronous operation and protocol.
The following code is an example of non-blocking 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"); 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 processes tasks that are more complicated than simple queries and SQL data manipulation language (DML) statements. PL/SQL allows you to group a number of constructs into a single block and execute them as a unit. These constructs include:
You can use PL/SQL blocks in your OCI program to perform the following operations:
Note: While the OCI can only directly process anonymous blocks, and not named packages or procedures, the user can always put the package or procedure call within an anonymous block and process that block.
Warning: When writing PL/SQL code, it is important to keep in mind that the parser treats everything that starts with "--" to a carriage return as a comment. So if comments are indicated on each line by "--", the C compiler can concatenate all lines in a PL/SQL block into a single line without putting a carriage return "/n" for each line. In this particular case, the parser fails to extract the PL/SQL code of a line if the previous line ends with a comment. To avoid the problem, the programmer should put "/n" after each "--" comment to make sure the comment ends there.
See the PL/SQL User's Guide and Reference for information about coding PL/SQL blocks.
