Cursors of the database in Oracle 11g

Database cursor is a name for a specific private SQL area in which information for processing the specific statement is kept.


PL/SQL uses both implicit and explicit cursors. PL/SQL implicitly declares a cursor for all SQL data manipulation statements on a set of rows, including queries that return only one row. If we want precise control over query processing, we can declare an explicit cursor. We must declare an explicit cursor for queries that return more than one row. Cursor variables (REF cursors) are like pointers to result sets. We use them when we want to perform a query in one subprogram, and process the results in a different subprogram (possibly one written in a different language).

Using dynamic SQL, we can build SQL statements dynamically at runtime and we can create more general purpose, flexible applications because the full text of a SQL statement may be unknown at compilation. We can use “old” package DBMS_SQL or “new” native dynamic SQL. In Oracle 11g we can use new features DBMS_SQL.TO_CURSOR_NUMBER and DBMS_SQL.TO_REFCURSOR to convert a REF cursor to DBMS_SQL cursor and vice versa.