Data Management

On Variables, Moving, and Meaning

Download Now Free registration required

Executive Summary

A cursor variable is a PL/SQL variable that points to a result set. One can fetch the rows of a cursor variable's result set. One can also use BULK COLLECT against a cursor variable to deposit into a collection all the rows identified by the result set. The collection can be referenced in a FORALL INSERT statement. This paper explains the kind of code required to achieve the goals. It also explains how one can move back and forth within a cursor result set, comparing the contents of distinct rows. The result should also be read from the end to the beginning. Can this be done in a PL/SQL program. Oracle does not currently support bidirectional access to cursor result sets (aka scrollable cursors) through PL/SQL interface. A combination of variables is used. The paper explains five offerings of bidirectional cursor processing built around a collection of records with the same structure. It also addresses the security issue in case of errors. When an error is encountered, gather as much information as possible about the application and system context (by calling, for example, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE) and store it with the unique identifier. Provide this instance identifier to the user, for communication with the Support. Support then has access to a rich array of information, which it can use to fix the problem.

  • Format: HTML
  • Size: 0 KB