OCIStmtFetch2()

Purpose

This fetches a row from the (scrollable) result set. You are encouraged to use this fetch call instead of the deprecated call OCIStmtFetch().

Syntax

sword OCIStmtFetch2 ( OCIStmt     *stmthp,
                      OCIError    *errhp, 
                      ub4         nrows,
                      ub2         orientation,
                      sb4         fetchOffset,
                      ub4         mode );

Parameters

stmthp (IN/OUT)

This is the statement handle of the (scrollable) result set.

errhp (IN/OUT)

An error handle you can pass to OCIErrorGet() for diagnostic information in event of an error.

nrows (IN)

Number of rows to be fetched from the current position.

orientation (IN)

The acceptable values are:

• OCI_DEFAULT - has the same effect as OCI_FETCH_NEXT
• OCI_FETCH_CURRENT - gets the current row.
• OCI_FETCH_NEXT - gets the next row from the current position. It is the default (has the same effect as OCI_DEFAULT). Use for a non-scrollable statement handle.
• OCI_FETCH_FIRST - gets the first row in the result set.
• OCI_FETCH_LAST - gets the last row in the result set.
• OCI_FETCH_PRIOR - gets the previous row from the current row in the result set.
• OCI_FETCH_ABSOLUTE will fetch the row number (specified by fetchOffset parameter) in the result set using absolute positioning.
• OCI_FETCH_RELATIVE will fetch the row number (specified by fetchOffset parameter) in the result set using relative positioning.

fetchOffset (IN)

The offset to be used with the orientation parameter for changing the current row position.

mode (IN)

Pass in OCI_DEFAULT.

Comments

The fetch call works similar to the OCIStmtFetch() call with the addition of the fetchOffset parameter. It can be used on any statement handle, whether it is scrollable or not. For a non-scrollable statement handle, the only acceptable value of orientation is OCI_FETCH_NEXT, and the fetchOffset parameter will be ignored. For new applications you are encouraged to use this new call, OCIStmtFetch2(). A fetchOffset with orientation set to OCI_FETCH_RELATIVE is equivalent to all of the following:

• OCI_FETCH_CURRENT with a value of fetchOffset equal to 0,
• OCI_FETCH_NEXT with a value of fetchOffset equal to 1,
• OCI_FETCH_PRIOR with a value of fetchOffset equal to -1.

OCI_ATTR_ROW_COUNT contains the highest absolute row value that was fetched.

All other orientation modes besides OCI_FETCH_ABSOLUTE and OCI_FETCH_RELATIVE will ignore the fetchOffset value.

This call can also be used to find out the number of rows in the result set by using OCI_FETCH_LAST, and then calling OCIAttrGet() on OCI_ATTR_CURRENT_POSITION. But the response time of this call can be high.

The return codes are the same as for OCIStmtFetch(), except that OER(1403) with return code OCI_NO_DATA will be returned every time a fetch on a scrollable statement handle (or execute) is made and not all rows requested by the application could be fetched.

The scrollable statement handle will need to be explicitly cancelled (that is, fetch with 0 rows) or freed in order to release server-side resources for this scrollable cursor. A non-scrollable statement handle is implicitly cancelled on receiving the OER(1403).

Use OCI_ATTR_ROWS_FETCHED to find the number of rows that were successfully fetched into the user's buffers in the last fetch call.

Related Functions

OCIStmtExecute(), OCIBindByPos()