کیا با ت ھے۔
میاں محمد احمد, 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
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?
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
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
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
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.
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,
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,
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.
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.
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;
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.
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.
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
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?
March 16, 2017 - 1:34 am UTC
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.
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?
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.