Skip Headers

Oracle Call Interface Programmer's Guide
Release 2 (9.2)

Part Number A96584-01
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to next page

OCI Programming Basics

This chapter introduces you to the basic concepts involved in programming with the OCI. This chapter covers the following topics:

Overview of OCI Programming

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:

OCI Program Structure

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.

The OCI uses the following basic program structure:

  1. Initialize the OCI programming environment and threads.
  2. Allocate necessary handles, and establish server connections and user sessions.
  3. Exchange data with the database server by executing SQL statements on the server, and perform necessary application data processing.
  4. Reexecute prepared statements, or prepare a new statement for execution.
  5. Terminate user sessions and server connections.
  6. Free handles.

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

Figure 2-1 Basic OCI Program Flow

Text description of lnoci017.gif follows
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, 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.


In previous releases, a separate explicit process-level initialization was required. This requirement has been simplified and no explicit process-level initialization is required.

Figure 2-2 Multiple Environments Within an OCI Process

Text description of lnoci033.gif follows
Text description of the illustration lnoci033.gif


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" and the chapters that follow it

OCI Data Structures

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 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 following section. Descriptors are discussed in the section "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 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.

Table 2-1 OCI Handle Types  
Description C Type Handle Type

OCI environment handle



OCI error handle



OCI service context handle



OCI statement handle



OCI bind handle



OCI define handle



OCI describe handle



OCI server handle



OCI user session handle



OCI authentication information handle



OCI connection pool handle



OCI session pool handle



OCI transaction handle



OCI complex object retrieval (COR) handle



OCI thread handle



OCI subscription handle



OCI direct path context handle



OCI direct path function context handle



OCI direct path column array handle



OCI direct path stream handle



OCI process handle


Allocating and Freeing Handles

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.


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().


The environment handle is allocated and initialized with a call to OCIEnvCreate(), 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.


When a parent handle is freed, all child handles associated with it are also freed, and can no longer be used. For example, when a statement handle is freed, any bind and define handles associated with it are also freed.

Figure 2-3 Hierarchy of Handles

Text description of lnoci038.gif follows
Text description of the illustration lnoci038.gif

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 various handle types are described in more detail in the following sections.

Environment Handle

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

Error Handle

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.

Service Context and Associated Handles

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-4, "Components of a Service Context":

Figure 2-4 Components of a Service Context

Text description of lnoci019.gif follows
Text description of the illustration lnoci019.gif

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 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 handle and user session handle 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:

Statement Handle, Bind Handle, and Define Handle

A statement handle is the context that identifies a SQL or PL/SQL statement and its associated attributes.

Figure 2-5 Statement Handles

Text description of lnoci041.gif follows
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.

Statement context data, the data associated with a statement handle, can be shared.

See Also:

For information about OCI shared mode, see "Shared Data Mode"

Describe 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 OCIDescribeAny() function

Complex Object Retrieval Handle

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"

Thread Handle

For information about the thread handle, which is used in multithreaded applications, refer to "The OCIThread Package".

Subscription Handle

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"

Direct Path Handles

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.

Figure 2-6 Direct Path Handles

Text description of lnoci042.gif follows
Text description of the illustration lnoci042.gif

See Also:

Process Handle

The process handle is a specialized handle for OCI applications that utilize shared data structures mode to set global parameters.

See Also:

"Shared Data Mode"

Connection Pool Handle

The connection pool handle is used for applications that pool physical connections into virtual connections, by calling specific OCI functions.

See Also:

"Connection Pooling"

Handle Attributes

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

See Also:

User Memory Allocation

The OCIEnvCreate() call, which initializes the environment handle, and the generic handle allocation (OCIHandleAlloc()) and descriptor 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. This memory is not used by OCI and is for use by the application only.

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

Table 2-2 Descriptor Types  
Description C Type OCI Type Constant

snapshot descriptor



LOB datatype locator



FILE datatype locator



read-only parameter descriptor



ROWID descriptor



ANSI DATE descriptor



TIMESTAMP descriptor















complex object descriptor



advanced queuing enqueue options



advanced queuing dequeue options



advanced queuing message properties



advanced queuing agent



advanced queuing notification



the distinguished names of the database servers in a registration request




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:

Snapshot Descriptor

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"

LOB/FILE Datatype Locator

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). OCILob* functions take the LOB locator as a parameter instead of the LOB value. OCI LOB functions do not take actual LOB data as parameters. These functions take the LOB locators as parameters and operate on the LOB data referenced by these locators.

Hence, the old long interface can operate on the actual LOB value. 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.


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"

Parameter Descriptor

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

ROWID Descriptor

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

Datetime and Interval Descriptors

These descriptors are used by applications which use the datetime or interval datatypes (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:

For more information about these datatypes refer to Chapter 3, "Datatypes". The functions which operate on these datatypes are listed in Chapter 18, "OCI Datatype Mapping and Manipulation Functions"


The functions which operate on OCIDateTime and OCIInterval datatypes also work on the OCIDate datatype

Complex Object Descriptor

For information about the complex object descriptor and its use, refer to "Complex Object Retrieval".

Advanced Queuing Descriptors

For information about Advanced Queuing and its related descriptors, refer to "OCI and Advanced Queuing".

LDAP-based Publish-Subscribe Notification

For information about LDAP-based publish-subscribe notification, see "Publish-Subscribe Registration Functions".

User Memory Allocation

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

OCI Programming Steps

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.


For an example showing the use of OCI calls for processing SQL statements, see the first sample program in Appendix B, "OCI Demonstration Programs"

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.

7.x Upgrade Note:

OCI programmers should take note that OCI programs no longer require an explicit parse step. This means that 8.0 or later applications must issue an execute command for both DML and DDL statements.

OCI Environment Initialization

This section describes how to initialize the OCI environment, establish a connection to a server, and authorize a user to perform actions against a database.

First, the three main steps in initializing the OCI environment are described in the following sections:

  1. Creating the OCI environment.
  2. Allocating Handles and Descriptors.
  3. Initializing the Application, Connection, and Session.

Creating the OCI Environment

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 Also:

"Shared Data Mode"

The mode parameter of OCIEnvCreate() specifies whether the application calling the OCI library functions will:

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 runs in a threaded environment and use objects.

You can also specify user-defined memory management functions for each OCI environment.


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:

Shared Data Mode

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 requires its own memory for storing the metadata. The total amount of memory required is roughly equal to the number of statements being executed in all the processes combined multiplied by the memory required for each statement handle.

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 is much less than in the previous case for the same number of processes. The memory requirement for each statement handle is 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 can be useful in the following scenarios:

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 be initialized in OCI shared mode by setting environment variables. New applications should use OCI API calls to initialize shared mode functionality.

Using OCI Functions

To initialize OCI shared mode functionality, process handle parameters must be set and OCIEnvCreate() must be called with the mode flag set to OCI_SHARED. For example:

OCIEnvCreate (&envhp, mode, (CONST dvoid *)0, 0, 0, 0, (size_t)0, (dvoid **)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.

See Also:

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,

The OCI_NO_SHARING flag has no effect if the process has not been initialized in the shared mode.

See Also:


To detach a process from the shared memory subsystem, use the OCITerminate() call.

See Also:


Using Environment Variables

The environment 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 lets you to quickly examine the benefits of using shared mode functionality in existing applications.


To initialize an OCI application to run in shared mode, set the environment variable OCI_SHARED_MODE before executing a OCI program. To set the variable in the C-shell under SolarisTM Operating Environment, for example, 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:


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.

Allocating Handles and Descriptors

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 needs to allocate some or all of these handles.

See Also:

the description of OCIHandleAlloc()

Application Initialization, Connection, and Session Creation

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.


OCIEnvCreate() should be used instead of the OCIInitialize() and OCIEnvInit() calls. OCIInitialize() and OCIEnvInit() calls are supported for backward compatibility.

Option 1: Single User, Single Connection

This option is the simplified logon method.

If an application maintains only a single user session for each 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 are initialized), the username, the user's password, and the name of the database that are 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 initializes its session and authorization using OCILogon() should terminate them using OCILogoff().

Option 2: Multiple Sessions or Connections

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

See Also:

These calls are described in more detail in "Connect, Authorize, and Initialize Functions" . Refer to Chapter 9, "OCI Programming Advanced Topics", for more information about maintaining multiple sessions, transactions, and connections.

Example of Creating and initializing an OCI Environment

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.

#include <s.h>
#include <oci.h>
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 username attribute in user session handle */
 (void) OCIAttrSet ((dvoid *)myusrhp, OCI_HTYPE_SESSION,
      (dvoid *)"scott", (ub4)strlen("scott"),
      OCI_ATTR_USERNAME, myerrhp);

      /* set password attribute in user session handle */
 (void) OCIAttrSet ((dvoid *)myusrhp, OCI_HTYPE_SESSION,
      (dvoid *)"tiger", (ub4)strlen("tiger"),
      OCI_ATTR_PASSWORD, myerrhp);

 (void) OCISessionBegin ((dvoid *) mysvchp, myerrhp, myusrhp,

    /* 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.

Processing SQL Statements

For information about processing SQL statements, refer to Chapter 4, "Using SQL Statements in OCI".

Commit or Rollback

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.


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 round-trip.

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 "OCI Support for Transactions"

Terminating the Application

An OCI application should perform the following three steps before it terminates:

  1. Delete the user session by calling OCISessionEnd() for each session.
  2. Delete access to the data source(s) by calling OCIServerDetach() for each source.
  3. Explicitly deallocate all handles by calling OCIHandleFree() for each handle.
  4. Delete the environment handle, which deallocates all other handles associated with it.


    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, 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"


If the application has used the simplified logon method of OCILogon(), then a call to OCILogoff() terminates the session, disconnects from the server, and frees the service context and associated handles. The application is still responsible for freeing other handles it has allocated.

Error Handling

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.

See Also:

For exceptions, see "Functions Returning Other Values"

Table 2-3 OCI Return Codes  
OCI Return Code Description


The function completed successfully.


The function completed successfully; a call to OCIErrorGet() returns additional diagnostic information. This may include warnings.


The function completed, and there is no further data.


The function failed; a call to OCIErrorGet() returns additional information.


An invalid handle was passed as a parameter or a user callback is passed an invalid handle or invalid context. No further diagnostics are available.


The application must provide run-time data.


The service context was established in nonblocking mode, and the current operation could not be completed immediately. The operation must be called again to complete. OCIErrorGet() returns ORA-03123 as the error code.


This code is returned only from a callback function. It indicates that the callback function wants the OCI library to resume its normal processing.

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.


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)
    (void) printf("Error - OCI_SUCCESS_WITH_INFO\n");
    (void) printf("Error - OCI_NEED_DATA\n");
  case OCI_NO_DATA:
    (void) printf("Error - OCI_NODATA\n");
  case OCI_ERROR:
    (void) OCIErrorGet (errhp, (ub4) 1, (text *) NULL, &errcode,
                    errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
    (void) printf("Error - %s\n", errbuf);
    (void) printf("Error - OCI_INVALID_HANDLE\n");
    (void) printf("Error - OCI_STILL_EXECUTE\n");

Return and Error Codes for Truncation and Null Data

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.

See Also:

See "Indicator Variables" for a discussion of indicator variables.

Table 2-4 Normal Data - Not Null and Not Truncated  
Return Code Indicator - not provided Indicator - provided

not provided

error = 0
error = 0
indicator = 0


error = 0
return code = 0
error = 0
indicator = 0
return code = 0
Table 2-5 Null Data  
Return Code Indicator - not provided Indicator - provided

not provided

error = 1405
error = 0
indicator = -1


error = 1405
return code = 1405
error = 0
indicator = -1
return code = 1405
Table 2-6 Truncated Data  
Return Code Indicator - not provided Indicator - provided

not provided

error = 1406
error = 1406
indicator = data_len



error = 24345
return code = 1405

error = 24345
indicator = data_len
return code = 1406

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.

Functions Returning Other Values

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 the reference chapters. Some examples of these functions are:

Additional Coding Guidelines

This section explains some additional factors to keep in mind when coding applications using the Oracle Call Interface.

Parameter Types

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:

For more information about parameter datatypes and parameter passing conventions, refer to "Connect, Authorize, and Initialize Functions".

Address Parameters

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.

Integer Parameters

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 String Parameters

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, you do not pass -1 for the string length parameter of a null-terminated string.

Inserting Nulls into a Column

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 Also:

"Indicator Variables"

One other method to insert a NULL is to set the buffer length and maximum length parameters both to zero on a bind call.


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.

Indicator Variables

Each bind and define OCI call has a parameter that lets 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.

The C languages 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 (on OCIStmtFetch()) or a truncation (on OCIStmtExecute() or OCIStmtFetch()), the OCI call returns OCI_SUCCESS. The corresponding indicator variable is set to the appropriate value, as listed in Table 2-8, "Output Indicator Values". If the application has 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


Oracle assigns a NULL to the column, ignoring the value of the input variable.


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
Output Indicator Value Meaning


The length of the item is greater than the length of the output variable; the item has been truncated. Additionally, the original length is longer than the maximum data length that can be returned in the sb2 indicator variable.


The selected value is null, and the value of the output variable is unchanged.


Oracle assigned an intact value to the host variable.


The length of the item is greater than the length of the output variable; the item has been truncated. The positive value returned in the indicator variable is the actual length before truncation.

Indicator Variables for Named Data Types and REFs

Indicator variables for most new (after 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:

Cancelling Calls

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.


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 nonblocking connection after an OCI application aborts a function with OCIBreak().


OCIBreak() is not yet supported if the server is an NT system.

The status of potentially long-running calls can be monitored through the use of nonblocking calls. See the section "Nonblocking Mode" for more information.

Positioned Updates and Deletes

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 = OCIDescriptorAlloc ((dvoid *) envhp, (dvoid **) &rowid,
     (ub4) OCI_TYPE_ROWID, (size_t) 0, (dvoid **) 0));
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 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 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.

See Also:

For more information on ROWIDs, see "UROWID" and "DATE".

Reserved Words

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 Oracle9i SQL Reference and the PL/SQL User's Guide and Reference

Oracle Reserved Namespaces

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 Oracle Net Transparent Network Service functions all begin with the characters NS, so you need to avoid naming functions that begin with NS.

Table 2-9 Oracle Reserved Namespaces  
Namespace Library


external functions for XA applications only


external SQLLIB functions used by Oracle Precompiler and SQL*Module applications


external OCI functions internal OCI functions


function names from the Oracle UPI layer












Oracle Net Native Services Product

Oracle Net Rpc Project

Oracle Net Directory

Oracle Net Network Library Layer

Oracle Net Management Project

Oracle Net Interchange

Oracle Net Transparent Network Service

Oracle Net Drivers

Oracle Net Security Service

SQL*Net V1

Oracle Net Two Task


Core library functions


function names from the Oracle Globalization Support layer


function names from system-dependent libraries


Kernel Objects

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.

Function Names

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.

Nonblocking Mode

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. In this case, the OCI client can continue to process program logic while waiting to retry the OCI call to the server.

The nonblocking 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 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. The client application must check whether the pending call has finished at the server by executing the call again with the exact same parameters.


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 OCIBreak() and OCIReset().

See "Cancelling a Nonblocking Call" for more information on these calls

Setting Blocking Modes

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 Also:



Only functions that have server context or a service context handle as a parameter may return OCI_STILL_EXECUTING.

Cancelling a Nonblocking Call

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.

Nonblocking Example

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 nonblocking on */ 
  if (retval = OCIAttrSet ((dvoid *) srvhp, (ub4) OCI_HTYPE_SERVER, 
                           (dvoid *) 0, (ub4) 0, 
                           (ub4) OCI_ATTR_NONBLOCKING_MODE, errhp)) 
    printf ("Unable to set nonblocking 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); 
  return (int)OCI_SUCCESS; 

Using PL/SQL in an OCI Program

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 lets 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:

OCI Globalization Support

OCI supports UTF-16 Unicode encoding formats in string-based function calls (for SQL statements, data, metadata such as user and password, object support, in error messages).

UTF-16 is a variable-width Unicode encoding built from 2-byte UCS2. Any character in any language can be represented in UTF-16. AL16UTF16 is the Oracle character set name for UTF-16. UTF-16 is a superset of UCS2.

ASCII and other native character sets are supported as well. Strings in ASCII follow so-called byte length semantics. Unicode strings have character length semantics.

OCI is the interface between users and servers, so describe, insert, update and fetch operations can be aware of codepoint-length semantics.

See Also:

UTF-16 Environment

Whether and how UTF-16 is used in OCI will be determined by setting up OCI environment handles through the mode parameter and using the concept of inheritance of handles. Except for the environment, all handles are stateless for UTF-16 setting. In other words, they do not save UTF-16 information. So users should always rely on the parent environment handle in terms of UTF-16 setting.

See Also:

Users are encouraged to use "OCIEnvNlsCreate()" as a better approach to programming in UTF-16 environment than is OCIEnvCreate(). For more about its usage, see "Client Character Set Control from OCI".

Aside from OCIEnvCreate(), all these functions have one characteristic which is they take text * parameters which might point to UTF-16 buffers. However, some OCI functions, in order to simplify the OCI, require some parameters which are string primitives to be passed down as dvoid * followed by length parameters.

Buffer constraints can be checked against the codepoint length limit, as well as the byte limit. For instance, OCIAttrGet(), which retrieves handle attribute information, asks for attribute name returned out to be cast to dvoid * type, and OCIDescribeAny(), which describes existing schema objects, takes object name passed in as a dvoid * parameter. These dvoid * parameters can come from string buffers, such as names.

For text * parameters, their corresponding length values, no matter whether passed in or out, are always the number of bytes for the whole string, regardless of UTF-16 setting. In other words, the length should always be twice the number of UTF-16 characters in the string.

The only exception to this rule is the group of functions with existing UTF-16 support where the length parameters refer to number of characters.

Top-level Environment Creation

OCIEnvCreate() initializes an environment handle from which other OCI handles and structures get the fundamental information. A user can set UTF-16 encoding for the whole environment through this function call

Relational OCI Functions with Text Input

The following functions communicate with the server, but do not take mode parameters as they do not need to. They choose the mode setting embedded in the environment or statement handles that are passed down to them.

If mixed programming for both UTF-16 and non-UTF-16 buffers is inevitable for an application, it is advised to create two separated environment handles each dealing with one encoding respectively, because most relational OCI and object OCI calls take only one environment handle at a time. For any functions taking more than text parameter, these string buffers should always be prepared and treated in the same encoding. Mixed encoding for different string parameters in one function call is invalid.

Relational OCI Functions with Text Output

The same advice to users as for the functions with text input applies to these functions:

OCI String Functions with UTF-16 Data

As a scalar type OCIString, the encoding of the buffer depends on the environment handle it belongs to. Being treated as a datatype SQLT_VST, OCIString behaves in the same way as normal string types for bind and define handles.

If the environment handle is created with OCI_UTF16 mode, the data in OCIString should be in UTF-16 encoding. Otherwise, the data is in NLS_LANG encoding. The default character set in the corresponding bind or define handles are OCI_UTF16ID, which means UTF-16. The size parameters are always in bytes. The related functions are:

Character Length Semantics

OCI works as a translator between server and client, and passes around character information for constraint checking.

There are two kinds of character sets in the world, variable-width and fixed-width, as a single byte character set is just a special 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 through the whole string to find out number of characters is needed for fixed-width character sets. However, for variable-width ones, complete scanning is inevitable to find out the number of characters.

Character Set Support

See "Character Length Semantics Support in Describing" and "Character Conversion Issues in Binding and Defining" for a complete discussion.

Client Character Set Control from OCI

The function OCIEnvNlsCreate(), first introduced in release 2 of Oracle9i, allows you to set character set information in applications on the fly, independent from NLS_LANG and NLS_NCHAR settings. On the other hand, one application can have several environment handles initialized within the same system environment with 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() does not deprecate OCIEnvCreate(), because OCIEnvNlsCreate() takes two extra parameters which might not be necessary for some applications. On the contrary, OCIEnvNlsCreate() is an enhancement for programmatic control of character sets. Because it validates OCI_UTF16ID, OCIEnvNlsCreate() just deprecates OCI_UTF16 mode.

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. You can retrieve character sets in NLS_LANG and NLS_NCHAR through another OCI function, OCINlsEnvironmentVariableGet().

See Also:


Code Example for Character Set Control in OCI

The following code fragment illustrates a sample usage of these calls.

OCIEnv *envhp; 
ub2 ncsid = 2; /* we8dec */ 
ub2 hdlcsid, hdlncsid; 
raText thename[20]; 
utext *selstmt = L"SELECT ENAME FROM EMP"; /* UTF16 statement */ 
OCIStmt *stmthp; 
OCIDefine *defhp;
OCIError *errhp; 

OCIEnvNlsCreate(OCIEnv **envhp, ..., OCI_UTF16ID, ncsid); 
OCIStmtPrepare(stmthp, ..., selstmt, ...); /* prepare UTF16 statement */ 

OCIDefineByPos(stmthp, defnp, ..., 1, thename, sizeof(thename), SQLT_CHR,...); 

OCINlsEnvironmentVariableGet(&hdlcsid, (size_t)0, OCI_NLS_CHARSET_ID, (ub2)0,

OCIAttrSet(defnp, ..., &hdlcsid, 0, OCI_ATTR_CHARSET_ID, errhp); 
                                  /* change charset id to NLS_LANG setting*/ 

Character Control and OCI Interfaces

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() do 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. Note 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 Database Globalization Support Functions

The following tables list the OCI functions that support database globalization. For complete details and discussions of these functions:

See Also:

OCI String Manipulation Functions

Table 2-10 OCI String Manipulation Functions  
Function Description


Converts an entire null-terminated string into the wchar format


Converts part of a string into the wchar format


Converts an entire null-terminated wide character string into a multibyte string


Converts part of a wide character string into the multibyte format


If there is an uppercase character mapping in the specified locale, then it will return the lowercase in wide character. If not, it returns the same wide character.


If there is an lowercase character mapping in the specified locale, it will return the uppercase in wide character. If not, it returns the same wide character.


Compares two wide character strings by binary, linguistic, or case-insensitive comparison method


Similar to OCIWideCharStrcmp(), but compares two multibyte strings by binary, linguistic, or case-insensitive comparison method. At most len1 bytes form str1, and len2 bytes form str2.


Appends a copy of the string pointed to by wsrcstr. Then it returns the number of characters in the resulting string.


Searches for the first occurrence of wc in the string pointed to by wstr. Then it returns a pointer to the wchar if the search is successful.


Copies the wchar string pointed to by wsrcstr into the array pointed to by wdststr. Then it returns the number of characters copied.


Computes the number of characters in the wchar string pointed to by wstr and returns this number


Appends a copy of the string pointed to by wsrcstr. Then it returns the number of characters in the resulting string, except that at most n characters are appended.


Copies the wchar string pointed to by wsrcstr into the array pointed to by wdststr. Then it returns the number of characters copied, except that at most n characters are copied from the array.


Searches for the last occurrence of wc in the string pointed to by wstr


Converts the wide character string pointed to by wsrcstr into the case specified by a flag and copies the result into the array pointed to by wdststr


Determines the number of column positions required for wc in display


Determines the number of bytes required for wc in multibyte encoding


Compares two multibyte strings by binary, linguistic, or case-insensitive comparison methods


Compares two multibyte strings by binary, linguistic, or case-insensitive comparison methods. At most len1 bytes form str1 and len2 bytes form str2.


Appends a copy of the multibyte string pointed to by srcstr


Copies the multibyte string pointed to by srcstr into an array pointed to by dststr. It returns the number of bytes copied.


Computes the number of bytes in the multibyte string pointed to by str and returns this number


Appends a copy of the multibyte string pointed to by srcstr, except that at most n bytes from srcstr are appended to dststr


Copies the multibyte string pointed to by srcstr into an array pointed to by dststr. It returns the number of bytes copied, except that at most n bytes are copied from the array pointed to by srcstr to the array pointed to by dststr.


Returns the number of display positions occupied by the complete characters within the range of n bytes


Converts part of a string from one character set to another

OCI Character Classification Functions

Table 2-11 OCI Character Classification Functions  
Function Description


Tests whether the wide character is a letter or decimal digit


Tests whether the wide character is an alphabetic letter


Tests whether the wide character is a control character


Tests whether the wide character is a decimal digital character


Tests whether the wide character is a graph character


Tests whether the wide character is a lowercase letter


Tests whether the wide character is a printable character


Tests whether the wide character is a punctuation character


Tests whether the wide character is a space character


Tests whether the wide character is an uppercase character


Tests whether the wide character is a hexadecimal digit


Tests whether wc is a single-byte character when converted into multibyte

OCI Character Conversion Functions

Table 2-12 OCI Character Set Conversion Functions  
Function Description


Converts a multibyte string pointed to by src to Unicode into the array pointed to by dst


Converts a Unicode string pointed to by src to multibyte into the array pointed to by dst


Indicates whether the replacement character was used for nonconvertible characters in character set conversion in the last invocation of OCICharsetConv()

OCI Messaging Functions

Table 2-13 OCI Messaging Functions  
Function Description


Opens a message handle in a language pointed to by hndl


Retrieves a message with message number identified by msgno. If the buffer is not zero, then the function copies the message into the buffer pointed to by msgbuf.


Closes a message handle pointed to by msgh and frees any memory associated with this handle

Go to previous page Go to next page
Copyright © 1996, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index