Skip to Main Content
  • Questions
  • Difference between cursor and a ref cursor

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Praveen.

Asked: December 06, 2003 - 12:05 pm UTC

Last updated: September 28, 2017 - 3:15 pm UTC

Version: 8.1.6

Viewed 50K+ times! This question is

You Asked

Hi Tom,

Feels good that i got a chance to ask you a question. I attended an interview for oracle pl/sql. There he asked one question as what is the difference between cursor and ref cursor, and when would you appropriately use each of these. Could you please tell me how can I effectively answer this question in short.

Your input will be greatly appreciated.

Thanks and regards,
Praveen.

and Tom said...

technically, under the covers, at the most "basic level", they are the same.

A "normal" plsql cursor is static in defintion.

Ref cursors may be dynamically opened or opened based on logic.

Declare
type rc is ref cursor;

cursor c is select * from dual;

l_cursor rc;
begin
if ( to_char(sysdate,'dd') = 30 ) then
open l_cursor for 'select * from emp';
elsif ( to_char(sysdate,'dd') = 29 ) then
open l_cursor for select * from dept;
else
open l_cursor for select * from dual;
end if;
open c;
end;
/


Given that block of code -- you see perhaps the most "salient" difference -- no matter how many times you run that block -- cursor C will always be select * from dual. The ref cursor can be anything.

Another difference is a ref cursor can be returned to a client. a plsql "cursor cursor" cannot be returned to a client.

Another difference is a cursor can be global -- a ref cursor cannot (you cannot define them OUTSIDE of a procedure / function)

Another difference is a ref cursor can be passed from subroutine to subroutine -- a cursor cannot be.

Another difference is that static sql (not using a ref cursor) is much more efficient then using ref cursors and that use of ref cursors should be limited to
- returning result sets to clients
- when there is NO other efficient/effective means of achieving the goal

that is, you want to use static SQL (with implicit cursors really) first and use a ref cursor only when you absolutely have to

Then sit back and say "anything else you wanted to know about them"

Rating

  (68 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

کیا با ت ھے۔

میاں محمد احمد, December 06, 2003 - 7:43 pm UTC

Good Explain....

Tom last week I saw a review on this site in which writer name with unknown language to me. Here I code the name and title of review in my mother language "URDU-Pakistan" do you see it and most important if yes, that How I configure oracle forms and reports for this.

Thanks

Tom Kyte
December 07, 2003 - 8:49 am UTC

I see escaped html entities in the title and something that looks like it might be in your language for the name

so the title is like

کیا با ت ھے۔

you set your NLS_LANG on the client for forms and reports -- contact support for total assistance on that, i don't really work with foreign charactersets personally and don't have forms/reports around for testing.

Thanks

Praveen, December 07, 2003 - 2:39 am UTC

Thanks tom for that great answer with an example. This will be really helpful.

Terrific explanation

DD, December 08, 2003 - 12:45 am UTC

that was a great explanation. You are simply the best .. :)

Tom the Great

Manish Upadhyay, December 08, 2003 - 12:18 pm UTC

I wanted to know the same difference and your explanation is what I needed. Thanks a lot for being there and help so many people.




Excellent explanation.

Dilip Patel, December 08, 2003 - 5:23 pm UTC

Even though I knew before what pl/sql cursor is and what ref cursor is, your explanation really completes my understanding on how they differentiate.

Thanks Much.

Absolutely understandable....

Biswadip Seth, December 11, 2003 - 3:37 am UTC

thanks,,,
your respose of this question is absolutely easy to understandable.even any begginer's to the oracle can make out this article...
thanks a lot

NLS Lang

Arabic Masood Sultan Ali Betheri, December 29, 2003 - 2:22 pm UTC

You can use the NLS_LANG environment variable to customise the forms and reports/sql plus on your platform. I have done it in Arabic. It really works!

A reader, December 29, 2003 - 2:23 pm UTC


Can U see this

A reader, December 29, 2003 - 2:24 pm UTC

ÉãÅbàøÞ¼í ØâMV ØíxÞV ¦µÃæÎCßW

ÈÞϵȿzÞøßW ÆßÜàÉßÈá çÖ×ÎáU ÄÜÎáùÏßW ¯xÕáÎÇßµ¢ ÄÞøÎâÜc¢ §çMÞZ ÉãÅbàøÞ¼ßÈÞÃí. ØßÈßÎÞ dÉÕVJµV §GáæµÞ¿áJ çùxÞÃí. µÝßEÕV×¢ ø¢·æJJßÏ ÉãÅbßÏáæ¿ ædµÁßxßW §çMÞZ ºßdÄBZ

Can you interpret the above?

Tom Kyte
December 29, 2003 - 2:54 pm UTC

nope, just a bunch of characters -- none of them ascii.

A reader, December 29, 2003 - 3:58 pm UTC

Is that Russian ??

$%^&* does not follow

Harrison, December 29, 2003 - 5:36 pm UTC

I learned at least two things, a better grasp of when to use reference cursors, and how to push unrelated questions onto the stack.

Simply Great

Vijay Dwivedi, January 25, 2004 - 6:05 pm UTC

It is really great to see these differences, but why these were not explained in Oracle Books so simple..

most useful

vishram, March 17, 2004 - 12:49 am UTC

most useful

Excellent

Jayasri, March 22, 2004 - 12:53 am UTC

That's indeed a great explanation Tom!

Devesh joshi, March 24, 2004 - 1:49 am UTC

An Excellent Example

Thank You Very Much

Sugandha, April 29, 2004 - 10:57 am UTC

I have a PL/SQL developer job interview today and though I had an idea about the diff in REF cursors and normal cursors, I was not sure how to put it in such simple words. Thanks for helping me out

Clarity at its best

Apratim Shaw, June 10, 2004 - 4:46 am UTC

Clear and comprehensive.
No wonder I love your book - Expert One-on-One

Arabic Nls Lang

Amr Mohamed Elabbasy, November 30, 2004 - 10:14 am UTC

I ask about how to change my forms and reports to read in arabic plz send 2 me thx

Tom Kyte
November 30, 2004 - 11:18 am UTC

nothing to do with "cursors and ref cursors" as far as I can tell.


</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96529/toc.htm <code>

is the document you want to read and assuming

a) your database has data in arabic
b) your database character set actually supports that data
c) you have the proper fonts installed on your client
d) they have their NLS_LANG set properly (you'll read about that)

it should just work.

PKaur, January 13, 2005 - 11:49 am UTC

This answer gave me the clear difference between cursors and ref cursors I was looking for.Thank you very much Tom.

Ref Cursor Explanation

Simon, February 20, 2005 - 3:37 pm UTC

Excellent

R.Krit, April 27, 2005 - 8:32 am UTC

you are the best Tom :-)

Excellent

Gauri, May 11, 2005 - 6:14 am UTC

I have no words to describe how simply the concept was explained.I was very confused till now.Now I have a fair idea of what REF_CURSORS are.Thanks Tom,Absoloutely wonderful!!

Bhupinder Singh, May 13, 2005 - 2:43 am UTC

I got correct answer after so many years

Nice difference

Gaurav C. Pandey, May 14, 2005 - 4:46 am UTC

Now i am able to distingiush between Ref. cursor and cursor.

kanchan santani, March 28, 2006 - 12:37 am UTC

Absolutely Wonderful!!
Really happy to get such concepts cleared.
thanks a lot...Tom

PL/SQL Cursor and Ref Cursor

Ram, April 05, 2006 - 2:52 pm UTC

Hi Tom,
Most useful, but I need one clarificaiton though. As I was going through the article on differences between PL/SQL Cursor and Ref cursor
, Ref cursor can be used(in addition to others) to return recordset to Client.

I am on a Web based project with N-Tier architecture,
I simply write a stored proc with Select....and call the stored proc from COM+ layer and return data to client
as recordset. ASP vbscript will loop through recordset for display.

In this case there is no need to use Ref Cursor to pass data to client. Please comment.


thanks
Ram


Tom Kyte
April 06, 2006 - 9:25 am UTC

and how will you return a "record set" from a plsql stored procedure to vb?

You will be using a ref cursor to return a result set to vb.

Ram, April 06, 2006 - 9:49 am UTC

Hi Tom,
Sorry, I forgot to mention DB. The DB for this project is SQL Server.

--- Proc
create procedure GET_dept_data()
begin
Select * from dept
end

--- Inside COM+
Public Function GetData() As ADODB.Recordset

Set GetData = Execute GET_dept_data using ADO
End
--- Inside ASp
Instantiate DLL and
Set recset = DLL.GetData()
---

This works in SQLServer.

I was wondering if something like this is possible in Oracle WITHOUT using RefCursor

Thanks
Regards


Tom Kyte
April 07, 2006 - 3:39 pm UTC

In Oracle - the ref cursor is the way to do it.


create procedure get_dept_data( p_cursor in out sys_refcursor )
as
begin
open p_cursor for select * from dept;
end;


and here are examples of binding to it in various languages/apis

</code> http://asktom.oracle.com/~tkyte/ResultSets/index.html <code>

and please - begging you to remember "sqlserver isn't oracle, oracle isn't sqlserver, A is not B, B is not A"

If we were identical in every aspect, it would be useless to have more than one.


Clarified

Ram, April 10, 2006 - 9:26 am UTC

Hi Tom,
Understood Sir..I am getting ready for myself,
since there is a possibility to migrate to Oracle.

Thanks
Regards,
Ram

Super

Ram, May 02, 2006 - 10:52 am UTC

Hi Tom,
Super explanation. Now I know exactly what is Ref Cursor
& Cursor. I also thank Praveen who started this.

Thanks

Clear simple explanation

kannan, May 18, 2006 - 7:43 am UTC

Thanks for the simple and clear explanation of the differences between Cursors and Ref Cursors. I really enjoyed it.

practical use of ref cursor within pl/sql

shipra, May 25, 2006 - 11:35 am UTC

I am clear with ref cursor no, that was great. But i am always in doubt that what can be the practical example of using ref cursor within pl/sql. I understand the use of it when i am calling any function through front end(VB etc)...But in my project, i am calling functions through UNIX only. I will apprecite if you can tell me a scenerio where I am bound to use REF CURSOR.

Thanks in advance.

Tom Kyte
May 25, 2006 - 1:49 pm UTC

dynamic sql


open cursor for 'select ......';




Cursors and Bulk collect

info, May 25, 2006 - 5:08 pm UTC

Tom, How does Cursors and bulk collect differ ?

Cursor c1 is select * from emp;
--
begin
select * bulk collect into [array] from emp;

what happens after doing a bulk collect, does it store in memory? then we have cursor for that .
Which one is better and why?
Hope my question makes sense..

I am just trying to understand the fundamental concepts
[:-)] after working in Oracle for many years..



Thanks,


Tom Kyte
May 25, 2006 - 6:04 pm UTC

do you have access to "Effective Oracle by Design" - a book I wrote.

Diff between cursor and Ref cursor

Chandra Bhushan, May 26, 2006 - 5:54 am UTC

This difference is really very helpful for developers.
It gives the most of clear pictures of Pl/Sql cursor and Ref Cursor.

Thanks,
Chandra

Cursors and Bulk collect

info, May 27, 2006 - 7:56 pm UTC

Tom, Unfortunately, I do not have access to that book right away.

But I promise I'm going to buy after these holidays ;-)

Thanks,

Tom Kyte
May 27, 2006 - 9:53 pm UTC

A cursor and bulk collect do not "differ"

In order to bulk collect - you need a cursor.

bulk collecting is "array fetching", it is a more efficient way to process a cursors result set when the cursor returns more than a handful of rows.

Explanation of difference between cursor and ref cursor is too good.

Archana, December 07, 2006 - 1:37 am UTC

Hi Tom,

I am a new visistor to this site. Thanks. I was really looking for the difference between cursor and ref cursor.

Thanks & Regards,
Archana .

A Reader

Hari, December 11, 2006 - 1:54 pm UTC

hi tom i need a clarification about ref cursor, i saw one example given by u, i understood that one. but first what is a ref cursor and what is the use? give me full clarification with good examples.

Tom Kyte
December 11, 2006 - 2:27 pm UTC

just read the original example

Can you explain this in detail?

A reader, December 12, 2006 - 10:06 am UTC

Tom, You have given differences in original answer above , but can you explain more on this point?

<quote>
Another difference is a cursor can be global -- a ref cursor cannot (you cannot
define them OUTSIDE of a procedure / function)
</quote>


I thought I understood it..but again..not
[An Example would be much better for me to understand].

Thx.


Tom Kyte
December 12, 2006 - 10:06 pm UTC

try to define one outside of a procedure, as a global in a package body for example.

ops$tkyte%ORA9IR2> create or replace package my_pkg
  2  as
  3          procedure p;
  4  end;
  5  /

Package created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace package body my_pkg
  2  as
  3
  4  procedure p
  5  is
  6          x       sys_refcursor;
  7  begin
  8          null;
  9  end;
 10
 11  end;
 12  /

Package body created.

ops$tkyte%ORA9IR2> create or replace package body my_pkg
  2  as
  3          x       sys_refcursor;
  4
  5  procedure p
  6  is
  7  begin
  8          null;
  9  end;
 10
 11  end;
 12  /

Warning: Package Body created with compilation errors.

ops$tkyte%ORA9IR2> show errors
Errors for PACKAGE BODY MY_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/4      PLS-00994: Cursor Variables cannot be declared as part of a
         package

3/4      PL/SQL: Item ignored
 

A Reader, December 20, 2006 - 10:54 pm UTC

Hi Tom,

my understanding is sys_refcursors are always using dynamic SQL, so is there a difference (performance etc) between get_dept_data1 & get_dept_data2

create procedure get_dept_data1( p_dept_no in varchar2, p_cursor in out sys_refcursor )
as
begin
open p_cursor for select * from dept where dept_no = p_dept_no;
end;


create procedure get_dept_data2( p_dept_no in number, p_cursor in out sys_refcursor )
as
begin
open p_cursor for 'select * from dept where dept_no = ''' || tO_char(p_dept_no || '''';
end;


Tom Kyte
December 22, 2006 - 5:54 am UTC

your understanding is "wrong"

sort of.

there is ONLY dynamic sql - all sql is dynamic. Languages like PL/SQL and C (with pro*c) and Java with SQL/J can provide this thing called "static sql" which really means 'known at program compile time'. But at the end of the day - it is processed not any differently by Oracle than 'dynamic sql' is - because it really is dynamic sql under the covers.

That said - your get_dept_data2 should NEVER SEE THE LIGHT OF DAY

a) it isn't using binds, bad bad bad
b) it has no need to not us static sql, you lose all of the compile time benefits
c) you are putting quotes around a number, bad bad bad

Please explain "Cursor can be Global"

Gowtham Sen, December 24, 2006 - 6:54 am UTC

Its very good explanation. It helps me a lot in understanding cursors. I have one doubt. "Another difference is a cursor can be global -- but a ref curson cannot" Please explain it. I didn't get it.


Thanks and regards
Gowtham sen.
Tom Kyte
December 24, 2006 - 9:18 am UTC

ops$tkyte%ORA10GR2> create or replace package my_pkg
  2  as
  3          cursor global_cursor is select * from dual;
  4  end;
  5  /

Package created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace package my_pkg
  2  as
  3          global_cursor sys_refcursor;
  4  end;
  5  /

Warning: Package created with compilation errors.

ops$tkyte%ORA10GR2> show err
Errors for PACKAGE MY_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/16     PL/SQL: Declaration ignored
3/16     PLS-00994: Cursor Variables cannot be declared as part of a
         package


Great explanation

A reader, January 02, 2007 - 10:33 am UTC

I got ur point...

Good Answer

Mayank Jain, January 02, 2007 - 10:42 am UTC

It is really understood

Great answer !!!

Subhas, January 08, 2007 - 12:38 am UTC

Hi Tom,

its awesome :)-
Your answer cleared all my doubts.

Very much clear

Poorni, January 16, 2007 - 6:54 am UTC

Hi Tom,
Iam referring your articles from quite a long time.
you are simply great

Wouldn't it be neat if ....

Arun Mathur, April 26, 2007 - 11:45 am UTC

Create table <new table name>
as <select clause> from <table name>;

would also accept reference cursors ie:

create table <new table name>
as select * from <ref cursor>;

I'm testing a database function which I wrote that scales better than the existing one in production; however, I want to:

1) Place resultsets from both functions to a table
2) Fire up SQL*Plus and compare the resultsets.

No biggie. I'll just fetch/insert them out during the database function.

Regards,
Arun





shilpesh, August 07, 2007 - 3:48 am UTC

You are simply superb TOM!!!!

Difference between cursor and a ref cursor

Badri S, November 14, 2007 - 12:30 pm UTC

Its a very useful and nice explanation. really useful set of points !!

Thanks a ton Tom

Simple & Neat Explanation

A reader, February 05, 2011 - 12:07 am UTC

Thanks a ton, Its user friendly explanation.

Excellent

Guna, May 08, 2011 - 5:40 am UTC

It was ex tra ordinary ... your examples are very simple and understandable ..

Thoroughly Explained.

Priya, January 06, 2012 - 10:40 am UTC

I appreciate the knowledge/understanding that you share with the readers. This explanation has helped me in understanding the concept to a great extent.
Tom Kyte
January 10, 2012 - 10:10 pm UTC


mfz, January 11, 2012 - 3:00 pm UTC

How can I use a refcursor to spit the result set from a with clause.

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 11 15:54:45 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> WITH deptList AS
  2    (SELECT SUBSTR (txt, instr (txt, ',', 1, level ) + 1, Instr (Txt, ',', 1, Level+1) - Instr (Txt, ',', 1, Level) -1 ) AS deptcode
  3    FROM
  4      (SELECT ',' ||('003,050') ||',' txt FROM dual
  5      )
  6      CONNECT BY level <= LENGTH('003,050')-LENGTH(REPLACE('003,050',',',''))+1
  7    )
  8  SELECT  * from deptlist ;

DEPTCODE
---------
003
050


SQL> variable x refcursor;
SQL> open x for WITH deptList AS
SP2-0734: unknown command beginning "open x for..." - rest of line ignored.
SQL>   (SELECT SUBSTR (txt, instr (txt, ',', 1, level ) + 1, Instr (Txt, ',', 1, Level+1) - Instr (Txt, ',', 1, Level) -1 ) AS deptcode
  2    FROM
  3      (SELECT ',' ||('003,050') ||',' txt FROM dual
  4      )
  5      CONNECT BY level <= LENGTH('003,050')-LENGTH(REPLACE('003,050',',',''))+1
  6    )
  7  SELECT  * from deptlist
  8  ;
SELECT  * from deptlist
*
ERROR at line 7:
ORA-00933: SQL command not properly ended



Tom Kyte
January 11, 2012 - 3:36 pm UTC

ops$tkyte%ORA11GR2> variable x refcursor
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> begin
  2          open :x for
  3  WITH deptList AS
  4    (SELECT SUBSTR (txt, instr (txt, ',', 1, level ) + 1, Instr (Txt, ',', 1, Level+1) - Instr (Txt, ',', 1, Level) -1 ) AS deptcode
  5    FROM
  6      (SELECT ',' ||('003,050') ||',' txt FROM dual
  7      )
  8      CONNECT BY level <= LENGTH('003,050')-LENGTH(REPLACE('003,050',',',''))+1
  9    )
 10  SELECT  * from deptlist ;
 11  end;
 12  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> print x

DEPTCODE
---------
003
050

ops$tkyte%ORA11GR2> 

Guna, January 12, 2012 - 3:13 am UTC

Excellent Explanation Tom. Thanks for your service to the Oracle Developers

Great Answer

A reader, April 03, 2013 - 12:56 pm UTC

Thanks Tom :)

ref cursor Vs cursor

sagayakapil, April 11, 2013 - 3:50 am UTC

That was really great. I feel good that at least i knew the difference. Keep up the good work. Thanks.

Excellent

Sureshkumar, July 25, 2013 - 10:57 am UTC

Wonderful! simple examples which are given huge ideas... Great!...

Giridhar, September 21, 2013 - 12:36 am UTC

Nice explanation. Its really useful

Venky, October 21, 2013 - 5:16 am UTC

Try this Way

WITH deptList AS
(SELECT regexp_substr(txt,',?\d+,?',1,level) AS deptcode
FROM
(SELECT '003,050' txt FROM dual)
CONNECT BY level <= regexp_count('003,050',',')+1)
SELECT trim(',' from deptcode) from deptlist ;

reader

HarshaChowdary, December 03, 2013 - 6:29 am UTC

excellent

Perfect

Padpat, April 01, 2014 - 6:51 pm UTC

Hi,

I always had this doubt about cursor and ref cursor. Your answer was very clear. Thanks a lot.

But why do we use a ref cursor exactly?

Prateek Dave, August 12, 2014 - 12:51 pm UTC

Hello Tom,
Just to start..I had an interview around a month ago and the interviewer asked me this question. I mentioned the uses you mentioned in this review (for dynamic sql, to pass it between subroutines, to pass it to clients). But he kept me asking "Its OK..but why do we actually use the ref cursor". Still I didnt get what he was looking for (I got the job though). A question from my side. Is there a difference in which a normal cursor and a ref cursor access the memory structures (SQL AREA).

Hi Tom I hope we can declare ref cursor the following Way

Mastan, November 11, 2014 - 10:02 am UTC

SQL> create or replace package p19
  2  as
  3  procedure p;
  4  end;
  5  /

-------------------------------------------
  1  create or replace package body p19
  2  as
  3  type s is ref cursor;
  4  procedure p
  5  as
  6  begin
  7  null;
  8  end;
  9* end;
SQL> /

Package body created.


Thanks ,

Mastan.

THANKS

abdul, October 21, 2015 - 3:20 am UTC

Thanks a lot for your answer

AWSOME

Vignesh, March 22, 2016 - 4:19 am UTC

Hi Tom,
It's a wonderful explanation for cursor and ref cursor.
thank you

Good explanation

Himanshu, July 31, 2016 - 10:30 am UTC


what is main difference ref cursor and explicit cursor

vadivel, March 15, 2017 - 12:51 pm UTC

what is main difference ref cursor and explicit cursor?

Update the Links.

Santhosh, March 20, 2017 - 6:54 am UTC

Please Update the Links Provided by TOM in this answer those Links Became INVALID, Not Only this links so many links given by Tom for reference were invalid.Please try to Update those Links then it will be very useful for all.
Connor McDonald
March 22, 2017 - 1:52 am UTC

Thanks - we'll follow this up

Practical use of Ref cursor

Tinku, September 28, 2017 - 5:21 am UTC

Are there any practical uses of ref cursor apart from passing the result set to the client?
Could you please give me a simple example for static ref cursor and dynamic ref cursor? when to use these two constructs?




Chris Saxon
September 28, 2017 - 3:15 pm UTC

I can't think of any good uses for a ref cursor other than returning data to the client.

If you write your SQL statement using a string literal or variable, then it's dynamic. Otherwise it's static. For example:

declare
  type cur is ref cursor;
  static_cur cur;
  dynamic_cur cur;
begin
  open static_cur for 
    select * from t;
    
  open dynamic_cur for
    'select * from t';
end;
/


Notice the quotes around the dynamic SQL.

You should only use dynamic SQL when you don't know what the complete statement will be at compile time. e.g. you're changing the where clause based on user input. In all other cases you should use static SQL.

Practical use of Ref cursor

tinku, September 29, 2017 - 6:25 am UTC

Thanks Chris for the crisp and simple explanation.

Difference between cursor and REF cursor

A reader, January 22, 2018 - 7:25 am UTC

Good explanation, very informative. Thanks Tom.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library