Release 8.0
A58234-01
 Library |
 Product |
 Contents |
 Index |
| Oracle Call Interface Programmer's Guide, Volumes 1 & 2 Release 8.0 A58234-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.
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.
This 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:
This chapter is broken down into the following major sections:
The general goal of an OCI application is to connect to an Oracle server, engage in some sort of data exchange, and perform necessary 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 illustrates the flow of steps in an OCI application. Each step is described in more detail in the section "OCI Programming Steps" on page 2-16.
Keep in mind that the above diagram and the list of steps on page 2-3 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 (e.g., managing multiple transactions, using objects, etc.) will require additional steps.
Once the OCI process is initialized, an application may choose to create multiple environments, as illustrated in the following figure:
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 8.
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" on page 2-12.
Almost all Oracle8 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" on page 2-11.
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 and define 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 illustrates the relationship between the various types of handles.
All user-allocated handles, except the environment handle, must be allocated using the OCI handle allocation call, OCIHandleAlloc(). The environment handle is allocated and initialized with a call to OCIEnvInit(), which is required by all OCI applications.
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 first example program in Appendix D.
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. You must allocate and initialize the service context handle with OCIHandleAlloc() or OCILogon() before you can use it.
The service context contains three additional handles that represent a server connection, a user session, and a transaction, as illustrated in 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.
Applications maintaining only a single user session per database connection at any time can call OCILogon() to allocate the service context and its associated handles.
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, as well, or it 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" on page 7-3.
For more information about establishing a server connection and user session, see the sections "Initialization, Connection, and Session Creation" on page 2-17, and "User Authentication and Password Management" on page 7-11.
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.
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 Oracle8 server. This handle contains COR descriptors, which provide instructions to the OCI 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" on page 8-21.
For information about the security handle, and about using OCI calls to write Oracle Security Services applications, refer to the Oracle Security Server Guide.
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 transaction 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);
The next set of statements demonstrates the use of OCIAttrGet() to read the function code of the last OCI function processed on a handle (in this case a bind handle):
ub4 fcode = 0; OCIBind *mybndp; err = OCIAttrGet( (dvoid*) mybndp, OCI_HTYPE_BIND, (dvoid*) &fcode, (ub4) 0, OCI_ATTR_FNCODE,(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.
For a list of all handle attributes, refer to Appendix B, "Handle and Descriptor Attributes".
See Also: See the description of OCIAttrGet() on page 13 - 11 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 an amount of user memory which should be allocated along with that handle.
Typically, an application uses this parameter to allocate an application-defined structure that has the same lifetime as the handle. 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 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 specific data-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" on page 4-7.
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 example, 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 the section "LOB and FILE Operations" on page 7-24.
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" on page 4-8 for more information about obtaining and using parameter descriptors.
The ROWID descriptor is used by applications that need to retrieve and use Oracle ROWIDs. The size and structure of the ROWID has changed from Oracle7 to Oracle8, and is opaque to the user. To work with a ROWID using the Oracle8 OCI, an application can define a ROWID descriptor for a 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.
For information about the complex object descriptor and its use, refer to "Complex Object Retrieval" on page 8-21.
For information about advanced queueing and its related descriptors, refer to "OCI and Advanced Queueing" on page 7-40.
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 OCIEnvInit() call has 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" on page 7-16.
Special considerations for operations involving arrays of structures are described in the section "Arrays of Structures" on page 5-17.
Refer to the section "Error Handling" on page 2-25 for an outline of the steps involved in processing a SQL statement within an OCI program.
For information on using the OCI to write multithreaded applications, refer to "Thread Safety" on page 7-13.
For more information about types of SQL statements, refer to the section "SQL Statements" on page 1-4.
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 Oracle8 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.
The initialize process call, OCIInitialize(), must be invoked before any other OCI call. The mode parameter of this call specifies whether the application will run in a threaded environment (mode = OCI_THREADED), and whether or not it will use objects (mode = OCI_OBJECT). 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 neither of these features (mode = OCI_DEFAULT) or both, separating the options with a vertical bar (mode = (OCI_THREADED | OCI_OBJECT)).
The OCIInitialize() call can also specify user-defined memory management functions.
See Also: See the description of OCIInitialize() on for more information about the call.
For information about using the OCI to write multithreaded applications, refer to "Thread Safety" on page 7-13.
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 allocates the handles for you (e.g. OCIBindByPos()).
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.
Once OCIInitialize() has been called, an application must call OCIEnvInit() 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.
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, "oracle8", 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:
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 13. Refer to Chapter 7, "OCI Programming Advanced Topics", for more information about maintaining multiple sessions, transactions, and connections.
The following example demonstrates the use of the OCI initialization calls. 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 */ (void) OCIInitialize (OCI_THREADED | OCI_OBJECT, (dvoid *)0, mymalloc, myrealloc, myfree); /* initialize the mode to be the threaded and object environment */ (void) OCIEnvInit (&myenvhp, OCI_DEFAULT, 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 */
This section presents one possible scenario for an application which is managing multiple user, multiple server connections, and multithreading. This example is intended to help the reader understand some of the issues involved in programming such an application.
An application is supporting two users, User1 and User2. The application has completed the following steps:
User1 performs the following actions:
User2 performs the following actions:
The following questions and answers relate to the above scenario:
Q1. How many server handles are required?
A1. Even though DB1 and DB2 reside on the same server machine, 2 server handles are required. Each server handle represents a database connection, and is identified by its own connect string.
Q2. How many service context handles are required?
A2. Four service context handles are required. Each user is executing two transactions simultaneously, so each requires its own service context. 2 users x 2 transactions = 4 service context handles. If each user had executed the statements in the same transaction, each would require only a single service context.
Q3. How many user session handles are required?
A3. Four user session handles are required. Each user needs a user session handle on each server. If each user executed their statements serially, then two sessions would be sufficient.
Q4. How many transaction handles are required?
A4. Four transaction handles are required; one for each concurrent transaction. However, the application could also take advantage of the implicit transaction created when database changes are made, and avoid allocating transaction handles altogether.
Q5. Could the example use multiple environment handles?
A5. Yes. Since there are two databases involved, the application should use two environment handles so that accesses to each database can be completely concurrent.
Q6. If a single user in a single environment wants to execute four different statements on 4 transactions concurrently against the same database, how many server handles are required?
A6. Four server handles are required; one for each concurrent transaction. There can be at most a single outstanding call on any one server handle at a time.
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.
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" on page 2-8, and the section "Transactions" on page 7-3.
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 D, "Code Examples".
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 below in Table 2-3, which indicate the success or failure of the call (e.g., OCI_SUCCESS or OCI_ERROR) or provide other information that may be required by the application (e.g., OCI_NEED_DATA or OCI_STILL_EXECUTING). Most OCI calls return one of these codes. For exceptions, see "Functions Returning Other Values" on page 2-27.
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 error 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, taken from the first sample program in Appendix D, "Code Examples", 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; } }
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 13, "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" on page 2-29 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, Oracle8 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 the "Output" section below. 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:
| 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:
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 12, "Using the Object Type Translator", and the section "Nullness" on page 8-28 of this manual for information about null indicator structures.
See the descriptions of OCIBindByName() and OCIBindByPos() in Chapter 13, and the sections "Additional Information for Named Data Type and REF Binds" on page 10-3, and "Additional Information for Named Data Type and REF Defines, and PL/SQL OUT Binds" on page 10-5, 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.
You can use the binary 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.
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" on page A-7.
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.
