Establishing a Cursor

Once a connection is established, the next step is to open a cursor for your SQL statements. Acursor is a
connection to a specific area in the Program Global Area (PGA) that contains information about a specific
SQL statement.

The cursor acts as the intermediary between the SQL statements your application submits and the actions
of the Oracle database as a result of those statements. The cursor holds information about the current state
of the SQL statement, such as the parsed version of the SQL statement. For statements that return multiple
rows of data, the cursor keeps track of where you are in terms of returning rows from the result set. This
important piece of information is also called a cursor, in that the cursor for a particular result set is the
pointer to the current row. But the cursor exists before any rows have been returned for a query, which is
why advancing the cursor to the next row at the start of retrieving data places the cursor on the first row
in the result set.
You don’t necessarily have to explicitly open a cursor to execute an SQL statement, because the Oracle
database can automatically take care of this for you. As with most aspects of application programming,
explicit control over cursor creation and use will give you more options for implementing your logic effi-
ciently. You may want to open more than one cursor to improve the operation of your application. For
instance, if you have an SQL statement that is repeatedly used, you might want to open a cursor for the
statement so that Oracle will not have to re-parse the statement. Of course, the Oracle database uses its
own internal caches to hold parsed versions of SQL statements also, so creation of individual cursors
may not be necessary for optimal performance in many cases.