what is ref cursor?

by neelima
(Hyderabad)



What is a PL/SQL ref cursor? What is the purpose and where the situation we use Ref cursor in our application?


A ref cursor in Oracle PL/SQL is much like an ordinary PL/SQL cursor in that it acts as a pointer to the result set of the cursor with which it is associated. However, the difference is that a ref cursor can be assigned to different result sets whereas a cursor is always associated with the same result set. Cursors and ref cursors are not interchangeable.

The real purpose of ref cursors is to be able to share cursors and result sets between the client and the Oracle server or between different subroutines. For example you might open a cursor in an Oracle Forms client and then continue working with the cursor on the server or you might open a cursor in a Java program and then continue working with it in an Oracle PL/SQL stored procedure.

Ref cursors also come in two variants: strongly typed and weakly typed depending on how likely you are (or want to) reuse a cursor variable. Weak ref cursor types can be associated with any query whereas strong ref cursor types can only be associated with cursors of the same type.

Let's look at a couple of examples.


DECLARE
 TYPE wkrefcurty IS REF CURSOR;
        -- weak ref cursor type
 my_cur wkrefcurty;
 dept departments%ROWTYPE;
 BEGIN
  OPEN my_cur FOR SELECT * FROM departments;
  FETCH my_cur INTO dept;
  CLOSE my_cur;
 END;


Note that despite the cursor being weakly typed, the variable to hold the results must be strongly typed. In other words you can't fetch into a variable of the weak cursor's row type.

The following declaration generates a PL/SQL compilation error (PLS-00320: the declaration of the type of this expression is incomplete or malformed).
DECLARE
 TYPE wkrefcurty IS REF CURSOR;
 my_cur wkrefcurty;
 rslt my_cur%ROWTYPE; -- generates a PL/SQL 320 error
 BEGIN
  OPEN my_cur FOR SELECT * FROM departments;
  FETCH my_cur INTO rslt;
  CLOSE my_cur ;
 END;
/
ORA-06550: line 4, column 7:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 4, column 7:
PL/SQL: Item ignored
ORA-06550: line 7, column 20:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 7, column 2:


Also with a PL/SQL weak ref cursor if you fetch into a variable of incompatible type this causes an an exception to be raised by Oracle at run time. For example

DECLARE
 TYPE wkrefcurty IS REF CURSOR;
 -- weakly-typed ref cursor
 my_cur wkrefcurty;
 emp employees%ROWTYPE;
BEGIN
  OPEN my_cur FOR SELECT * FROM departments;
  FETCH my_cur INTO emp;
  CLOSE my_cur;
END;
/
DECLARE
*
ERROR at line 1:
ORA-06504: PL/SQL: Return types of Result Set variables or query do not match

ORA-06512: at line 7


In the above example we attempted to fetch a row from the departments table into a variable that matches the employee table structure. This causes Oracle to raise an exception.

With a strong PL/SQL ref cursor type, errors like this are reported by the Oracle PL/SQL compiler at compile time, as in the following example.

DECLARE
 TYPE myrefcurty IS REF CURSOR RETURN employees%ROWTYPE; -- strong ref cursor type
 my_cur myrefcurty;
 emp employees%ROWTYPE;
BEGIN
 OPEN my_cur FOR SELECT * FROM departments; -- can't do this
 FETCH my_cur INTO emp;
 CLOSE my_cur;
END;
/
ORA-06550: line 6, column 18:
PLS-00382: expression is of wrong type
ORA-06550: line 6, column 2:
PL/SQL: SQL Statement ignored


As long as the result set matches the cursor declaration we can reuse the cursor variable in our Oracle PL/SQL code for new queries as much as we like as in the following examples.

Example of cursor re-use using a strongly-typed ref cursor.

DECLARE
 TYPE myrefcurty IS REF CURSOR RETURN employees%ROWTYPE; -- strong ref cursor type
 my_cur myrefcurty;
 emp employees%ROWTYPE;
BEGIN
 OPEN my_cur FOR SELECT * FROM employees;
 FETCH my_cur INTO emp;
 CLOSE my_cur;

 OPEN my_cur FOR
  SELECT * FROM employees
  WHERE employee_id = 1;
 FETCH my_cur INTO emp;
 CLOSE my_cur;

 OPEN my_cur FOR
  SELECT * FROM employees
  WHERE employee_id > 1000;
 FETCH my_cur INTO emp;
 CLOSE my_cur;

 OPEN my_cur
  FOR SELECT * FROM employees
  WHERE department_id = 1;
 FETCH my_cur INTO emp;
 CLOSE my_cur;

END;

The following example shows the re-use of a weakly-typed ref cursor.

DECLARE
 TYPE wkrefcurty IS REF CURSOR;
     -- weakly-typed ref cursor
 my_cur wkrefcurty;
 emp employees%ROWTYPE;
 dept departments%ROWTYPE;
BEGIN
 OPEN my_cur FOR SELECT * FROM departments;
 FETCH my_cur INTO dept;
 CLOSE my_cur;

 OPEN my_cur FOR SELECT * FROM employees;
 FETCH my_cur INTO emp;
 CLOSE my_cur;
END;

As mentioned earlier, we can open a ref cursor in one Oracle PL/SQL procedure and fetch from it in another as in the following example.

CREATE OR REPLACE PACKAGE ref_cursor_demo IS
 TYPE wkrefcurty IS REF CURSOR;
     -- weakly-typed ref cursor
 PROCEDURE open_cursor (the_cursor OUT wkrefcurty);
 PROCEDURE fetch_cursor (the_cursor IN OUT wkrefcurty);
END ref_cursor_demo;

CREATE OR REPLACE PACKAGE BODY
ref_cursor_demo IS

 PROCEDURE open_cursor (the_cursor OUT wkrefcurty) IS
 BEGIN
  OPEN the_cursor FOR
   SELECT * FROM departments
   ORDER BY department_name DESC;
END open_cursor;

 PROCEDURE fetch_cursor (the_cursor IN OUT wkrefcurty) IS
  dept departments%ROWTYPE;
BEGIN
 FETCH the_cursor INTO dept;
 dbms_output.put_line('1st department is '||dept.department_name);
END fetch_cursor;
END ref_cursor_demo;

DECLARE
 my_cur ref_cursor_demo.wkrefcurty;
BEGIN
 ref_cursor_demo.open_cursor(my_cur);
 ref_cursor_demo.fetch_cursor(my_cur);
END;
/

1st department is Treasury


For more help with PL/SQL see our Oracle PL/SQL tutorial.

If you're interested in formal Oracle training (either on-line or in a classroom) then see our Oracle training page for more information.

Comments for what is ref cursor?

Average Rating starstarstarstarstar

Click here to add your own comments

Mar 13, 2014
Simply explained the ref cursor types
by: Vinni

The way of explaining was very simple,even for a person having basic knowledge about cursors. Also,the concept of strong and weak ref cursor was explained like one which I never came across before.
Thanks.Keep the good work going :)

Aug 01, 2013
Error in anonymous pl/sql block
by: Anonymous

The declaration of the cursor in the anonymous Oracle pl/sql block used to invoke the package was incorrect.

Instead of saying:

DECLARE
my_cur decl_cursor.wkrefcurty;

it should have said :

DECLARE
my_cur ref_cursor_demo.wkrefcurty;

This was a cut and paste error - we've corrected it now.


Apr 25, 2013
Good Artical
by: Rishi

well explained.....

Apr 16, 2013
GUD
by: Anonymous

I understood clearly why we are going to use ref cursors. Thanku very much giving the explanation of refcursors.

Apr 11, 2013

by: Anonymous

excellent work sir,thank so much

Mar 14, 2013
annonymous block declaration error
by: Anonymous

getting the below errors on execution of the plsql block:-
DECLARE
my_cur decl_cursor.wkrefcurty;
BEGIN
ref_cursor_demo.open_cursor (my_cur);
ref_cursor_demo.fetch_cursor (my_cur);
END;

ORA-06550: line 2, column 13:
PLS-00201: identifier 'DECL_CURSOR.WKREFCURTY' must be declared
ORA-06550: line 2, column 13:
PL/SQL: Item ignored
ORA-06550: line 4, column 33:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 4, column 4:
PL/SQL: Statement ignored
ORA-06550: line 5, column 34:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 5, column 4:
PL/SQL: Statement ignored


Feb 01, 2013
awesoe
by: sriram

neelima has good knowledge..thk u..

Aug 29, 2012
SImple snd Nice
by: Hanuma

good explanation

Aug 20, 2012
Not very clear
by: Anonymous

Explanation is clear, but didn't completely got the purpose of the ref cursor,

Aug 02, 2012
Good Explanation.
by: Anonymous

Good Explanation.

Jun 23, 2012
Worth
by: Prasad

AskTheOracle.net a worthfull site with this kind of explanations. Very good.

Jun 15, 2012
Awesom
by: Angel

It is awesom.It is really help full for me..
.I have few questions
1)please can you explain with only for loop i mean not using open fetch statements.
2)when we need to use weak ref cursor and when we need to use strong ref cursor.can u explain in detail because i am beginner.

Jun 09, 2012
Nice Explanation.
by: Anonymous

Really awesome explanation on REF CURSOR.
All my doubts are cleared now.

Thanks.

Cheers!

May 31, 2012
Nice topic
by: Anonymous

Very gud tutorial for ref cursore,all basic concepts are cleared .Thanx a lot once again.

May 29, 2012
an excellent overview of ref cursor definition and use
by: Anonymous

really enjoyed reading and understanding the use of ref cursors

May 23, 2012
Very good explanation
by: pushpita

Very good explanation. Person having no idea at all about refcursor also will clearly understand it completely.

Apr 02, 2012
Its fabulous
by: Prabhudatta

I think your explanation is very clear and understandable.Keep it Up.......

Mar 22, 2012
clear
by: Anonymous

Good explanation with good example very clear

Mar 15, 2012
average
by: Anonymous

more explanation is needed

Mar 14, 2012
Nice One
by: Shah

It's very simple and easily understandable thanks for the post

Mar 02, 2012
Simple & Clear
by: Anonymous

Very clean,clear and understandable...

Jan 06, 2012
its really useful to one and all
by: Anonymous

its really useful to one and all

Jan 05, 2012
Very well explained
by: Akhil Singh

Thanx for enlightening on ref cursor..... its really helpful to anonymous guy to understand it.

Jan 04, 2012
USEFUL
by: Anonymous

Its So useful...NIce

Dec 22, 2011
It is very userful
by: gurunath

It helps a lot in understanding the concept


Dec 09, 2011
NICE
by: ALLADI VENKATESWARLU

VERY GOOD EXPLANATION

Dec 09, 2011
VERY GOOD
by: ALLADI VENKATESWARLU

EXCELLENT EXPLANATION WITH EXAMPLE
THANKS

Nov 01, 2011
hii
by: Manoj

Its very useful..nice explanation..Need more explanation with examples on ref cursors

Oct 28, 2011
Very Good
by: Anonymous

Good Examples, Very Useful

Oct 25, 2011
HI
by: surya

its very help full
but beginers are not understand the examples
a little bit confusion
i am not telling it is not usefull
its very usefull
better u can give simple examples

Oct 18, 2011
.....
by: hemant .....

very helpful

Oct 17, 2011
very good
by: mallesh

u r coding musch usefull for me

Sep 22, 2011
Need More
by: Prakash

What you have given is simple example. Please elaborate with complex example

Sep 20, 2011
Very helpful even for the non DB guys
by: Anonymous

It was great reading through the post and learning something new and quite hands-on. Very helpful for writing scalable PL-SQL blocks!!!

Cheers

Aug 26, 2011
Really Helpfull!
by: SMC

Thanks for such a nice explaination on ref cursor!!!

Aug 24, 2011
Good Doc
by: Archana Sagar

Helpfull n easy to understand doc. Thanks

Aug 21, 2011
very simple , clear & nice
by: Anonymous

realy very simple , clear & nice way of describtion

Aug 18, 2011
Its Nice
by: gr

Simply this article is nice ....

Aug 12, 2011
Thanks..
by: lalkumartl

Thanx 4 sharing this datas... It really helped me to understand about ref cursor.

Aug 04, 2011
about refcurs.ors
by: harinath

Simply Superb.

No one can not provide this much of clarity. I searched soo many ways but i didn't get perfectly.

this article i liked very well

Aug 02, 2011
Answer to decl_cursor mystery
by: Michael Milligan

I believe it was probably a typo. I tried substituting ref_cursor_demo for decl_cursor and it worked fine.

Excellent lesson. Explained much more clearly than others I've read.

Thanks.

Michael Milligan
Layton, Utah

Jul 17, 2011
ref cursor demo example
by: Anonymous

Thanks for the explanation of ref cusors.
Following is with reference to the last example in which I do not see - 1. the cursor being closed. 2. any definition of the object 'decl_cursor' (used to declare the ref cursor type variable). 3. open_cursor procedure using the 'out' parm like it is an 'in' parm.
Would have been nice to have declared the cursor variable in the open_cursor procedure and the fetch_cursor called from this procedure, followed by a close cursor statement

Jun 24, 2011
Oracle dump
by: Guru

From where can I get the Oracle dumps ? And your answer was very good

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.