4
Using SQL Statements in OCI

This chapter discusses the concepts and steps involved in processing SQL statements with the Oracle Call Interface. The following topics are covered in this chapter:

• Overview of SQL Statement Processing
• Processing SQL Statements
• Preparing Statements
• What is Binding?
• Executing Statements
• Describing Select-List Items
• What is Defining?
• Fetching Results
• Scrollable Cursors

Overview of SQL Statement Processing

Chapter 2, "OCI Programming Basics" discussed the basic steps involved in any OCI application. This chapter presents a more detailed look at the specific tasks involved in processing SQL statements in an OCI program.

Processing SQL Statements

One of the most common tasks of an OCI program is to accept and process SQL statements. This section outlines the specific steps involved in processing SQL.

Once you have allocated the necessary handles and attached to a server, the basic steps in processing a SQL statement are the following, as illustrated in Figure 4-1, "Steps In Processing SQL Statements":

1. Prepare. Define an application request using OCIStmtPrepare().
2. Bind. For DML statements and queries with input variables, perform one or more bind calls using
   • OCIBindByPos(),
   • OCIBindByName(),
   • OCIBindObject(),
   • OCIBindDynamic(),
   • or OCIBindArrayOfStruct()

   to bind the address of each input variable (or PL/SQL output variable) or array to each placeholder in the statement.

3. Execute. Call OCIStmtExecute() to execute the statement. For DDL statements, no further steps are necessary.
4. Describe. Describe the select-list items, if necessary, using OCIParamGet() and OCIAttrGet(). This is an optional step; it is not required if the number of select-list items and the attributes of each item (such as its length and datatype) are known at compile time.
5. Define. For queries, perform one or more define calls to OCIDefineByPos(), OCIDefineObject(), OCIDefineDynamic(), or OCIDefineArrayOfStruct() to define an output variable for each select-list item in the SQL statement. Note that you do not use a define call to define the output variables in an anonymous PL/SQL block. You have done this when you have bound the data.
6. Fetch. For queries, call OCIStmtFetch() to fetch the results of the query.

Following these steps, the application can free allocated handles and then detach from the server, or it may process additional statements.

Figure 4-1 Steps In Processing SQL Statements

Text description of the illustration lnoci020.gif

For each of the steps in the diagram, the corresponding OCI function calls are listed. In some cases multiple calls may be required.

Each step above is described in detail in the following sections.

Additional steps beyond those listed above may be required if your application needs to do the following:

• initiate and manage multiple transactions
• manage multiple threads of execution
• perform piecewise inserts, updates, or fetches

  See Also: These topics are described in Chapter 9, "OCI Programming Advanced Topics". For information on using OCI shared mode functionality, refer to "Shared Data Mode".

Preparing Statements

SQL and PL/SQL statements need to be prepared for execution by using the statement prepare call and bind calls (if necessary). In this phase, the application specifies a SQL or PL/SQL statement and binds associated placeholders in the statement to data for execution. The client-side library allocates storage to maintain the statement prepared for execution.

An application requests a SQL or PL/SQL statement to be prepared for execution using the OCIStmtPrepare() call and passing it a previously allocated statement handle. This is a completely local call, requiring no round trip to the server. No association is made at this point between the statement and a particular server.

Following the request call, an application can call OCIAttrGet() on the statement handle, passing OCI_ATTR_STMT_TYPE to the attrtype parameter, to determine what type of SQL statement was prepared. The possible attribute values, and corresponding statement types are listed in Table 4-1.

Table 4-1 OCI_ATTR_STMT_TYPE Values and Statement Types  

Attribute Value	Statement Type
OCI_STMT_SELECT	SELECT statement
OCI_STMT_UPDATE	UPDATE statement
OCI_STMT_DELETE	DELETE statement
OCI_STMT_INSERT	INSERT statement
OCI_STMT_CREATE	CREATE statement
OCI_STMT_DROP	DROP statement
OCI_STMT_ALTER	ALTER statement
OCI_STMT_BEGIN	BEGIN... (PL/SQL)
OCI_STMT_DECLARE	DECLARE... (PL/SQL)

Using Prepared Statements on Multiple Servers

A prepared application request can be executed on multiple servers at run time by reassociating the statement handle with the respective service context handles for the servers. All information cached about the current service context and statement handle association is lost when a new association is made.

For example, consider an application such as a network manager, which manages multiple servers. In many cases, it is likely that the same SELECT statement will need to be executed against multiple servers to retrieve information for display. The OCI allows the network manager application to prepare a SELECT statement once and execute it against multiple servers. It must fetch all of the required rows from each server prior to reassociating the prepared statement with the next server.

What is Binding?

Most DML statements, and some queries (such as those with a WHERE clause), require a program to pass data to Oracle as part of a SQL or PL/SQL statement. Such data can be constant or literal data, known when your program is compiled. For example, the following SQL statement, which adds an employee to a database contains several literals, such as 'BESTRY' and 2365:

INSERT INTO emp VALUES
    (2365, 'BESTRY', 'PROGRAMMER', 2000, 20)

Coding a statement like this into an application would severely limit its usefulness. You would need to change the statement and recompile the program each time you add a new employee to the database. To make the program more flexible, you can write the program so that a user can supply input data at run time.

When you prepare a SQL statement or PL/SQL block that contains input data to be supplied at run time, placeholders in the SQL statement or PL/SQL block mark where data must be supplied. For example, the following SQL statement contains five placeholders, indicated by the leading colons (:ename), that show where input data must be supplied by the program.

INSERT INTO emp VALUES
    (:empno, :ename, :job, :sal, :deptno)

You can use placeholders for input variables in any DELETE, INSERT, SELECT, or UPDATE statement, or PL/SQL block, in any position in the statement where you can use an expression or a literal value. In PL/SQL, placeholders can also be used for output variables.

Placeholders cannot be used to represent other Oracle objects such as tables. For example, the following is not a valid use of the emp placeholder:

INSERT INTO :emp VALUES
    (12345, 'OERTEL', 'WRITER', 50000, 30)

For each placeholder in the SQL statement or PL/SQL block, you must call an OCI routine that binds the address of a variable in your program to the placeholder. When the statement executes, Oracle gets the data that your program placed in the input, or bind, variables and passes it to the server with the SQL statement.

Executing Statements

An OCI application executes prepared statements individually using OCIStmtExecute().

When an OCI application executes a query, it receives data from Oracle that matches the query specifications. Within the database, the data is stored in Oracle-defined formats. When the results are returned, an OCI application can request that data be converted to a particular host language format, and stored in a particular output variable or buffer.

For each item in the select-list of a query, the OCI application must define an output variable to receive the results of the query. The define step indicates the address of the buffer and the type of the data to be retrieved.

For non-queries, the number of times the statement is executed during array operations is equal to iters - rowoff, where rowoff is the offset in the bound array, and is also a parameter of the OCIStmtExecute() call. For example, if an array of 10 items is bound to a placeholder for an INSERT statement, and iters is set to 10, all 10 items will be inserted in a single execute call when rowoff is zero. If rowoff is set to 2, only 8 items will be inserted.

Execution Snapshots

The OCIStmtExecute() call provides the ability to ensure that multiple service contexts operate on the same consistent snapshot of the database's committed data. This is achieved by taking the contents of the snap_out parameter of one OCIStmtExecute() call and passing that value in the snap_in parameter of the next OCIStmtExecute() call.

The datatype of both the snap_out and snap_in parameter is OCISnapshot, an OCI snapshot descriptor. This descriptor is allocated with the OCIDescAlloc() function.

It is not necessary to specify a snapshot when calling OCIStmtExecute(). The following sample code shows a basic execution in which the snapshot parameters are passed as NULL.

checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
          (OCISnapshot *)NULL, (OCISnapshot *) NULL, OCI_DEFAULT))

Execution Modes

You can specify several modes for the OCIStmtExecute() call:

• OCI_DEFAULT. Calling OCIStmtExecute() in this mode executes the statement. It also implicitly returns describe information about the select-list.
• OCI_DESCRIBE_ONLY. This mode is for users who wish to describe a query prior to execution. Calling OCIStmtExecute() in this mode does not execute the statement, but it does return the select-list description.
• OCI_COMMIT_ON_SUCCESS - When a statement is executed in this mode, the current transaction is committed after execution, if execution completes successfully.
• OCI_EXACT_FETCH - Used when the application knows in advance exactly how many rows it will be fetching.
• OCI_BATCH_ERRORS - See "Batch Error Mode for OCIStmtExecute()", for information about this mode.

Batch Error Mode for OCIStmtExecute()

The OCI provides the ability to perform array DML operations. For example, an application can process an array of INSERT, UPDATE, or DELETE statements with a single statement execution. If one of the operations fails due to an error from the server, such as a unique constraint violation, the array operation aborts and the OCI returns an error. Any rows remaining in the array are ignored. The application must then reexecute the remainder of the array, and go through the whole process again if it encounters more errors, which makes additional round-trips.

To facilitate processing of array DML operations, the OCI provides the batch error mode (also called the enhanced DML array feature). This mode, which is specified in the OCIStmtExecute() call, simplifies DML array processing in the event of one or more errors. In this mode, the OCI attempts to INSERT, UPDATE, or DELETE all rows, and collects (batches) information about any errors which occurred. The application can then retrieve this error information and reexecute any DML operations which failed during the first call.

In this way, all DML operations in the array are attempted in the first call, and any failed operations can be reissued in a second call.

This mode is used as follows:

1. The user specifies OCI_BATCH_ERRORS as the mode parameter of the OCIStmtExecute() call.
2. After performing an array DML operation with OCIStmtExecute(), the application can retrieve the number of errors encountered during the operation by calling OCIAttrGet() on the statement handle to retrieve the OCI_ATTR_NUM_DML_ERRORS attribute. For example:

   ub4   num_errs;
   OCIAttrGet(stmtp, OCI_HTYPE_STMT, &num_err, 0, OCI_ATTR_NUM_DML_ERRORS, errhp);
   
   

3. The list of errors hangs off an error handle.

   The application extracts each error, along with its row information, from the error handle which was passed to the OCIStmtExecute() call using OCIParamGet(). In order to retrieve the information, the application must allocate an additional new error handle for the OCIParamGet() call. This new error handle is populated with the batched error information. The application obtains the syntax of each error with OCIErrorGet(), and the row offset (into the DML array) at which the error occurred by calling OCIAttrGet() on the new error handle.

   For example, once the num_errs amount has been retrieved, the application can issue the following calls:

   ...
   OCIError errhndl;
   for (i=0; i<num_errs; i++) 
   {
      OCIParamGet(errhp, OCI_HTYPE_ERROR, errhp, &errhndl, i);
      OCIAttrGet(errhndl, OCI_HTYPE_ERROR, &row_offset, 0,
          OCI_ATTR_DML_ROW_OFFSET, errhp);
      OCIErrorGet(..., errhndl, ...);
   ...
   
   

   Following this, the application can correct the bind information for the appropriate entry in the array using the diagnostic information retrieved from the batched error. Once the appropriate bind buffers are corrected or updated, the application can reexecute the associated DML statements.

   Because the application cannot know at compile time which rows in the first execution will cause errors, the binds for the subsequent DML should be done dynamically by passing in the appropriate buffers at runtime. The user can reuse the bind buffers used in the array binds done on the first DML operation.

Example of Batch Error Mode

The following code shows an example of how this execution mode might be used. In this example assume that we have an application which inserts five rows (with two columns, of types NUMBER and CHAR) into a table. Furthermore, let us assume only two rows (say, 1 and 3) are successfully inserted in the initial DML operation. The user then proceeds to correct the data (wrong data was being inserted the first time) and to issue an update with the corrected data. The user uses statement handles stmtp1 and stmtp2 to issue the INSERT and UPDATE respectively.

...
OCIBind *bindp1[2], *bindp2[2];
ub4 num_errs, row_off[MAXROWS], number[MAXROWS] = {1,2,3,4,5};
char grade[MAXROWS] = {'A','B','C','D','E'};
...
/* Array bind all the positions */
OCIBindByPos (stmtp1,&bindp1[0],errhp,1,(dvoid *)&number[0],
     sizeof(number[0]),SQLT_NUM,(dvoid *)0, (ub2 *)0,(ub2 *)0,
            0,(ub4 *)0,OCI_DEFAULT);
OCIBindByPos (stmtp1,&bindp1[1],errhp,2,(dvoid *)&grade[0], 
     sizeof(grade[0],SQLT_CHR,(dvoid *)0, (ub2 *)0,(ub2 *)0,0,
            (ub4 *)0,OCI_DEFAULT);
/* execute the array INSERT */
OCIStmtExecute (svchp,stmtp1,errhp,5,0,0,0,OCI_BATCH_ERRORS);
/* get the number of errors */
OCIAttrGet (stmtp1, OCI_HTYPE_STMT, &num_errs, 0,
            OCI_ATTR_NUM_DML_ERRORS, errhp);
if (num_errs) {
   /* The user can do one of two things: 1) Allocate as many  */
   /* error handles as number of errors and free all handles  */
   /* at a later time; or 2) Allocate one err handle and reuse */
   /* the same handle for all the errors */
   OCIError *errhndl[num_errs];
   for (i = 0; i < num_errs; i++) {
      OCIParamGet(errhp, OCI_HTYPE_ERROR, errhp, &errhndl[i], i);
      OCIAttrGet (errhndl[i], OCI_HTYPE_ERROR, &row_off[i], 0, 
                  OCI_ATTR_DML_ROW_OFFSET, errhp);
      /* get server diagnostics */
      OCIErrorGet (..., errhndl[i], ...); 
      }
   }
/* make corrections to bind data */
OCIBindByPos (stmtp2,&bindp2[0],errhp,1,(dvoid *)0,0,SQLT_NUM, 
     (dvoid *)0, (ub2 *)0,(ub2 *)0,0,(ub4 *)0,OCI_DATA_AT_EXEC);
OCIBindByPos (stmtp2,&bindp2[1],errhp,2,(dvoid *)0,0,SQLT_DAT, 
     (dvoid *)0, (ub2 *)0,(ub2 *)0,0,(ub4 *)0,OCI_DATA_AT_EXEC);
/* register the callback for each bind handle */
OCIBindDynamic (bindp2[0],errhp,row_off,my_callback,0,0);
OCIBindDynamic (bindp2[1],errhp,row_off,my_callback,0,0);
/* execute the UPDATE statement */
OCIStmtExecute (svchp,stmtp2,errhp,2,0,0,0,OCI_BATCH_ERRORS);
...

In this example, OCIBindDynamic() is used with a callback because the user does not know at compile time what rows will return with errors. With a callback, you can simply pass the erroneous row numbers, stored in row_off, through the callback context and send only those rows that need to be updated or corrected. The same bind buffers can be shared between the INSERT and the UPDATE executes.

Describing Select-List Items

If your OCI application is processing a query, you may need to obtain more information about the items in the select-list. This is particularly true for dynamic queries whose contents are not known until run time. In this case, the program may need to obtain information about the datatypes and column lengths of the select-list items. This information is necessary to define output variables that will receive query results.

For example, a user might enter a query such as

SELECT * FROM employees

where the program has no prior information about the columns in the employees table.

There are two types of describes available: implicit and explicit. An implicit describe is one which does not require any special calls to retrieve describe information from the server although special calls are necessary to access the information. An explicit describe is one which requires the application to call a particular function to bring the describe information from the server.

An application may describe a select-list (query) either implicitly or explicitly. Other schema elements must be described explicitly.

An implicit describe allows an application to obtain select-list information as an attribute of the statement handle after a statement has been executed without making a specific describe call. It is called implicit, because no describe call is required. The describe information comes free with the execute.

You can describe a query explicitly prior to execution. To do this, specify OCI_DESCRIBE_ONLY as the mode of OCIStmtExecute(). Calling OCIStmtExecute() in this mode does not execute the statement, but it does return the select-list description. For performance reasons, however, it is recommended that applications take advantage of the implicit describe that comes free with a standard statement execution.

An explicit describe with the OCIDescribeAny() call obtains information about schema objects rather than select-lists.

In all cases, the specific information about columns and datatypes is retrieved by reading handle attributes.

Implicit Describe

After a SQL statement is executed, information about the select-list is available as an attribute of the statement handle. No explicit describe call is needed.

To retrieve information about select-list items from the statement handle, the application must call OCIParamGet() once for each position in the select-list to allocate a parameter descriptor for that position. Select-list positions are 1-based, meaning that the first item in the select-list is considered to be position number 1.

To retrieve information about multiple select-list items, an application can call OCIParamGet() with the pos parameter set to 1 the first time, and then iterate the value of pos and repeat the OCIParamGet() call until OCI_ERROR with ORA-24334 is returned. An application could also specify any position n to get a column at random.

Once a parameter descriptor has been allocated for a position in the select-list, the application can retrieve specific information by calling OCIAttrGet() on the parameter descriptor. Information available from the parameter descriptor includes the datatype and maximum size of the parameter.

The following sample code shows a loop that retrieves the column names and data types corresponding to a query following query execution. The query was associated with the statement handle by a prior call to OCIStmtPrepare().

OCIParam     *mypard;
ub4          counter;
ub2          dtype;
text         *col_name;
ub4          col_name_len;
sb4          parm_status;
...
/* Request a parameter descriptor for position 1 in the select-list */
counter = 1;
parm_status = OCIParamGet(stmthp, OCI_HTYPE_STMT, errhp, &mypard,
               (ub4) counter);
/* Loop only if a descriptor was successfully retrieved for
   current position, starting at 1 */
while (parm_status==OCI_SUCCESS) {
/* Retrieve the data type attribute */
checkerr(errhp, OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM, 
                 (dvoid*) &dtype,(ub4 *) 0, (ub4) OCI_ATTR_DATA_TYPE, 
                (OCIError *) errhp  ));
/* Retrieve the column name attribute */
checkerr(errhp, OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM, 
       (dvoid**) &col_name,(ub4 *) &col_name_len, (ub4) OCI_ATTR_NAME, 
       (OCIError *) errhp ));
printf("column=%s  datatype=%d\n\n", col_name, dtype);
fflush(stdout);
/* increment counter and get next descriptor, if there is one */
counter++;
parm_status = OCIParamGet(stmthp, OCI_HTYPE_STMT, errhp, &mypard,
             (ub4) counter);
}

The checkerr() function is used for error handling. The complete listing can be found in the first sample application in Appendix B, "OCI Demonstration Programs".

The calls to OCIAttrGet() and OCIParamGet() are local calls that do not require a network round trip, because all of the select-list information is cached on the client side after the statement is executed.

Explicit Describe of Queries

You can describe a query explicitly prior to execution. To do this, specify OCI_DESCRIBE_ONLY as the mode of OCIStmtExecute(). Calling OCIStmtExecute() in this mode does not execute the statement, but it does return the select-list description.

The following short example demonstrates the use of this mechanism to perform an explicit describe of a select-list to return information about the columns in the select-list. This pseudo-code shows how to retrieve column information (for example, data type).

/* initialize svchp, stmhp, errhp, rowoff, iters, snap_in, snap_out */
/* set the execution mode to OCI_DESCRIBE_ONLY. Note that setting the mode to 
OCI_DEFAULT does an implicit describe of the statement in addition to executing 
the statement */

OCIParam *colhd;   /* column handle */
checkerr(errhp, OCIStmtExecute(svchp, stmhp, errhp, iters, rowoff, 
        snap_in, snap_out, OCI_DESCRIBE_ONLY);

/* Get the number of columns in the query */
checkerr(errhp, OCIAttrGet(stmhp, OCI_HTYPE_STMT, &numcols, 
                      0, OCI_ATTR_PARAM_COUNT, errh));

/* go through the column list and retrieve the data type of each column. We 
start from pos = 1 */
for (i = 1; i <= numcols; i++)
{
  /* get parameter for column i */
  checkerr(errhp, OCIParamGet(stmhp, OCI_HTYPE_STMT, errh, &colhd, i));

  /* get data-type of column i */
  checkerr(errhp, OCIAttrGet(colhd, OCI_DTYPE_PARAM,
          &type[i-1], 0, OCI_ATTR_DATA_TYPE, errh));
}

What is Defining?

Query statements return data from the database to your application. When processing a query, you must define an output variable or an array of output variables for each item in the select-list from which you want to retrieve data. The define step creates an association which determines where returned results are stored, and in what format.

For example, if your OCI statement processes the following statement:

SELECT name, ssn FROM employees
    WHERE empno = :empnum

you would normally need to define two output variables, one to receive the value returned from the name column, and one to receive the value returned from the ssn column.

Fetching Results

If an OCI application has processed a query, it is typically necessary to fetch the results with OCIStmtFetch() or with OCIStmtFetch2() after the statement has been executed. Oracle encourages the use of OCIStmtFetch2(), which supports scrollable cursors, and will be enhanced.

Fetched data is retrieved into output variables that have been specified by define operations.

Fetching LOB Data

If LOB columns or attributes are part of a select-list, they can be returned as LOB locators or actual LOB values, depending on how the user has defined them. If LOB locators are fetched, then the application can perform further operations on these locators through the OCILob* interfaces.

Setting Prefetch Count

In order to minimize server round trips and maximize the performance of applications, the OCI can prefetch result set rows when executing a query. The OCI programmer can customize this prefetching by setting the OCI_ATTR_PREFETCH_ROWS or OCI_ATTR_PREFETCH_MEMORY attribute of the statement handle using the OCIAttrSet() function. The attributes are used as follows:

• OCI_ATTR_PREFETCH_ROWS sets the number of rows to be prefetched.
• OCI_ATTR_PREFETCH_MEMORY sets the memory allocated for rows to be prefetched. The application then fetches as many rows as will fit into that much memory.

When both of these attributes are set, the OCI prefetches rows up to the OCI_ATTR_PREFETCH_ROWS limit unless the OCI_ATTR_PREFETCH_MEMORY limit is reached, in which case the OCI returns as many rows as will fit in a buffer of size OCI_ATTR_PREFETCH_MEMORY.

By default, prefetching is turned on, and the OCI fetches an extra row all the time. To turn prefetching off, set both the OCI_ATTR_PREFETCH_ROWS and OCI_ATTR_PREFETCH_MEMORY attributes to zero.

Scrollable Cursors

A cursor is a database query and its result set. Execution of a cursor puts the results of the query into a set of rows called the result set. The result set can be fetched either sequentially or non-sequentially. The latter case is known as a scrollable cursor.

A scrollable cursor provides support for forward and backward access into the result set from a given position, using either absolute or relative row number offsets into the result set.

Rows are numbered starting at one. For a scrollable cursor, you can fetch previously-fetched rows, the n-th row in the result set, or the n-th row from the current position. Client-side caching of either the partial or entire result set means fewer calls to the server, thus improving performance.

Oracle does not support DML operations on scrollable cursors. A cursor cannot be made scrollable if the LONG datatype is part of the select list.

Moreover, fetches from a scrollable statement handle are based on the snapshot at execution time. The size of the client cache can be controlled by the existing OCI attributes OCI_ATTR_PREFETCH_ROWS and OCI_ATTR_PREFETCH_MEMORY.

Support for Scrollable Cursors in OCI

The OCIStmtExecute() call has an execution mode for scrollable cursors, OCI_STMT_SCROLLABLE_READONLY. The default for statement handles is non-scrollable, that is, forward sequential access only (where the mode is OCI_FETCH_NEXT). You must set this execution mode each time the statement handle is executed.

The statement handle attribute OCI_ATTR_CURRENT_POSITION can be retrieved only using OCIAttrGet(). This attribute cannot be set by the application. This indicates the current position in the result set.

For non-scrollable cursors, OCI_ATTR_ROW_COUNT is the total number of rows fetched into user buffers with the OCIStmtFetch2() calls issued since this statement handle was executed. Since they are forward sequential only, this also represents the highest row number seen by the application.

For scrollable cursors, OCI_ATTR_ROW_COUNT will represent the maximum (absolute) row number fetched into the user buffers. Since the application can arbitrarily position the fetches, this need not be the total number of rows fetched into the user's buffers since the (scrollable) statement was executed.

The attribute OCI_ATTR_ROWS_FETCHED on the statement handle, represents the number of rows that were successfully fetched into the user's buffers in the last fetch call or execute. It works for both scrollable and non-scrollable cursors.

Use the call OCIStmtFetch2(), which is replacing the call OCIStmtFetch(), which is retained for backward compatibility. You are encouraged to use OCIStmtFetch2() instead, for all new applications, even those not using scrollable cursors. This call also works for non-scrollable cursor, but then an error will be raised if any other orientation besides OCI_DEFAULT or OCI_FETCH_NEXT is passed.

Increasing Scrollable Cursor Performance

Response time is improved if you use OCI client-side prefetch buffers.

After calling OCIStmtExecute() for a scrollable cursor, call OCIStmtFetch2() using OCI_FETCH_LAST to obtain the size of the result set. Then set OCI_ATTR_PREFETCH_ROWS to about 20% of that size, and set OCI_PREFETCH_MEMORY if the result set uses a large amount of memory.

Limitations on the Use of Scrollable Cursors

Failover does not work with scrollable cursors.

Remote mapped queries cannot be used with scrollable cursors.

Example of Access on a Scrollable Cursor

Assume that a result set is returned by the SQL query:

SELECT empno, ename FROM emp

and that the table EMP has 14 rows. One usage of scrollable cursors is:

...
/* execute the scrollable cursor in the scrollable mode */
OCIStmtExecute(svchp, stmthp, errhp, (ub4) 0, (ub4)
               0,  (CONST OCISnapshot *)NULL, (OCISnapshot *) NULL,
               OCI_STMT_SCROLLABLE_READONLY ); 
                                  
/* Fetches rows with absolute row numbers 6, 7, 8. After this call,
 OCI_ATTR_CURRENT_POSITION = 8, OCI_ATTR_ROW_COUNT = 8 */
checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 3, 
                               OCI_FETCH_ABSOLUTE, (sb4) 6, OCI_DEFAULT);
                               
/* Fetches rows with absolute row numbers 6, 7, 8. After this call,
 OCI_ATTR_CURRENT_POSITION = 8, OCI_ATTR_ROW_COUNT = 8 */
checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 3, 
                               OCI_FETCH_RELATIVE, (sb4) -2, OCI_DEFAULT);
                               
/* Fetches rows with absolute row numbers 14. After this call,
 OCI_ATTR_CURRENT_POSITION = 14, OCI_ATTR_ROW_COUNT = 14 */
checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1, 
                               OCI_FETCH_LAST, (sb4) 0, OCI_DEFAULT);

/* Fetches rows with absolute row number 1. After this call,
 OCI_ATTR_CURRENT_POSITION = 1, OCI_ATTR_ROW_COUNT = 14 */
checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1, 
                               OCI_FETCH_FIRST, (sb4) 0, OCI_DEFAULT);

/* Fetches rows with absolute row numbers 2, 3, 4. After this call,
 OCI_ATTR_CURRENT_POSITION = 4, OCI_ATTR_ROW_COUNT = 14 */
checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 3, 
                               OCI_FETCH_NEXT, (sb4) 0, OCI_DEFAULT);

/* Fetches rows with absolute row numbers 3,4,5,6,7. After this call,
 OCI_ATTR_CURRENT_POSITION = 7, OCI_ATTR_ROW_COUNT = 14. It is assumed 
the user's define memory is allocated. */
checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 5, 
                               OCI_FETCH_PRIOR, (sb4) 0, OCI_DEFAULT);
...
}
checkprint (errhp, status)
{
 ub4 rows_fetched;
 checkerr (errhp, status);
 checkerr(errhp, OCIAttrGet((CONST void *) stmthp, OCI_HTYPE_STMT,
         (void *) &rows_fetched, (uint *) 0, OCI_ATTR_ROWS_FETCHED, errhp));
}
...