Oracle Cursor


A cursor is a pointer that leads to a query’s result. There are two sorts of cursors in PL/SQL: implicit and explicit cursors.


What is a cursor in Oracle

In PL/SQL, cursors are used. For each SQL statement, Oracle constructs a memory region known as the context area, which holds all the information needed to process the query, for example, the number of rows processed, etc.

This context area is indicated by a cursor. A cursor is used by PL/SQL to control the context region. The rows (one or more) returned by a SQL statement are stored in a cursor. The active set refers to the set of rows that the cursor is now holding.


Types of Cursors

A cursor can be given a name so that it can be used in a program to retrieve and process the rows of a SQL statement one by one. Cursors are divided into two categories.

1) Explicit cursors

A SELECT statement that is declared explicitly in the current block’s declaration section or a package specification is known as an explicit cursor.

From OPEN, FETCH, and CLOSE, you can manage the execution cycle of an explicit cursor.

Oracle specifies an execution cycle in which a SQL statement is executed, and a cursor is associated with it.


2) Implicit cursor

Oracle creates an implicit cursor whenever it executes a SQL command like SELECT INTO, INSERT, UPDATE, or DELETE.

When the query returns zero or numerous rows, the implicit cursor is inefficient, resulting in the NO DATA FOUND or TOO MANY ROWS exceptions.

How to declare a cursor in Oracle

Declaring the cursor offers it a name and the SELECT statement that goes with it.

For example:


How to use cursor in a function

Let’s take an example to understand how to use cursor in a function, Consider there are two tables Orders and Order_Items
















The sentence below creates a view that returns customer sales revenues:


SELECT customer_id,

       SUM(unit_price * quantity) total,

       ROUND(SUM(unit_price * quantity) * 0.05) credit

FROM order_items

INNER JOIN orders USING (order_id)

WHERE status = 'Shipped'

GROUP BY customer_id;


How to open a cursor

The cursor’s memory is allocated when it is opened, and it is ready to receive the rows produced by the SQL statement. For instance, let’s open the above-mentioned cursor as follows:

OPEN c_employees


How to fetch rows from a cursor

By accessing one row at a time, the cursor may be found. For instance, we can get rows from the above-opened by executing the following query:

FETCH c_employees INTO c_id,c_name,c_addr;


How to close a cursor

When the cursor is closed, the allocated memory is released. For example, the above-opened cursor will be closed as follows:

CLOSE c_employees;


Cursor within cursor

Let’s see an example:

create or replace procedure MULTIPLE_CURSORS_PROC is

               v_owner varchar2(40);

               v_table_name varchar2(40);

               v_column_name varchar2(100);

cursor firstCursor is

select distinct tbl.owner, tbl.table_name

from all_tables tbl

where tbl.owner = 'SYSTEM';

cursor secondCursor is

select distinct col.column_name

from all_tab_columns col

where col.owner = v_owner

and col.table_name = v_table_name;


open firstCursor;


fetch firstCursor into v_owner, v_table_name;

dbms_output.put_line('v_owner: '||v_owner);

dbms_output.put_line('v_table_name: '||v_table_name);

open secondCursor;


fetch secondCursor into v_column_name;

dbms_output.put_line('v_column_name: '||v_column_name);

end loop;

close secondCursor;

end loop;

close firstCursor;






  • Barry Allen

    A Full Stack Developer with 10+ years of experience in different domain including SAP, Blockchain, AI and Web Development.

    View all posts


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.