When working with SQL in Oracle PL/SQL, queries often return multiple rows. To process these rows one at a time, Oracle provides a powerful mechanism called a Cursor.

This article explains what a cursor is, how it works, cursor attributes, different cursor types, and REF CURSORs, with easy-to-understand examples using the HR schema.

What Is a Cursor in Oracle PL/SQL?

A Cursor is a pointer-like variable that points to the result set returned by a SELECT statement.

In simple terms:

SQL retrieves rows all at once
PL/SQL processes rows one by one
Cursors act as the bridge between SQL and PL/SQL

Cursor Architecture – How Cursor Works Internally (Diagram)

Explicit Cursor Flow

+-------------+        +--------------+        +--------------+
|  SELECT     | -----> |   Result Set | -----> |   Cursor     |
|  Statement  |        |  (Active Set)|        |  Pointer     |
+-------------+        +--------------+        +--------------+
                                                     |
                                                     v
                                               FETCH ROW

How a Cursor Works (Lifecycle)

Declare the cursor (define the SELECT query)
Open the cursor (memory is allocated)
Fetch rows one by one into variables
Close the cursor (release memory)

DECLARE CURSOR
|
OPEN CURSOR –> Memory Allocated
|
FETCH ROWS –> One row at a time
|
%FOUND / %NOTFOUND
|
CLOSE CURSOR –> Memory Released

Important Cursor Concepts

When a cursor is opened, memory is allocated, but it points to the result set only after FETCH
The result set that the cursor processes is called the Active Set
Cursor always moves forward
Cursor cannot move backward
Until all rows are fetched, Oracle does not reuse the memory

Cursor Attributes (Cursor Properties)

Oracle provides built-in attributes to track cursor status:

Attribute	Type	Description
`%FOUND`	BOOLEAN	Returns TRUE if the last fetch returned a row
`%NOTFOUND`	BOOLEAN	Returns TRUE if no row was fetched
`%ISOPEN`	BOOLEAN	Returns TRUE if cursor is open
`%ROWCOUNT`	NUMBER	Returns number of rows fetched so far

Example 1: Simple Explicit Cursor

Requirement

Display employee ID and last name for employees working in department 30.

DECLARE
    CURSOR c_emp_cursor IS
        SELECT employee_id, last_name
        FROM employees
        WHERE department_id = 30;

    v_empno employees.employee_id%TYPE;
    v_lname employees.last_name%TYPE;
BEGIN
    OPEN c_emp_cursor;

    DBMS_OUTPUT.PUT_LINE('After open, row count: ' || c_emp_cursor%ROWCOUNT);

    FETCH c_emp_cursor INTO v_empno, v_lname;
    DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_lname);

    DBMS_OUTPUT.PUT_LINE('After first fetch, row count: ' || c_emp_cursor%ROWCOUNT);

    CLOSE c_emp_cursor;
END;
/

Explanation

%ROWCOUNT is 0 after OPEN
After first FETCH, %ROWCOUNT becomes 1
Cursor fetches one row at a time

Example 2: Cursor with LOOP

DECLARE
    CURSOR c1 IS
        SELECT employee_id, last_name, salary FROM employees;

    myc1 c1%ROWTYPE;
BEGIN
    OPEN c1;
    DBMS_OUTPUT.PUT_LINE('*********************************');

    LOOP
        FETCH c1 INTO myc1;
        EXIT WHEN c1%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE('Employee Id: ' || myc1.employee_id);
        DBMS_OUTPUT.PUT_LINE('Last Name: ' || myc1.last_name);
        DBMS_OUTPUT.PUT_LINE('Salary: ' || myc1.salary);
        DBMS_OUTPUT.PUT_LINE('*********************************');
    END LOOP;

    CLOSE c1;
END;
/

Key Notes

%ROWTYPE fetches an entire row
EXIT WHEN %NOTFOUND ensures clean loop termination

Example 3: Checking Cursor Attributes

DECLARE
    CURSOR c1 IS
        SELECT employee_id, last_name, salary FROM employees;

    myc1 c1%ROWTYPE;
BEGIN
    IF c1%ISOPEN THEN
        DBMS_OUTPUT.PUT_LINE('Cursor already open');
    ELSE
        OPEN c1;
        DBMS_OUTPUT.PUT_LINE('Cursor opened now');
    END IF;

    FETCH c1 INTO myc1;
    WHILE c1%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE('Row count: ' || c1%ROWCOUNT);
        DBMS_OUTPUT.PUT_LINE(myc1.employee_id || ' ' || myc1.last_name);
        FETCH c1 INTO myc1;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Total rows fetched: ' || c1%ROWCOUNT);
    CLOSE c1;
END;
/

Example 4: Cursor FOR LOOP (Best Practice)

Cursor FOR LOOP is simpler and safer.

Advantages

No OPEN
No FETCH
No EXIT condition
No CLOSE

DECLARE
    CURSOR c1 IS
        SELECT employee_id, last_name, salary FROM employees;
BEGIN
    FOR myc1 IN c1 LOOP
        DBMS_OUTPUT.PUT_LINE('Employee Id: ' || myc1.employee_id);
        DBMS_OUTPUT.PUT_LINE('Last Name: ' || myc1.last_name);
        DBMS_OUTPUT.PUT_LINE('Salary: ' || myc1.salary);
        DBMS_OUTPUT.PUT_LINE('*********************************');
    END LOOP;
END;
/

✅ Recommended for most cursor use cases

Example 5: Cursor with Parameters

When working with cursors in PL/SQL, you often want to reuse the same cursor logic but fetch different data each time.
That’s exactly where a Parameterized Cursor comes in.

What Is a Parameterized Cursor?

A Parameterized Cursor is a cursor that accepts input values (parameters) at runtime, just like a function or procedure.

👉 Instead of hardcoding values inside the cursor query, we pass them dynamically when opening the cursor.

In simple words

A parameterized cursor lets you run the same cursor query for different inputs without writing multiple cursors.

Why Do We Need Parameterized Cursors?

Without parameters:

You must write multiple cursors
Code becomes repetitive
Maintenance becomes harder

With parameters:

One cursor → many inputs
Cleaner and reusable code
Better readability

Basic Syntax of Parameterized Cursor

CURSOR cursor_name (parameter_name datatype) IS
    SELECT column_list
    FROM table_name
    WHERE column_name = parameter_name;

Opening a Parameterized Cursor

OPEN cursor_name(value);

DECLARE
    CURSOR c_emp_cursor (p_dept NUMBER) IS
        SELECT employee_id, last_name
        FROM employees
        WHERE department_id = p_dept;

    v_empno employees.employee_id%TYPE;
    v_lname employees.last_name%TYPE;
BEGIN
    OPEN c_emp_cursor(20);

    LOOP
        FETCH c_emp_cursor INTO v_empno, v_lname;
        EXIT WHEN c_emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_lname);
    END LOOP;

    CLOSE c_emp_cursor;
END;
/

Parameterized Cursor with Cursor FOR LOOP (Best Practice)

This is the cleanest and safest approach.

DECLARE
    CURSOR c_emp (p_dept_id NUMBER) IS
        SELECT employee_id, last_name
        FROM employees
        WHERE department_id = p_dept_id;
BEGIN
    FOR emp_rec IN c_emp(50) LOOP
        DBMS_OUTPUT.PUT_LINE(
            emp_rec.employee_id || ' ' || emp_rec.last_name
        );
    END LOOP;
END;
/

Why This Is Better

No OPEN
No FETCH
No EXIT
No CLOSE
Oracle manages everything internally

Parameterized Cursor with Multiple Parameters

CURSOR c_emp (
    p_dept_id NUMBER,
    p_min_salary NUMBER
) IS
    SELECT employee_id, last_name, salary
    FROM employees
    WHERE department_id = p_dept_id
      AND salary >= p_min_salary;

Opening

OPEN c_emp(60, 8000);

Real-World Use Cases

1️⃣ HR Applications

Fetch employees by:

Department
Job role
Salary range

OPEN c_emp(p_department_id);

2️⃣ Banking Systems

Fetch transactions:

For a specific account
Within a date range

CURSOR c_txn (p_acc_no NUMBER, p_from DATE, p_to DATE)

3️⃣ Reporting Systems

Generate reports dynamically:

Monthly reports
Region-wise data
Product-wise sales

4️⃣ Batch Processing Jobs

Process records in chunks
Parameter controls which data is processed

5️⃣ Code Reusability

One cursor
Used across multiple blocks
Avoids duplicate logic

Assignments (Practice)

Example 6

Cursor with subquery

Business Scenario (Real-World)

👉 HR Department Requirement

“List employees who earn more than the average salary of their own department.”

Why this needs a subquery

Average salary is different for each department
The comparison must be done per department
This logic cannot be hardcoded

This is a classic, real-world HR analytics requirement.

Cursor with Subquery – PL/SQL Example

PL/SQL Program

DECLARE
    CURSOR c_high_paid_emp IS
        SELECT employee_id,
               last_name,
               salary,
               department_id
        FROM employees e
        WHERE salary >
              ( SELECT AVG(salary)
                FROM employees
                WHERE department_id = e.department_id );

BEGIN
    DBMS_OUTPUT.PUT_LINE('Employees earning more than department average');
    DBMS_OUTPUT.PUT_LINE('------------------------------------------------');

    FOR emp_rec IN c_high_paid_emp LOOP
        DBMS_OUTPUT.PUT_LINE(
            'Emp ID: ' || emp_rec.employee_id ||
            ', Name: ' || emp_rec.last_name ||
            ', Salary: ' || emp_rec.salary ||
            ', Dept: ' || emp_rec.department_id
        );
    END LOOP;
END;
/

Example 7

Print employees department-wise in the following format:

Department ID : 10
*************************************
Employee King works in department 10
Employee ABC works in department 10

Total employees working in department 10 are 2
*************************************

REF CURSOR in Oracle PL/SQL

A REF CURSOR (cursor variable) is a pointer to a result set, but unlike explicit cursors, it is dynamic.

Why Use REF CURSOR?

Query is decided at runtime
Can be passed between procedures
Commonly used in applications and APIs

REF CURSOR Architecture (Client–Server View)

Database (PL/SQL)                    Client / Caller
------------------                  ------------------
OPEN REF CURSOR  ----------------->  Cursor Handle
Result Set Stored                   Fetch Rows
                                     |
                                     v
                                  Display / Process

Types of REF CURSOR

Type	Description
Strong REF CURSOR	Return structure defined
Weak REF CURSOR	No return structure

Weak REF CURSOR Example

What is a Weak REF CURSOR (in one line)?

A Weak REF CURSOR is a cursor variable without a predefined return structure, so it can be opened for any SELECT query at runtime.

Why “Weak”?

Because:

Oracle does not know the column structure at compile time
Structure is decided only when the cursor is opened

This gives flexibility, but less type safety.

Step 1: Declare a Weak REF CURSOR Type

DECLARE
    TYPE weak_ref_cursor IS REF CURSOR;

No RETURN clause → this makes it weak.

Step 2: Complete Simple PL/SQL Example

Requirement

Display employee ID and last name for department 10.

PL/SQL Program

DECLARE
    TYPE weak_ref_cursor IS REF CURSOR;

    emp_cur   weak_ref_cursor;
    v_emp_id  employees.employee_id%TYPE;
    v_lname   employees.last_name%TYPE;
BEGIN
    -- Open the weak REF CURSOR for a query
    OPEN emp_cur FOR
        SELECT employee_id, last_name
        FROM employees
        WHERE department_id = 10;

    -- Fetch data
    LOOP
        FETCH emp_cur INTO v_emp_id, v_lname;
        EXIT WHEN emp_cur%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE(
            'Employee ID: ' || v_emp_id ||
            ', Last Name: ' || v_lname
        );
    END LOOP;

    -- Close cursor
    CLOSE emp_cur;
END;
/

Explanation (Step-by-Step, Easy Language)

1️⃣ Weak REF CURSOR Declaration

TYPE weak_ref_cursor IS REF CURSOR;

No RETURN type
Can point to any SELECT query

2️⃣ Opening the Cursor

OPEN emp_cur FOR SELECT ...

Query is assigned at runtime
Structure becomes known only now

3️⃣ Fetching Rows

FETCH emp_cur INTO v_emp_id, v_lname;

Fetches one row at a time
Variables must match the SELECT list

4️⃣ Exit Condition

EXIT WHEN emp_cur%NOTFOUND;

Stops when all rows are fetched

5️⃣ Closing Cursor

CLOSE emp_cur;

Releases memory
Mandatory for REF CURSORs

Why This Example Is Truly “Weak” REF CURSOR

✔ No fixed return type
✔ Query decided at runtime
✔ Same cursor variable could be reused for another query

Example reuse:

OPEN emp_cur FOR SELECT department_id, department_name FROM departments;

Where Weak REF CURSOR Is Used in Real Projects

Reporting screens
Dynamic search results
UI-driven queries
APIs returning different result sets
Middleware / application integration

Strong REF CURSOR Example

CREATE OR REPLACE PACKAGE hr_package AS
    TYPE emp_ref_cursor IS REF CURSOR RETURN employees%ROWTYPE;
END hr_package;
/

✔ Ensures type safety

What Is a Strong REF CURSOR?

A Strong REF CURSOR is a cursor variable that has a predefined return type.

👉 This means Oracle knows the structure of the result set at compile time.

In simple words:

A Strong REF CURSOR is a REF CURSOR that can return only one specific row structure.

Why Is It Called “Strong”?

Because:

The return type is strictly defined
Oracle enforces type safety
Only compatible queries are allowed

If the query does not match the defined structure, Oracle raises a compile-time error.

Syntax of Strong REF CURSOR

TYPE strong_ref_cursor IS REF CURSOR RETURN table_name%ROWTYPE;

TYPE strong_ref_cursor IS REF CURSOR RETURN some_record_type;

Simple Example of Strong REF CURSOR

Requirement

Fetch employees working in department 20.

Step 1: Declare Strong REF CURSOR Type

DECLARE
    TYPE emp_ref_cursor IS REF CURSOR RETURN employees%ROWTYPE;

✔ Return structure is employees table row

Step 2: Complete PL/SQL Program

DECLARE
    TYPE emp_ref_cursor IS REF CURSOR RETURN employees%ROWTYPE;

    emp_cur emp_ref_cursor;
    emp_rec employees%ROWTYPE;
BEGIN
    -- Open the Strong REF CURSOR
    OPEN emp_cur FOR
        SELECT *
        FROM employees
        WHERE department_id = 20;

    -- Fetch data
    LOOP
        FETCH emp_cur INTO emp_rec;
        EXIT WHEN emp_cur%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE(
            'Employee ID: ' || emp_rec.employee_id ||
            ', Name: ' || emp_rec.first_name || ' ' || emp_rec.last_name ||
            ', Salary: ' || emp_rec.salary
        );
    END LOOP;

    -- Close cursor
    CLOSE emp_cur;
END;
/

Explanation (Easy Language)

1️⃣ Strong REF CURSOR Declaration

TYPE emp_ref_cursor IS REF CURSOR RETURN employees%ROWTYPE;

Cursor can return only employee rows
Structure is fixed
Oracle validates it at compile time

2️⃣ Opening the Cursor

OPEN emp_cur FOR SELECT * FROM employees ...

✔ Query structure must match employees table
✔ Otherwise, compilation fails

3️⃣ Fetching Rows

FETCH emp_cur INTO emp_rec;

Fetches entire row
Clean and safe

4️⃣ Closing Cursor

CLOSE emp_cur;

Mandatory to release memory

What Happens If Structure Does NOT Match?

❌ This will FAIL:

OPEN emp_cur FOR
SELECT employee_id, last_name FROM employees;

Error Reason:

Cursor expects all columns
Query returns only two columns

This is why Strong REF CURSOR is safe but less flexible.

Static Cursor vs REF CURSOR

Feature	Static Cursor	REF CURSOR
Query	Fixed	Dynamic
Binding	Compile time	Runtime
Flexibility	Low	High
Parameter Passing	No	Yes
Application Usage	Limited	Common

When to Use What?

Use Static Cursor

Fixed reports
Known SQL
Simple logic

Use REF CURSOR

Dynamic SQL
Returning result sets
Front-end applications
APIs and services

Strong REF CURSOR – Example 2 (Department-wise Employees)

Scenario

Return employees of a department where structure is fixed and known.

Step 1: Define Strong REF CURSOR

CREATE OR REPLACE PACKAGE emp_pkg AS
    TYPE emp_cur IS REF CURSOR RETURN employees%ROWTYPE;
END emp_pkg;
/

Step 2: Procedure

CREATE OR REPLACE PROCEDURE get_emp_by_dept (
    p_dept_id IN employees.department_id%TYPE,
    p_cursor  OUT emp_pkg.emp_cur
) AS
BEGIN
    OPEN p_cursor FOR
    SELECT * FROM employees
    WHERE department_id = p_dept_id;
END;
/

Step 3: Fetching

DECLARE
    v_cur emp_pkg.emp_cur;
    v_emp employees%ROWTYPE;
BEGIN
    get_emp_by_dept(60, v_cur);

    LOOP
        FETCH v_cur INTO v_emp;
        EXIT WHEN v_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(
            v_emp.employee_id || ' ' || v_emp.last_name || ' ' || v_emp.salary
        );
    END LOOP;

    CLOSE v_cur;
END;
/

Why Strong REF CURSOR?

Compile-time validation
Structure safety
Best for stable schemas

Weak REF CURSOR – Example 2 (Dynamic Report)

Scenario

Fetch different result sets based on input.

Package

CREATE OR REPLACE PACKAGE report_pkg AS
    TYPE ref_cur IS REF CURSOR;
END report_pkg;
/

Procedure with Dynamic Query

CREATE OR REPLACE PROCEDURE get_report (
    p_type   IN VARCHAR2,
    p_cursor OUT report_pkg.ref_cur
) AS
BEGIN
    IF p_type = 'EMP' THEN
        OPEN p_cursor FOR
        SELECT employee_id, last_name, salary FROM employees;
    ELSIF p_type = 'DEPT' THEN
        OPEN p_cursor FOR
        SELECT department_id, department_name FROM departments;
    END IF;
END;
/

Fetching

DECLARE
    v_cur report_pkg.ref_cur;
    v_id NUMBER;
    v_name VARCHAR2(100);
    v_sal NUMBER;
BEGIN
    get_report('EMP', v_cur);

    LOOP
        FETCH v_cur INTO v_id, v_name, v_sal;
        EXIT WHEN v_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_id || ' ' || v_name || ' ' || v_sal);
    END LOOP;

    CLOSE v_cur;
END;
/

Why Weak REF CURSOR?

Dynamic structure
Runtime flexibility
Ideal for reports & dashboards

Real-World Use Cases of Cursors & REF CURSORs

1. Banking Systems

Fetch transaction history row-by-row
Generate account statements
REF CURSORs return data to UI screens

2. HR Management Applications

Cursor FOR LOOP for payroll calculations
REF CURSORs for employee search screens
Department-wise employee reports

3. Reporting Tools (BI / Crystal Reports)

REF CURSORs as data sources
Multiple queries from a single procedure
Improves performance & security

4. APIs & Middleware

PL/SQL procedure returns REF CURSOR
Java / .NET application consumes it
Common in enterprise architecture

5. Batch Processing

Explicit cursors for:
- Salary hikes
- Data migration
- Validation jobs

Best Practices for Using Cursors

✅ Prefer Cursor FOR LOOP when possible
✅ Always CLOSE explicit cursors
✅ Use Strong REF CURSOR when structure is known
✅ Use Weak REF CURSOR only when needed
❌ Avoid cursors when pure SQL can solve it
❌ Do not fetch without checking %NOTFOUND

Practice Questions (Interview & Hands-On)

Beginner

What is an active set?
Difference between %FOUND and %NOTFOUND?
Can a cursor move backward? Why?

Intermediate

Write a cursor to display top 5 salaried employees.
Write a parameterized cursor for department ID.
Convert an explicit cursor into a Cursor FOR LOOP.

Advanced

Write a REF CURSOR returning employees and departments dynamically.
Difference between Strong and Weak REF CURSOR with example.
How REF CURSORs are used in front-end applications?
When should cursors be avoided?

Summary Table

Feature	Explicit Cursor	REF CURSOR
Query	Static	Dynamic
Use Case	Row processing	Result sharing
Parameter Passing	❌	✅
Application Friendly	❌	✅
Performance	Good	Excellent for APIs

Conclusion

Cursors are a core PL/SQL concept that allow row-by-row processing of SQL results.
Understanding explicit cursors, cursor attributes, and REF CURSORs is essential for writing efficient and professional Oracle PL/SQL code.

Disclaimer:
The examples provided in this article are for educational purposes only and are based on Oracle’s HR sample schema.
Performance, behavior, and suitability of cursors may vary depending on database size, schema design, and Oracle version.
Always test code in a non-production environment before implementing it in real systems.