2 OCI Programming Basics

Handles

Almost every OCI call includes in its 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 must access the information stored in handles. The get and set attribute OCI calls, OCIAttrGet() and OCIAttrSet(), access and set this information.

Table 2-1 lists the handles defined for OCI. For each handle type, the C data type and handle type constant used to identify the handle type in OCI calls are listed.

Service Context Handle and Associated Handles

A service context handle defines attributes that determine the operational context for OCI calls to a server. The service context handle contains three handles as its attributes, that represent a server connection, a user session, and a transaction. These attributes are illustrated in Figure 2-2.

Figure 2-2 Components of a Service Context

Description of "Figure 2-2 Components of a Service Context"

• A server handle identifies a connection to a database. It translates into a physical connection in a connection-oriented transport mechanism.

• A user session handle defines a user's roles and privileges (also known as the user's security domain), and the operational context in which the calls execute.

• A transaction handle defines the transaction in which the SQL operations are performed. The transaction context includes user session state information, including any fetch state and package instantiation.

Breaking the service context handle down in this way provides scalability and enables programmers to create sophisticated multitiered 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(), OCILogon(), or OCILogon2() before you can use it. The service context handle is allocated explicitly by OCIHandleAlloc(). It can be initialized using OCIAttrSet() with the server, user 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 handle must be explicitly allocated, and the server and user session handles must be explicitly set into the service context handle. OCIServerAttach() and OCISessionBegin() calls initialize the server and user session handle respectively.

An application only defines a transaction explicitly if it is a global transaction or there are multiple transactions active for sessions. It works correctly with the implicit transaction created automatically by OCI when the application makes changes to the database.

See Also:

• "OCI Support for Transactions"

• "OCI Environment Initialization", and "Password and Session Management" for more information about establishing a server connection and user session

Statement, Bind, and Define Handles

A statement handle is the context that identifies a SQL or PL/SQL statement and its associated attributes, as shown in Figure 2-3.

Figure 2-3 Statement Handles

Description of "Figure 2-3 Statement Handles"

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 must not 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 must not allocate define handles. They are implicitly allocated by the define call.

Bind and define handles are implicitly allocated by the OCI library, and are transparently reused if the bind or define operation is repeated. The actual value of the bind or define handle is needed by the application for the advanced bind or define operations described in Chapter 5. The handles are freed when the statement handle is freed or when a new statement is prepared on the statement handle. Explicitly allocating bind or define handles may lead to memory leaks. Explicitly freeing bind or define handles may cause abnormal program termination.

See Also:

• "Advanced Bind Operations in OCI"

• "Advanced Define Operations in OCI"

Direct Path Handles

The direct path handles are necessary for an OCI application that uses the direct path load engine in the Oracle database. The direct path load interface enables the application to access the direct block formatter of the Oracle database. Figure 2-4 shows the different kinds of direct path handles.

Figure 2-4 Direct Path Handles

Description of "Figure 2-4 Direct Path Handles"

See Also:

• "Direct Path Loading Overview"

• "Direct Path Loading Handle Attributes"

text username[] = "hr";
err = OCIAttrSet ((void *) mysessp, OCI_HTYPE_SESSION, (void *)username,
      (ub4) strlen((char *)username), OCI_ATTR_USERNAME, (OCIError *) myerrhp);

ub4 parmcnt;
/* get the number of columns in the select list */
err = OCIAttrGet ((void  *)stmhp, (ub4)OCI_HTYPE_STMT, (void  *)
         &parmcnt, (ub4 *) 0, (ub4)OCI_ATTR_PARAM_COUNT, errhp);

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

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

• "Creating the OCI Environment"

• "Allocating Handles and Descriptors"

• "Application Initialization, Connection, and Session Creation"

OCILogon2(envhp, errhp, &svchp, (text *)"hr", (ub4)strlen("hr"), (text *)"hr",
          (ub4)strlen("hr"), (text *)"oracledb", (ub4)strlen("oracledb"),
          OCI_DEFAULT);

ALTER USER <targetuser> GRANT CONNECT THROUGH <proxy> [AUTHENTICATION REQUIRED];

ALTER USER joe GRANT CONNECT THROUGH dilbert;

dilbert[joe]/tiger123@db1

ALTER USER "Joe" GRANT CONNECT THROUGH "Dilbert";

"Dilbert"["Joe"]/tiger123@db1

"dilbert[mybert]"/tiger123

rem the user was already created this way:
rem CREATE USER "dilbert[mybert]" IDENTIFIED BY tiger123;

"dilbert[mybert]"["joe[myjoe]"]/tiger123

ALTER USER joe GRANT CONNECT THROUGH dilbert;

OCIAttrSet(session, OCI_HTYPE_SESSION, (void  *)"dilbert",
           (ub4)strlen("dilbert"), OCI_ATTR_USERNAME, 
           error_handle);
OCIAttrSet(session, OCI_HTYPE_SESSION, (void  *)"tiger123", 
           (ub4)strlen("tiger123"), OCI_ATTR_PASSWORD, 
           error_handle);
OCIAttrSet(session, OCI_HTYPE_SESSION, (void  *)"joe",
           (ub4)strlen("joe"), OCI_ATTR_PROXY_CLIENT, 
           error_handle);

#include <oci.h>
...
main()
{
...
OCIEnv     *myenvhp;    /* the environment handle */
OCIServer  *mysrvhp;    /* the server handle */
OCIError   *myerrhp;    /* the error handle */
OCISession *myusrhp;    /* user session handle */
OCISvcCtx  *mysvchp;    /* the  service handle */
...
/* initialize the mode to be the threaded and object environment */
(void) OCIEnvCreate(&myenvhp, OCI_THREADED|OCI_OBJECT, (void  *)0,
                    0, 0, 0, (size_t) 0, (void  **)0);

     /* allocate a server handle */
(void) OCIHandleAlloc ((void  *)myenvhp, (void  **)&mysrvhp,
      OCI_HTYPE_SERVER, 0, (void  **) 0);

      /* allocate an error handle */
(void) OCIHandleAlloc ((void  *)myenvhp, (void  **)&myerrhp,
      OCI_HTYPE_ERROR, 0, (void  **) 0);

      /* create a server context */
(void) OCIServerAttach (mysrvhp, myerrhp, (text *)"inst1_alias",
      strlen ("inst1_alias"), OCI_DEFAULT);

     /* allocate a service handle */
(void) OCIHandleAlloc ((void  *)myenvhp, (void  **)&mysvchp,
      OCI_HTYPE_SVCCTX, 0, (void  **) 0);

 /* set the server attribute in the service context handle*/
(void) OCIAttrSet ((void  *)mysvchp, OCI_HTYPE_SVCCTX,
       (void  *)mysrvhp, (ub4) 0, OCI_ATTR_SERVER, myerrhp);

      /* allocate a user session handle */
(void) OCIHandleAlloc ((void  *)myenvhp, (void  **)&myusrhp,
     OCI_HTYPE_SESSION, 0, (void  **) 0);

      /* set user name attribute in user session handle */
 (void) OCIAttrSet ((void  *)myusrhp, OCI_HTYPE_SESSION,
      (void  *)"hr", (ub4)strlen("hr"),
      OCI_ATTR_USERNAME, myerrhp);

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

 (void) OCISessionBegin ((void  *) mysvchp, myerrhp, myusrhp,
      OCI_CRED_RDBMS, OCI_DEFAULT);

    /* set the user session attribute in the service context handle*/
 (void) OCIAttrSet ((void  *)mysvchp, OCI_HTYPE_SVCCTX,
       (void  *)myusrhp, (ub4) 0, OCI_ATTR_SESSION, myerrhp);
...
}

Processing SQL Statements in OCI

Chapter 4 outlines the specific steps involved in processing SQL statements in OCI.

Commit or Roll Back Operations

An application commits changes to the database by calling OCITransCommit(). This call uses a service context as one of its parameters. The transaction is associated with the service context whose changes are committed. This transaction can be explicitly created by the application or implicitly created when the application modifies the database.

To roll back a transaction, use the OCITransRollback() call.

If an application disconnects from Oracle Database in a way other than a normal logoff, such as losing a network connection, and OCITransCommit() has not been called, all active transactions are rolled back automatically.

Terminating the Application

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

1. Delete the user session by calling OCISessionEnd() for each session.

2. Delete access to the data sources 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.

   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 but are recommended. If the application terminates, and OCITransCommit() (transaction commit) has not been called, any pending transactions are automatically rolled back.

Return and Error Codes for Data

In Table 2-4, the OCI return code, error number, indicator variable, and column return code are specified when the data fetched is normal, null, or truncated.

For truncated data, data_len is the actual length of the data that has been truncated if this length is less than or equal to SB2MAXVAL. Otherwise, the indicator is set to -2.

Functions Returning Other Values

Some functions return values other than the OCI error codes listed in Table 2-3. When you use these functions, be aware that they return values directly from the function call, rather than through an OUT parameter. More detailed information about each function and its return values is listed in the reference chapters.

Operating System Considerations

Operating systems may provide facilities for spawning processes that allow child processes to reuse the state created by their parent process. After spawning a child process, the child process must not use the same database connection as created by the parent. Any attempt on behalf of the child process to use the same database connection as the parent may cause undesired connection interference and result in intermittent ORA-03137 errors, because Oracle Net expects only one user process to be using a connection to the database.

Where multiple, concurrent connections are required, consider using threads if your platform supports a threads package. Concurrent connections are supported in either single-threaded or multithreaded applications.

For better performance with many concurrently opened connections, consider pooling them.

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.

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 makes the ENAME column NULL.

       INSERT INTO emp1 (ename, empno, deptno)
          VALUES (NULL, 8010, 20)
  

• Use indicator variables in the OCI bind call. See "Indicator Variables".

• Insert a NULL to set both the buffer length and maximum length parameters to zero on a bind call.

  Note:

  Following the SQL standard requirements, Oracle Database 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 associates an indicator variable, or an array of indicator variables, with a DML statement, a PL/SQL statement, or a query.

The C language does not have the concept of null values; therefore, you associate indicator variables with input variables to specify whether the associated placeholder is a NULL. When data is passed to an Oracle database, the values of these indicator variables determine whether 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. For a NULL fetch in an OCIStmtFetch2() call or a truncation in an OCIStmtExecute() call, the OCI call returns OCI_SUCCESS_WITH_INFO. The output indicator variable is set.

The data type of indicator variables is sb2. For arrays of indicator variables, the individual array elements should be of type sb2.

Canceling Calls

On most operating systems, you can cancel long-running or repeated OCI calls by entering the operating system's interrupt character (usually Control+C) from the keyboard.

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.

When given a particular service context pointer or server context pointer, the OCIBreak() function performs an immediate (asynchronous) stop of any currently executing OCI function associated with the server. It is normally used to stop a long-running OCI call being processed on the server. The OCIReset() function is necessary to perform a protocol synchronization on a nonblocking connection after an OCI application stops a function with OCIBreak().

The status of potentially long-running calls can be monitored using nonblocking calls. Use multithreading for new applications.

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, consider a SQL statement such as the following:

SELECT ename FROM emp1 WHERE empno = 7499 FOR UPDATE OF sal

When the fetch is performed, the ROWID attribute in the handle contains the row identifier of the selected row. You can retrieve the ROWID into a buffer in your program by calling OCIAttrGet() as follows:

OCIRowid *rowid;   /* the rowid in opaque format */
/* allocate descriptor with OCIDescriptorAlloc() */
status = OCIDescriptorAlloc ((void  *) envhp, (void  **) &rowid,
     (ub4) OCI_DTYPE_ROWID, (size_t) 0, (void  **) 0);
status = OCIAttrGet ((void *) mystmtp, OCI_HTYPE_STMT,
     (void *) 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 the following by binding the new salary to the :1 placeholder and rowid to the :2 placeholder.

UPDATE emp1 SET sal = :1 WHERE rowid = :2

Be sure to use data type code 104 (ROWID descriptor, see Table 3-2) when binding rowid to :2.

By using prefetching, you can select an array of ROWIDs for use in subsequent batch updates.

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.

Polling Mode Operations in OCI

OCI has calls that poll for completion. Examples of such polling mode calls are:

• OCI calls in nonblocking mode

• OCI calls that operate on LOB data in pieces such as OCILobRead2() and OCILobWrite2()

• OCIStmtExecute() and OCIStmtFetch2() when used with OCIStmtSetPieceInfo() and OCIStmtGetPieceInfo()

In such cases, OCI requires that the application ensure that the same OCI call is repeated on the connection and nothing else is done on the connection in the interim. Performing any other OCI call on such a connection (when OCI has handed control back to the caller) can result in unexpected behavior.

Hence, with such polling mode OCI calls, the caller must ensure that the same call is repeated on the connection and that nothing else is done until the call completes.

OCIBreak() and OCIReset() are exceptions to the rule. These calls are allowed so that the caller can stop an OCI call that has been started.

Nonblocking Mode in OCI

OCI provides the ability to establish a server connection in blocking mode or nonblocking mode. When a connection is made in blocking mode, an OCI call returns control to an OCI client application only when the call completes, either successfully or in error. With the nonblocking mode, control is immediately returned to the OCI program if the call could not complete, and the call returns a value of OCI_STILL_EXECUTING.

In nonblocking mode, an application must test the return code of each OCI function to see if it returns OCI_STILL_EXECUTING. If it does, the OCI client can continue to process program logic while waiting to retry the OCI call to the server. This mode is particularly useful in graphical user interface (GUI) applications, real-time applications, and in distributed environments.

The nonblocking mode is not interrupt-driven. Rather, it is based on a polling paradigm, which means that the client application must check whether the pending call is finished at the server by executing the call again with the exact same parameters.

The following features and functions are not supported in nonblocking mode:

• Direct Path Load

• LOB buffering

• Objects

• Query cache

• Scrollable cursors

• Transparent application failover (TAF)

• OCIAQEnqArray()

• OCIAQDeqArray()

• OCIDescribeAny()

• OCILobArrayRead()

• OCILobArrayWrite()

• OCITransStart()

• OCITransDetach()

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. You must set this attribute only after OCISessionBegin() or OCILogon2() has been called. Otherwise, an error is returned.

Canceling a Nonblocking Call

You can cancel a long-running OCI call by using the OCIBreak() function while the OCI call is in progress. You must then issue an OCIReset() call to reset the asynchronous operation and protocol.

Client Character Set Control from OCI

The function OCIEnvNlsCreate() enables you to set character set information in applications independently from NLS_LANG and NLS_NCHAR settings. One application can have several environment handles initialized within the same system environment using different client-side character set IDs and national character set IDs. For example:

OCIEnvNlsCreate(OCIEnv **envhpp, ..., csid, ncsid); 

In this example, csid is the value for the character set ID, and ncsid is the value for the 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.

The OCIEnvNlsCreate() function is an enhancement for programmatic control of character sets, because it validates OCI_UTF16ID.

When character set IDs are set through the function OCIEnvNlsCreate(), they replace the settings in NLS_LANG and NLS_NCHAR. In addition to all character sets supported by the National Language Support Runtime Library (NLSRTL), OCI_UTF16ID is allowed as a character set ID in the OCIEnvNlsCreate() function, although this ID is not valid in NLS_LANG or NLS_NCHAR.

Any Oracle character set ID, except AL16UTF16, can be specified through the OCIEnvNlsCreate() function to specify the encoding of metadata, SQL CHAR data, and SQL NCHAR data.

You can retrieve character sets in NLS_LANG and NLS_NCHAR through another function, OCINlsEnvironmentVariableGet().

Character Control and OCI Interfaces

The OCINlsGetInfo() function returns information about OCI_UTF16ID if this value has been used in OCIEnvNlsCreate().

The OCIAttrGet() function 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 are returned.

The OCIAttrSet() function 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() is passed as charset or ncharset.

The OCIBindByName() and OCIBindByPos() functions bind variables with the 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.

The OCIDefineByPos() function defines variables with the value of charset in OCIEnvNlsCreate(), including OCI_UTF16ID, as the default. The actual length and returned length are always in bytes if OCIEnvNlsCreate() is used. This behavior for bind and define handles is different from that when OCIEnvCreate() is used and OCI_UTF16ID is the character set ID for the bind and define handles.

Character-Length Semantics in OCI

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

There are two kinds of character sets: variable-width and fixed-width. (A single-byte character set is a special case of a fixed-width character set where each byte stands for one character.)

For fixed-width character sets, constraint checking is easier, as the number of bytes is equal to a multiple of the number of characters. Therefore, scanning of the entire string is not needed to determine the number of characters for fixed-width character sets. However, for variable-width character sets, complete scanning is needed to determine the number of characters in a string.

Character Set Support in OCI

See "Character-Length Semantics Support in Describe Operations" and "Character Conversion in OCI Binding and Defining" for a complete discussion of character set support in OCI.

Other OCI Globalization Support Functions

Many globalization support functions accept either the environment handle or the user session handle. The OCI environment handle is associated with the client NLS environment variables. This environment does not change when ALTER SESSION statements are issued to the server. The character set associated with the environment handle is the client character set. The OCI session handle (returned by OCISessionBegin()) is associated with the server session environment. The NLS settings change when the session environment is modified with an ALTER SESSION statement. The character set associated with the session handle is the database character set.

Note that the OCI session handle does not have NLS settings associated with it until the first transaction begins in the session. SELECT statements do not begin a transaction.

Getting Locale Information in OCI

An Oracle Database locale consists of language, territory, and character set definitions. The locale determines conventions such as day and month names, as well as date, time, number, and currency formats. A globalized application follows a user's locale setting and cultural conventions. For example, when the locale is set to German, users expect to see day and month names in German.

You can retrieve the following information with the OCINlsGetInfo() function:

• Days of the week (translated)

• Abbreviated days of the week (translated)

• Month names (translated)

• Abbreviated month names (translated)

• Yes/no (translated)

• AM/PM (translated)

• AD/BC (translated)

• Numeric format

• Debit/credit

• Date format

• Currency formats

• Default language

• Default territory

• Default character set

• Default linguistic sort

• Default calendar

The code in Example 2-13 retrieves locale information and checks for errors.

sword MyPrintLinguisticName(envhp, errhp)
OCIEnv   *envhp;
OCIError *errhp;
{
  OraText  infoBuf[OCI_NLS_MAXBUFSZ];
  sword ret;
  
  ret = OCINlsGetInfo(envhp,                           /* environment handle */
                      errhp,                                 /* error handle */
                      infoBuf,                         /* destination buffer */
                      (size_t) OCI_NLS_MAXBUFSZ,              /* buffer size */
                      (ub2) OCI_NLS_LINGUISTIC_NAME);                /* item */

  if (ret != OCI_SUCCESS) 
  {
    checkerr(errhp, ret, OCI_HTYPE_ERROR);
    ret = OCI_ERROR; 
  }
  else
  {
    printf("NLS linguistic: %s\n", infoBuf);
   }
  return(ret);
}

Manipulating Strings in OCI

Multibyte strings and wide-character strings are supported for string manipulation.

Multibyte strings are encoded in native Oracle character sets. Functions that operate on multibyte strings take the string as a whole unit with the length of the string calculated in bytes. Wide-character string (wchar) functions provide more flexibility in string manipulation. They support character-based and string-based operations where the length the string calculated in characters.

The wide-character data type, OCIWchar, is Oracle-specific and should not be confused with the wchar_t data type defined by the ANSI/ISO C standard. The Oracle wide-character data type is always 4 bytes in all operating systems, whereas the size of wchar_t depends on the implementation and the operating system. The Oracle wide-character data type normalizes multibyte characters so that they have a uniform fixed width for easy processing. This guarantees no data loss for round-trip conversion between the Oracle wide-character set and the native character set.

String manipulation can be classified into the following categories:

• Conversion of strings between multibyte and wide character

• Character classifications

• Case conversion

• Calculations of display length

• General string manipulation, such as comparison, concatenation, and searching

  See Also:

  "OCI String Manipulation Functions"

Example 2-14 shows a simple case of manipulating strings.

size_t MyConvertMultiByteToWideChar(envhp, dstBuf, dstSize, srcStr)
OCIEnv     *envhp;
OCIWchar   *dstBuf;
size_t      dstSize;
OraText    *srcStr;                         /* null terminated source string */
{
  sword  ret;
  size_t dstLen = 0;
  size_t srcLen;

  /* get length of source string */
  srcLen = OCIMultiByteStrlen(envhp, srcStr);
  
  ret = OCIMultiByteInSizeToWideChar(envhp,            /* environment handle */
                 dstBuf,                               /* destination buffer */
                 dstSize,                         /* destination buffer size */
                 srcStr,                                    /* source string */
                 srcLen,                          /* length of source string */
                 &dstLen);                  /* pointer to destination length */

  if (ret != OCI_SUCCESS)                                          
  {
    checkerr(envhp, ret, OCI_HTYPE_ENV);                 
  }
  return(dstLen);
}

The OCI character classification functions are described in detail in "OCI Character Classification Functions".

Example 2-15 shows how to classify characters in OCI.

boolean MyIsNumberWideCharString(envhp, srcStr)
OCIEnv   *envhp;
OCIWchar *srcStr;                                 /* wide char source string */
{
  OCIWchar *pstr = srcStr;                        /* define and init pointer */
  boolean status = TRUE;            /* define and initialize status variable */

  /* Check input */
  if (pstr == (OCIWchar*) NULL)
    return(FALSE);


  if (*pstr == (OCIWchar) NULL)
    return(FALSE);

                                            /* check each character for digit */
  do 
  {
    if (OCIWideCharIsDigit(envhp, *pstr) != TRUE)
    {
      status = FALSE;
      break;                                  /* non-decimal digit character */
    }
  } while ( *++pstr != (OCIWchar) NULL);

  return(status);
}

Converting Character Sets in OCI

Conversion between Oracle character sets and Unicode (16-bit, fixed-width Unicode encoding) is supported. Replacement characters are used if a character has no mapping from Unicode to the Oracle character set. Therefore, conversion back to the original character set is not always possible without data loss.

Character set conversion functions involving Unicode character sets require data bind and define buffers to be aligned at a ub2 address or an error is raised.

Example 2-16 shows a simple conversion into Unicode.

/* Example of Converting Character Sets in OCI
--------------------------------------------*/

size_t MyConvertMultiByteToUnicode(envhp, errhp, dstBuf, dstSize, srcStr)
OCIEnv   *envhp;
OCIError *errhp;
ub2 *dstBuf;
size_t dstSize;
OraText *srcStr;
{
  size_t dstLen = 0;
  size_t srcLen = 0;
  OraText tb[OCI_NLS_MAXBUFSZ];   /* NLS info buffer */
  ub2    cid;                     /* OCIEnv character set ID */

  /* get OCIEnv character set */
  checkerr(errhp, OCINlsGetInfo(envhp, errhp, tb, sizeof(tb),
                                OCI_NLS_CHARACTER_SET));
  cid = OCINlsCharSetNameToId(envhp, tb);

  if (cid == OCI_UTF16ID)
  {
    ub2    *srcStrUb2 = (ub2*)srcStr;
    while (*srcStrUb2++) ++srcLen;
    srcLen *= sizeof(ub2);
  }
  else
    srcLen = OCIMultiByteStrlen(envhp, srcStr);

  checkerr(errhp,
    OCINlsCharSetConvert(
      envhp,       /* environment handle */
      errhp,       /* error handle */
      OCI_UTF16ID, /* Unicode character set ID */
      dstBuf,      /* destination buffer */
      dstSize,     /* size of destination buffer */
      cid,         /* OCIEnv character set ID */
      srcStr,      /* source string */
      srcLen,      /* length of source string */
      &dstLen));   /* pointer to destination length */

  return dstLen/sizeof(ub2);
}

OCI Messaging Functions

The user message API provides a simple interface for cartridge developers to retrieve their own messages and Oracle Database messages.

Example 2-17 creates a message handle, initializes it to retrieve messages from impus.msg, retrieves message number 128, and closes the message handle. It assumes that OCI environment handles, OCI session handles, and the product, facility, and cache size have been initialized properly.

OCIMsg msghnd;                                              /* message handle */
         /* initialize a message handle for retrieving messages from impus.msg*/
err = OCIMessageOpen(hndl,errhp, &msghnd, prod,fac,OCI_DURATION_SESSION);
if (err != OCI_SUCCESS)
                                                            /* error handling */
...
                            /* retrieve the message with message number = 128 */
msgptr = OCIMessageGet(msghnd, 128, msgbuf, sizeof(msgbuf));
                         /* do something with the message, such as display it */
...
      /* close the message handle when there are no more messages to retrieve */
OCIMessageClose(hndl, errhp, msghnd);

lmsgen Utility

The lmsgen utility converts text-based message files (.msg) into binary format (.msb) so that Oracle Database messages and OCI messages provided by the user can be returned to OCI functions in the desired language.

The BNF syntax of the Imsgen utility is as follows:

lmsgen text_file product facility [language]

In the preceding syntax:

• text_file is a message text file.

• product is the name of the product.

• facility is the name of the facility.

• language is the optional message language corresponding to the language specified in the NLS_LANG parameter. The language parameter is required if the message file is not tagged properly with language.

/ Copyright (c) 2001 by the Oracle Corporation.  All rights reserved.
/ This is a test us7ascii message file
# CHARACTER_SET_NAME= american_america.us7ascii
/
00000, 00000, "Export terminated unsuccessfully\n"
00003, 00000, "no storage definition found for segment(%lu, %lu)"

$HOME/myApp/mesg/impus.msg

00128,2, "Duplicate entry %s found in %s"

% lmsgen impus.msg $HOME/myApplication imp AMERICAN

Generating message file impus.msg -->
/home/scott/myApplication/mesg/impus.msb

NLS Binary Message File Generation Utility: Version 9.2.0.0.0 -Production

Copyright (c) Oracle Corporation 1979, 2001.  All rights reserved.

CORE    9.2.0.0.0       Production