Difference between REF CURSOR and CURSOR

To know the difference between ref cursor and cursor , first you should know what is cursor and ref_cursor.

REF_CURSOR:

Dynamic Result Sets: REF_CURSOR, or reference cursor, is a data type that represents a cursor variable. It allows for dynamic result sets to be returned from stored procedures or functions in Oracle.

Data Pointing: REF_CURSOR does not hold actual data but acts as a reference to the result set generated by a query. It points to the memory location where the result set resides.

Flexibility: REF_CURSOR provides more flexibility as it allows the result set to be processed outside the procedure or function, enabling greater control and manipulation of the data in the calling program.

CURSOR:

Static Result Sets: CURSOR is a database object used to retrieve data row by row, typically within a PL/SQL block. It is explicitly declared and defined to fetch a specific set of data from the database.

Data Storage: Unlike REF_CURSOR, a CURSOR holds the actual result set in memory. It stores the rows fetched from the database until they are processed or the cursor is closed.

Scoped: CURSORs are scoped within the PL/SQL block where they are declared. They cannot be passed as parameters to other procedures or functions directly.

In essence, REF_CURSOR offers more flexibility by allowing dynamic result sets to be passed between procedures/functions and the calling program, while CURSOR provides a more straightforward approach to fetching and processing data within a specific PL/SQL block.