6.5 Opening Cursors

The first step in using a cursor is to define it in the declaration section. The next step you must perform before you try to extract or fetch records from a cursor is to open that cursor.

The syntax for the OPEN statement is simplicity itself:

OPEN <cursor_name> [ ( argument [, argument ...] ) ];

where <cursor_name> is the name of the cursor you declared and the arguments are the values to be passed if the cursor was declared with a parameter list.

When you open a cursor, PL/SQL executes the query for that cursor. It also identifies the active set of data -- that is, the rows from all involved tables that meet the criteria in the WHERE clause and join conditions. The OPEN does not itself actually retrieve any of these rows -- that action is performed by the FETCH statement.

Regardless of when you perform the first fetch, however, the read consistency model in the Oracle RDBMS guarantees that all fetches will reflect the data as it existed when the cursor was opened. In other words, from the moment you open your cursor until the moment that cursor is closed, all data fetched through the cursor will ignore any inserts, updates, and deletes performed after the cursor was opened.

Furthermore, if the SELECT statement in your cursor uses a FOR UPDATE clause, then, when the cursor is opened, all the rows identified by the query are locked. (This topic is covered in Section 6.11, "SELECT FOR UPDATE in Cursors" later in this chapter.)

You should open a cursor only if it has been closed or was never opened. If you try to open a cursor that is already open you will get the following error:

ORA-06511: PL/SQL: cursor already open

You can be sure of a cursor's status by checking the %ISOPEN cursor attribute before you try to open the cursor:

IF NOT company_cur%ISOPEN
THEN
   OPEN company_cur;
END IF;

Section 6.9, "Cursor Attributes" later in the chapter, explains the different cursor attributes and how to make best use of them in your programs.