OCI Scrollable Cursor provides support for
forward and backward access into the result set from a given
current position, using either absolute or relative row number
offsets into the result set.
The main benefits of the OCI Scrollable Cursor
feature are:
Forward
and backward scrolling - The Scrollable Cursor
can be set to move in both forward and backward orientation
through the result set.
Positioning
to absolute and relative cursor position -
When positioning the scrollable cursor, an absolute position
within the result set can be indicated; rows in the result
set are numbered from 1 to n; positioning the cursor to
a row outside the result set will result in an error message.
The cursor can also be positioned using a relative offset
based on the current cursor position in the result set;
positioning the cursor to a row outside the result set
will result in an error message. Short forms are provided
to position the cursor to the next position, the
previous position, the first and to the
last position in the result set.
Recovery
from positioning error
- In contrast to the default non-scrollable cursor,
positioning a scrollable cursor outside the bounds of
the result set will not cancel the statement; an error
message is issued to the calling application, but the
next fetch call will be processed accordingly.
Using
OCI pre-fetching and compression - There is
better response time and less memory usage on the client-side
due to OCI’s pre-fetching and compression capabilities.
Minimizing
server round-trips - With OCI Scrollable Cursor,
the number of round-trips to the server is minimal; both
the individual rows in a result set and the total size
of the result set can be retrieved with one round-trip.
Providing
client-side caching - The caching capability
for storing the result set is part of the OCI API. This
means that the application does not have to make provisions
for memory allocation, cursor navigation and house keeping.
This increases the application / mid-tier scalability
using existing attributes
Scalability
through disk-backed storage of result set
- The result set for a query done with an OCI Scrollable
Cursor is transparently managed on the server side. Depending
on available memory resources rows will automatically
be saved on disk. This means that a given scrollable cursor
set-up will scale regardless of the configured memory
resources on the server.
Read-consistency
by using snapshot-based row fetch - Whenever
a Scrollable Cursor is opened, a statement handle is
returned from the OCI interface which points to the
result set. This statement handle represents a pointer
to a snapshot view of the rows making up the result
set. Applications using this statement handle will also
use the same snapshot view, thus guaranteeing read-consistency
across multiple applications and multiple users.
Bookmark
