One of the most common tasks of an OCI program is to accept and process SQL statements.

Chapter 3 “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.

This section outlines the specific steps involved in accepting and processing SQL statements.

Once you have allocated the necessary handles and connected to an Oracle database, follow the steps illustrated in Figure 6-1.

Figure 6-1 Steps in Processing SQL Statements

Description of "Figure 6-1 Steps in Processing SQL Statements"

1. Prepare the statement. Define an application request using OCIStmtPrepare2(). OCIStmtPrepare2() is an enhanced version of OCIStmtPrepare() that was introduced to support statement caching. Beginning with Oracle Database 12c Release 2 (12.2), OCIStmtPrepare() is deprecated.

2. Bind placeholders, if necessary. For DML statements and queries with input variables, perform one or more of the following bind calls to bind the address of each input variable (or PL/SQL output variable) or array to each placeholder in the statement.

   • OCIBindByPos2() or OCIBindByPos()

   • OCIBindByName2() or OCIBindByName()

   • OCIBindObject()

   • OCIBindDynamic()

   • OCIBindArrayOfStruct()

3. Execute the statement by calling OCIStmtExecute(). For DDL statements, no further steps are necessary.

4. Describe the select-list items, if necessary, usingOCIParamGet() and OCIAttrGet(). This is optional step is not required if the number of select-list items and the attributes of each item (such as its length and data type) are known at compile time.

5. Define output variables, if necessary. For queries, perform one or more define calls to OCIDefineByPos2() or 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 did this when you bound the data.

6. Fetch the results of the query, if necessary, by calling OCIStmtFetch2().

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

The following sections describe each step in detail.

Additional steps beyond those listed earlier may be required if your application must do any of the following:

• Initiate and manage multiple transactions

• Manage multiple threads of execution

• Perform piecewise inserts, updates, or fetches

SQL and PL/SQL statements are prepared for execution by using the statement prepare call and any necessary bind calls.

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 OCIStmtPrepare2() call and passes to this call a previously allocated statement handle. This is a completely local call, requiring no round-trip to the server. No association is made between the statement and a particular server at this point.

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

This section includes the following topic: About Using Prepared Statements on Multiple Servers

Most DML statements, and some queries (such as those with a WHERE clause), require a program to pass data to Oracle Database as part of a SQL or PL/SQL statement.

This data can be constant or literal, 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 must 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 in a 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 a SQL statement or PL/SQL block, you must call an OCI routine that binds the address of a variable in your program to that placeholder. When the statement executes, the database gets the data that your program placed in the input variables or bind variables and passes it to the server with the SQL statement.

Binding is used for both input and output variables in nonquery operations. In Example 6-1, the variables empno_out, ename_out, job_out, sal_out, and deptno_out should be bound. These are outbinds (as opposed to regular inbinds).

INSERT INTO emp VALUES
         (:empno, :ename, :job, :sal, :deptno)
      RETURNING
         (empno, ename, job, sal, deptno)
      INTO
         (:empno_out, :ename_out, :job_out, :sal_out, :deptno_out)

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

When an OCI application executes a query, it receives from the Oracle database data that matches the query specifications. Within the database, the data is stored in Oracle-defined formats. When the results are returned, the 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 nonqueries, the number of times the statement is executed during array operations equals 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 are inserted in a single execute call when rowoff is zero. If rowoff is set to 2, only 8 items are inserted.

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 data types and column lengths of the select-list items. This information is necessary to define output variables that may receive query results.

For example, consider a query where the program has no prior information about the columns in the employees table:

SELECT * FROM employees

There are two types of describes available: implicit and explicit.

An implicit describe does not require any special calls to retrieve describe information from the server, although special calls are necessary to access the information. 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 statement execution.

An explicit describe 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.

You can describe a query explicitly before execution by specifying OCI_DESCRIBE_ONLY as the mode of OCIStmtExecute(), which does not execute the statement, but returns the select-list description. For performance reasons, Oracle recommends that applications use the implicit describe, which 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 data types is retrieved by reading handle attributes.

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 to retrieve data. The define step creates an association that determines where returned results are stored, and in what format.

For example, to process the following statement you would normally 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:

SELECT name, ssn FROM employees
    WHERE empno = :empnum

If an OCI application has processed a query, it is typically necessary to fetch the results with OCIStmtFetch2() after the statement has completed execution.

The OCIStmtFetch2() function supports scrollable cursors.

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

A cursor is a current position in a result set.

Execution of a cursor puts the results of the query into a set of rows called the result set that can be fetched either sequentially or nonsequentially. In the latter case, the cursor is known as a scrollable cursor.

A scrollable cursor supports forward and backward access into the result set from a given position, by 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 nth row in the result set, or the nth row from the current position. Client-side caching of either the partial or entire result set improves performance by limiting calls to the server.

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

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

The OCIStmtExecute() call has an execution mode for scrollable cursors, OCI_STMT_SCROLLABLE_READONLY. The default for statement handles is nonscrollable, 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 by using OCIAttrGet(). This attribute cannot be set by the application; it indicates the current position in the result set.

For nonscrollable cursors, OCI_ATTR_ROW_COUNT is the total number of rows fetched into the user buffers with the OCIStmtFetch2() calls since this statement handle was executed. Because nonscrollable cursors are forward sequential only, OCI_ATTR_ROW_COUNT also represents the highest row number detected by the application.

Beginning with Oracle Database Release 12.1, using the attribute OCI_ATTR_UB8_ROW_COUNT is preferred to using the attribute OCI_ATTR_ROW_COUNT if row count values can exceed the value of UB4MAXVAL for an OCI application.

For scrollable cursors, OCI_ATTR_ROW_COUNT represents the maximum (absolute) row number fetched into the user buffers. Because the application can arbitrarily position the fetches, this does not have to 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 nonscrollable cursors.

Use the OCIStmtFetch2() call, instead of the OCIStmtFetch() call, which is retained for backward compatibility. You are encouraged to use OCIStmtFetch2() for all new applications, even those not using scrollable cursors. This call also works for nonscrollable cursors, but can raise an error if any other orientation besides OCI_DEFAULT or OCI_FETCH_NEXT is passed.

Scrollable cursors are supported for remote mapped queries. Transparent application failover (TAF) is supported for scrollable cursors.