Generic selectors
Exact matches only
Search in title
Search in content
Post Type Selectors

Oracle cursor

What is Cursor ?

Cursor is a pointer in memory area called context area.

What is context area ?

Context area is a memory area inside the Process Global Area (PGA) which helps Oracle server in processing an SQL statement by holding the important information about the statement.

This information include:

  1. Rows returned by a query.
  2. Number of rows processes by a query.
  3. A pointer to the parsed query in the shared pool.

Using cursor we can control the context area as it is a pointer to the same.

Types of cursors:

  1. Implicit cursors
  2. Explicit cursors

1. Implicit cursors:

  1. Automatically gets created by Oracle server when DML SQL statement gets executed.
  2. Users can not control the behavior of these cursors.
  3. Created in background for any PL/SQL block which executes an SQL statement. 

2. Explicit cursors:

  1. User defined cursors which means user has to create this cursor for any statement.
  2. Unlike implicit cursor user has full control over explicit cursor.

Create an explicit cursor:

This can be done in 4 steps-

  1. Declare
  2. Open
  3. Fetch
  4. Close

In case of implicit cursor Oracle performs all these steps automatically for us.

1. Declare cursor: 

Declaring a cursor means initializing a cursor into memory.


CURSOR cursor_name IS select_statement;

2. Open cursor:

  • In order to put the cursor at work we have to open it first.
  • When we open the cursor the memory will be allotted to it., and it set put to the next step which is fetching the data from it.


OPEN cursor_name;

3. Fetch cursor:

The process of retrieving the data from the cursor is known as fetching the cursor.


FETCH cursor_name INTO PL/SQL variable;OrFETCH cursor_name INTO PL/SQL record;

4. Close cursor:

Closing statement of a cursor will releases all the resources associated with it.


CLOSE cursor_name;

Example of a Cursor:

CURSOR cursor_name IS select_statement;
OPEN cursor_name;
FETCH cursor_name INTO PL/SQL variable;
CLOSE cursor_name;