Difference Between Cursor and Ref Cursor

Have you ever faced the challenge of fetching multiple rows in Oracle and wondered which tool to use, Cursor or Ref Cursor? Many beginners get confused because both seem similar at first. But the truth is, they work in different ways and are used for different purposes.

A cursor is the basic way to handle records one at a time in PL/SQL. It works well when your query is fixed and you know exactly what data you want to fetch.

A Ref Cursor, however, gives you more power. It is dynamic, flexible, and allows you to change the query at runtime. This is why Ref Cursors are used more in real-time projects and advanced PL/SQL programs.

In this blog, we will break down the difference between cursor and ref cursor in Oracle using simple words, examples, and easy explanations. You will also learn the clear difference between a cursor and a ref cursor in PL/SQL, along with when and why to use each one.

What Is a Cursor in Oracle?

A cursor in Oracle is a pointer that helps you fetch and work with rows returned by a query. When your SQL query returns more than one row, you cannot store it in a single variable. This is where a cursor becomes useful because it lets you process each row one by one.Cursors are mainly of two types: implicit and explicit.

1. Implicit Cursor

An implicit cursor is created automatically by Oracle whenever you run a simple SQL statement like INSERT, UPDATE, DELETE, or a SELECT that returns only one row. You do not need to declare or manage it yourself. Oracle handles everything in the background.

Example situations where an implicit cursor is used:

• Updating a row
• Inserting data
• Deleting data

2. Explicit Cursor

An explicit cursor is created manually by the programmer. You use it when your SELECTquery returns multiple rows, and you want to process them step-by-step.

You must explicitly:

1. Declare the cursor
2. Open the cursor
3. Fetch rows
4. Close the cursor

For example, we have a basic Cursor in PL/SQL (Explicit Cursor)

DECLARE
    CURSOR emp_cur IS
        SELECT employee_id, first_name FROM employees;
    v_id employees.employee_id%TYPE;
    v_name employees.first_name%TYPE;
BEGIN
    OPEN emp_cur;
    LOOP
        FETCH emp_cur INTO v_id, v_name;
        EXIT WHEN emp_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);
    END LOOP;
    CLOSE emp_cur;
END;
/

What this example does:

• Declares a cursor named emp_cur
• Opens it and fetches each row
• Prints employee details one by one
• Closes the cursor after all rows are processed

This is the simplest way for freshers to understand how explicit cursors work.

What Is a Ref Cursor in Oracle?

A Ref Cursor in Oracle is a special type of cursor that acts as a pointer to a query result set. Unlike a normal cursor where the query is fixed, a Ref Cursor allows you to pass different queries at runtime. This is why it is called a “reference cursor” — it holds a reference to any SELECT statement, not just one specific query.

Ref Cursors are widely used in real-time applications because they are flexible, reusable, and easy to pass between procedures, functions, and packages. This makes them very helpful for complex data handling.

If you are a fresher and wondering why we use Ref Cursor in Oracle, the answer is simple:

• When you want dynamic SQL
• When the query changes based on conditions
• When you need to return results to another program
• When you want to pass large result sets from one block to another

Ref Cursor in PL/SQL Example

Here is a very simple ref cursor in PL/SQL example to help you understand it easily:

DECLARE
    TYPE emp_refcur IS REF CURSOR;
    rc_emp emp_refcur;
    v_id employees.employee_id%TYPE;
    v_name employees.first_name%TYPE;
BEGIN
    OPEN rc_emp FOR
        SELECT employee_id, first_name FROM employees;

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

    CLOSE rc_emp;
END;
/

Types of Ref Cursors

In Oracle, Ref Cursors are mainly of two types. Understanding this makes it easier to decide which one to use in your PL/SQL programs.

1. Strong Ref Cursor

A Strong Ref Cursor is linked to a specific return type. This means the structure of the query is fixed. The cursor can only return rows that match the defined datatype structure.

Key points:

• Strict type checking
• Helps avoid mistakes during compile time
• Used when the SELECT columns are always the same

Example:

TYPE strong_emp_cur IS REF CURSOR RETURN employees%ROWTYPE;

At this point, the cursor can only return rows from the employees table.

2. Weak Ref Cursor

A Weak Ref Cursor is more flexible. It can return any query structure, meaning the columns and datatypes do not have to match a predefined format.

Key points:

• No fixed return type
• Can handle different SELECT statements
• Best for dynamic SQL and reusable procedures

Example:

TYPE weak_cur IS REF CURSOR;

This cursor can point to any SELECT query, making it very useful in real-world applications.

Static Cursor vs Ref Cursor

Many beginners often wonder about the difference between static cursor and ref cursor in Oracle because both are used to fetch multiple rows. But they are not the same. The main difference lies in how flexible they are.

• A static cursor (normal cursor) works with a fixed SELECT query.
• A Ref Cursor, on the other hand, is dynamic and allows the query to change at runtime.

Let’s break this down in simple words.

Static Cursor

A static cursor is fixed. Once you define the SELECT statement, it cannot be changed. It is best for simple programs where the query stays the same every time.

Key points:

• Query is static
• Structure cannot change
• Good for simple, predictable logic

Example:

CURSOR emp_cur IS
    SELECT employee_id, first_name FROM employees;

Ref Cursor (Dynamic Cursor)

A Ref Cursor is flexible and dynamic. It can accept different queries based on conditions. This makes it perfect for real-time applications, especially when working with large modules.

Key points:

• Query can change at runtime
• Can return different columns and structures
• Useful in procedures, packages, and APIs

Oracle Ref Cursor Dynamic SQL Example

Here is a simple example to show how a Ref Cursor works with dynamic SQL:

DECLARE
    TYPE emp_refcur IS REF CURSOR;
    rc emp_refcur;
    dept_id NUMBER := 50;
BEGIN
    OPEN rc FOR
        'SELECT employee_id, first_name FROM employees WHERE department_id = ' || dept_id;

    -- Process records here...

    CLOSE rc;
END;
/

This shows how the query can change based on values, conditions, or user input. This flexibility is not possible with a static cursor.

When to Use Ref Cursor

A Ref Cursor is very helpful when you work on real-time PL/SQL programs. While a normal cursor works well for fixed queries, a Ref Cursor is the better choice when your requirements change during runtime. Here are the common situations where using a Ref Cursor is the right option.

1. When the Result Set Changes at Runtime

A Ref Cursor is the best choice when the output of your query is not fixed. For example, in one situation you may need to fetch employee details, and in another situation you may need department details. A static cursor cannot handle this because its structure cannot change. But a Ref Cursor can easily manage such changing results.

2. When Returning Data to External Applications

If your PL/SQL program needs to send data to another application like Java, C#, .NET, Python, or even reporting tools, a Ref Cursor makes it easy. Many enterprise applications use Ref Cursors to read data directly from Oracle. This is because a Ref Cursor can be returned as an output parameter and processed easily by external programs.

3. When Building Modular PL/SQL Programs

Ref Cursors are very useful when you write procedures, functions, and packages that are reused in different modules.

They help you:

• Share result sets across multiple programs
• Keep your code clean and reusable
• Handle dynamic queries in large applications

This is why most modern Oracle projects prefer Ref Cursors for flexible, modular development.

SYS_REFCURSOR vs User-Defined Ref Cursor

When working with Ref Cursors in Oracle, you will come across two options:

1. Creating your own Ref Cursor type
2. Using Oracle’s built-in SYS_REFCURSOR
   

Both work in a similar way, but there are small differences that matter when writing PL/SQL programs. If you are a fresher, understanding the difference between ref cursor and sys ref cursor in Oracle helps you choose the right approach.

What Is SYS_REFCURSOR?

SYS_REFCURSOR is a pre-defined weak Ref Cursor type provided by Oracle. You don’t have to declare your own cursor type when using it. This saves time and keeps your PL/SQL code clean.

Why it’s useful:

• No need to create a custom TYPE
• Works well for simple procedures and functions
• Good for standard input-output operations

Example:

DECLARE
    rc SYS_REFCURSOR;
BEGIN
    OPEN rc FOR SELECT * FROM employees;
END;
/

What Is a User-Defined Ref Cursor?

A user-defined Ref Cursor is created using the TYPE keyword. You define it when you need more control or when you want a strong or weak cursor type, depending on your project.

Why it’s useful:

• Can define strong or weak types
• Helps maintain structure in large applications
• Supports specific return types if needed

Example:

TYPE emp_cur IS REF CURSOR;
rc emp_cur;

Key Difference

Feature	SYS_REFCURSOR	User-Defined Ref Cursor
Type	Weak	Weak or Strong
Need to Declare?	No	Yes
Flexibility	Good for general use	Very flexible for complex apps
Usage	Simple procedures/functions	Projects needing structure or strong typing

Frequently asked questions about cursor and ref cursor

1. What is the use of a Ref Cursor?

A Ref Cursor is used to return query results in a flexible way. It allows you to pass different SELECT statements at runtime and send data to other programs like Java, C#, or Python.

2. What are the different types of Ref Cursors?

There are two types:

• Strong Ref Cursor – Has a fixed return type
• Weak Ref Cursor – Can return any query structure

3. Which is better, Cursor or WHILE loop?

Both are different. A cursor is used to fetch rows one by one from a SELECT query. A WHILE loop is only a loop structure and does not fetch data by itself. So, a cursor is better when you need to process query rows.

4. What are the advantages of Ref Cursor in Oracle?

Ref Cursors are flexible and useful for:

• Dynamic SQL
• Returning results to external applications
• Passing data between procedures and packages
• Handling different types of result sets

5. How many types of cursors are there in Oracle?

Oracle has two main cursor types:

1. Implicit cursors – Created automatically by Oracle
2. Explicit cursors – Created by the programmer

Additionally, PL/SQL also has Ref Cursors (strong and weak).

6. What is the difference between Ref Cursor and SYS_REFCURSOR?

SYS_REFCURSORis a predefined weak Ref Cursor provided by Oracle. A user-defined Ref Cursor can be strong or weak, based on how you declare it.

7. What is a cursor type?

A cursor type defines the structure of a cursor. It helps Oracle know what kind of data the cursor will return. Strong cursor types have a fixed structure, while weak types can accept any structure.