OraFAQ Forum: SQL & PL/SQL » Refcursor with returning resultset

Home » SQL & PL/SQL » SQL & PL/SQL » Refcursor with returning resultset (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Refcursor with returning resultset [message #618122]

Tue, 08 July 2014 05:22

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

Whether one refcursor may be used once or mutliple times for different result sets.
Here we have used single refcursor with mutliple result sets. Is there any problem of doing that.
Please suggest.


create or replace package test_refcursor is
type t1 is ref cursor;
procedure get_emp(p_deptno number,c1 out t1);
procedure get_dept(p_deptno number,c1 out t1);
end;

create or replace package body test_refcursor is
  procedure get_emp(p_deptno number,c1 out t1)
  is begin
  open c1 for select * from emp where deptno=p_deptno;
  null;
  end;
  
  procedure get_dept(p_deptno number,c1 out t1)
  is begin
  open c1 for select * from dept where deptno=p_deptno;
  null;
  end;
end;

declare
cc1 test_refcursor.t1;
begin
test_refcursor.get_emp('20',cc1);
test_refcursor.get_dept('20',cc1);
end;

Regards,
Nathan

[Updated on: Tue, 08 July 2014 05:57]

Report message to a moderator

Re: Refcursor with returning resultset [message #618127 is a reply to message #618122]

Tue, 08 July 2014 06:00

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Before you post a new question maybe you could feedback to the previous one?

Report message to a moderator

Re: Refcursor with returning resultset [message #618128 is a reply to message #618122]

Tue, 08 July 2014 06:02

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Yes, there is a problem in doing what you posted.

Report message to a moderator

Re: Refcursor with returning resultset [message #618132 is a reply to message #618128]

Tue, 08 July 2014 06:15

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

Could you please elaborate what the actual problem. Because for entire package is using one refcursor for all procedures means a lot of code saving.
I excepted this error 'maximum open cursors exceeded ' after executing this because so may cursor were opened and nothing was closed .

declare
cc1 test_refcursor.t1;
begin
for i in 1.. 1000 loop
test_refcursor.get_emp('20',cc1);
test_refcursor.get_dept('20',cc1);
end loop;
end;

Report message to a moderator

Re: Refcursor with returning resultset [message #618133 is a reply to message #618132]

Tue, 08 July 2014 06:25

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I excepted this error 'maximum open cursors exceeded ' after executing this because so may cursor were opened and nothing was closed

This is the problem in your code.

Quote:

Because for entire package is using one refcursor for all procedures means a lot of code saving.

A lot? I doubt.
But you will generate a lot of maintenance problems for sure.
Use strong ref cursor type when you know what you will get and weak ref cursor type when it is not known at compile time.

Report message to a moderator

Re: Refcursor with returning resultset [message #618144 is a reply to message #618132]

Tue, 08 July 2014 07:34

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

sss111ind wrote on Tue, 08 July 2014 07:15

I excepted this error 'maximum open cursors exceeded ' after executing this because so may cursor were opened and nothing was closed .

You completely misunderstand cursor concepts. Example you posted opens same cursor many times. You really need to read documentation. Opening and Closing Cursor Variables:

Quote:

You need not close a cursor variable before reopening it (that is, using it in another OPEN FOR statement). After you reopen a cursor variable, the query previously associated with it is lost.

SY.

Report message to a moderator

Re: Refcursor with returning resultset [message #618222 is a reply to message #618144]

Wed, 09 July 2014 09:26

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

So by declaring one ref cursor,can we use it again and again like as follows,

create or replace package test_refcursor is
type t1 is ref cursor;
procedure get_proc1(p_deptno number,c1 out t1);
procedure get_proc2(p_deptno number,c1 out t1);
.
.
.
.
procedure get_proc10000(p_deptno number,c1 out t1);
end;

[Updated on: Wed, 09 July 2014 09:27]

Report message to a moderator

Re: Refcursor with returning resultset [message #618223 is a reply to message #618222]

Wed, 09 July 2014 09:37

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

In what you posted, you do not use a ref cursor, you use a ref cursor type.
Replace t1 by NUMBER and you will understand what you posted and get its answer.

[Updated on: Wed, 09 July 2014 09:37]

Report message to a moderator

Re: Refcursor with returning resultset [message #618224 is a reply to message #618223]

Wed, 09 July 2014 09:59

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Not sure what you mean. There are no issues with:

create or replace package test_refcursor is
type t1 is ref cursor;
procedure get_proc1(p_deptno number,c1 out t1);
procedure get_proc2(p_deptno number,c1 out t1);
.
.
.
.
procedure get_proc10000(p_deptno number,c1 out t1);
end;

SY.

Report message to a moderator

Re: Refcursor with returning resultset [message #618225 is a reply to message #618224]

Wed, 09 July 2014 10:00

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Ah, I see OP updated his post. Most likely issue you are referring to was in original post.

SY.

Report message to a moderator

Re: Refcursor with returning resultset [message #618226 is a reply to message #618225]

Wed, 09 July 2014 10:13

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No, I actually answer to this OP's post but I tried to understand what he was trying to use.
There are several levels in the question if you include the previous ones.

1/ There is no problem with the posted code
2/ The posted does not use "one ref cursor" but one ref cursor type
3/ If you use this ref cursor type for different cursors (queries) you will not have any problem executing, you will have some to maintain the code.

I think it is clearer in this way.
So, yes you can do it but, as BlackSwan often said, "you can also poke your eye with a pencil".

Report message to a moderator

Re: Refcursor with returning resultset [message #618228 is a reply to message #618222]

Wed, 09 July 2014 11:01

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

sss111ind wrote on Wed, 09 July 2014 19:56

So by declaring one ref cursor,can we use it again and again

If I understand correctly, Michel pointed out already that you are just declaring a ref cursor TYPE which you are using as an OUT parameter to hold the resultset of each OPEN CURSOR FOR statement. You could say the ref cursors are different when you have something like this in your code for each procedure

var_cursor  SYS_REFCURSOR;

Perhaps I got it right, perhaps not. Michel, did I get it correct? Or did you mean something else. Since it got a bit confusing in last two posts of yours and SY's.

Report message to a moderator

Re: Refcursor with returning resultset [message #618229 is a reply to message #618228]

Wed, 09 July 2014 11:10

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Yes you are right and you add one new point: ref cursor variable (and a new type sys_refcusor).
The problem is that OP's post is itself confusing.

So we have
(kind of) ref cursor type: type t1 is ref cursor;
(kind of) ref cursor variable: cc1 test_refcursor.t1;
(kind of) ref cursor parameter: c1 out t1

Report message to a moderator

Re: Refcursor with returning resultset [message #618285 is a reply to message #618229]

Thu, 10 July 2014 05:41

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

I got the clarity that by declaring one refcursor type we can use again as same as NUMBER datatype.
Then If weak refcursor gives so much flexibility why to use strong refcursor.

Report message to a moderator

Re: Refcursor with returning resultset [message #618287 is a reply to message #618285]

Thu, 10 July 2014 05:59

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

If you know something is an integer you use INTEGER datatype and not NUMBER.
If you know something is a string of 10 character max, you use VARCHAR2(10) and not VARCHAR2(4000) or CLOB.
This is the same thing.

It seems you lack the basics of programming and programming language. I advise you to read some things about this, there are a lot of tutorials on the web and you can start with Wikipedia: Strong and weak typing.

Report message to a moderator

Re: Refcursor with returning resultset [message #618290 is a reply to message #618285]

Thu, 10 July 2014 06:04

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

sss111ind wrote on Thu, 10 July 2014 16:11

Then If weak refcursor gives so much flexibility why to use strong refcursor.

Think about returning dataset to a GUI, and you use weak type ref cursor, so GUI has no idea what sort of dataset would be returning to it. How would a frontend developer know what to design to display the dataset? And there are many other things, may be this would answer your doubts.

Report message to a moderator

Previous Topic:	query to find reserved words
Next Topic:	pls help in query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 01:07:20 CDT 2024