Scrollable cursors

Solution

By default, the Oracle database driver uses native scrollable cursors by setting the OCI_STMT_SCROLLABLE_READONLY statement attribute.

However, if you experience problems with the native scrollable cursors provided by Oracle, you can turn on scrollable cursor emulation with the following FGLPROFILE entry:

dbi.database.dbname.ora.cursor.scroll.emul = true

When this FGLPROFILE setting is defined, the Oracle database driver emulates scrollable cursors with temporary files. On UNIX™, the temp files are created in the directory defined by the DBTEMP, TMPDIR, TEMP or TMP environment variables (the default is /tmp). On Windows™, the temp files are created with the _tempnam() MSVCRT API call.

Native scrollable cursors are normally handled by the database server (only pieces of the result-set are sent to the client application). With emulated scrollable cursors, when scrolling to the last row, all rows will be fetched into the temporary file. This can generate a lot of network traffic and can produce a large temporary file if the result-set contains a lot of rows. Additionally, programs are dependent on the file system resource allocated to the OS user (ulimit).

In case of a runtime system crash, the temporary files created for scrollable cursors are not removed automatically. Therefore, is it possible that you will find some unexpected files in the temp directory. Before removing such files, you must make sure that these files are no longer used by running processes. Recent operating systems take care of that, by removing unused temp files periodically.