What is the ref cursor in Oracle?

REF_CURSOR allows returning a recordset/cursor from a Stored procedure.

It is of 2 types:

Strong REF_CURSOR: Returning columns with datatype and length need to be known at compile time.

Weak REF_CURSOR: Structured does not need to be known at compile time.

Syntax till Oracle 9i
create or replace package REFCURSOR_PKG as
TYPE WEAK8i_REF_CURSOR IS REF CURSOR;
TYPE STRONG REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;
end REFCURSOR_PKG;
Procedure returning the REF_CURSOR:
create or replace procedure test( p_deptno IN number , p_cursor OUT
REFCURSOR_PKG.WEAK8i_REF_CURSOR)
is
begin
open p_cursor FOR
select *
from emp
where deptno = p_deptno;
end test;

Since Oracle 9i we can use SYS_REFCURSOR
create or replace procedure test( p_deptno IN number,p_cursor OUT SYS_REFCURSOR)
is
begin
open p_cursor FOR
select *
from emp
where deptno = p_deptno;
end test;
For Strong
create or replace procedure test( p_deptno IN number,p_cursor OUT REFCURSOR_PKG.STRONG
REF_CURSOR)
is
begin
open p_cursor FOR
select *
from emp
where deptno = p_deptno;
end test;

Oracle - What is the ref cursor in Oracle? - April 10, 2009 at 11:00 AM

What is the ref cursor in Oracle?  

Cursor is a reference type in oracle. We can allocate different storage locations to the cursor when the program runs.

Syntax

Type <type_name> is REF CURSOR
RETURN <return type>

Return_type – Name of new reference type.
Return Type – This represents select list type that will be returned by the query.

Also read

Oracle cursors interview questions

What a SELECT FOR UPDATE cursor represent?, What WHERE CURRENT OF clause does in a cursor?, Can you pass a parameter to a cursor?, Explain the functioning of CURSOR FOR LOOP with example., Define Simple/Explicit , Parametric and Internal/Implicit cursor.............

What is the difference between REF Cursor & Normal Cursor in oracle?

Normal cursors fall under the category of static cursors while REF cursors are dynamic.........

What is use of a cursor variable in oracle?

A cursor variable works like pointer in C. It is used to hold address of an item rather than the item itself. Cursor variables can be used to hold different values at run time............

Oracle - use of parameterized cursor

A parameter makes the cursor more reusable, A parameter avoids scoping problems...............

<<Previous  Next>>

Write your comment - Share Knowledge and Experience