Home » SQL & PL/SQL » SQL & PL/SQL » Ref Cursors (Oracle, 10g, Windows server 2003)
Ref Cursors [message #445292] Sun, 28 February 2010 02:59 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,
I have created a package with declaration of ref cursor type,
and two stored procedures.

I will not be able to give you the real code, but i'm writinga package here, which on the same lines with real code.

Package specification


Create Or Replace Package test_package Is
 Type test_cur Is Ref Cursor;
 
 Procedure test_sp1(p_cur OUT test_cur,p_chk IN  Number,p_no_of_records OUT Number);

Procedure test_sp2(p_cur OUT test_cur,p_entry OUT Char);

End test_package;


Package Body


Create Or Replace Package Body test_package Is
 Procedure test_sp1(p_cur OUT test_cur,p_chk IN  Number,p_no_of_records OUT Number)
 Is
 Begin

If p_chk = 1 Then
 Open p_cur For Select empno,deptno
 From EMP
 Where deptno = 10;
Elsif p_chk = 2 Then
 Open p_cur For Select empno,deptno
 From EMP
 Where deptno = 20;
End If;

End test_sp1;

 Procedure test_sp2(p_cur OUT test_cur,p_entry IN  Char)
 Is
 Begin
 Open p_cur For Select empno,deptno
 From EMP
 Where empno = p_entry;
End test_sp2;
End test_package;


My doubt is

1) Can using the same name for ref cursors in both the procedures cause problems?
2) Both p_cur are at same parameter position in both the procedures, does that cause any problem?
When one of my packaged procedure (test_package.test_sp1)is getting called from client side (ado.net) it is giving performance problems, though the query is working fine and taking less than 1 second to execute.
3) Can using sys_refcursor solve my problem?
What is difference between sys_refcursor and normal cursor type?


Regards,
Ritesh

[Updated on: Sun, 28 February 2010 03:09]

Report message to a moderator

Re: Ref Cursors [message #445295 is a reply to message #445292] Sun, 28 February 2010 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) Yes as it is a weakly type cursor, just a reference to any one. Don't you see it works when you tested it?

2) No problem.
"it is giving performance problems, though the query is working fine and taking less than 1 second to execute."
Without more information we can just say: optimize your application. For instance, do you bulk/array fetch?

3) No, it is not related to the type of cursor just to your application

4) If you mean custom reference cursor type, none but the name. You can custom your cursor type to receive some specific rows.

Regards
Michel
Re: Ref Cursors [message #445303 is a reply to message #445295] Sun, 28 February 2010 06:13 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi Michel,
So if i use sys_refcursor as type for both the cursors , they will not refer each other like as shown below in package specification

Create Or Replace Package test_package Is
 
 Procedure test_sp1(p_cur OUT sys_refcursor,p_chk IN  Number,p_no_of_records OUT Number);

Procedure test_sp2(p_cur OUT sys_refcursor,p_entry IN Char);

End test_package;



In my package specification above, in procedure test_sp2 parameter p_entry is IN not OUT (by mistake)


I'm not able to understand your answer to my third question, can u please eloborate it
Regards,
Ritesh

[Updated on: Sun, 28 February 2010 07:05]

Report message to a moderator

Re: Ref Cursors [message #445307 is a reply to message #445303] Sun, 28 February 2010 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
, they will not refer each other like as shown below in package specification

What does this mean?

Quote:
In my package specification above, in procedure test_sp2 parameter p_entry is IN not OUT (by mistake)

Did you do the same mistake a second time as I don't see an IN ref cursor parameter in your last post?

Your third question is about solving performances probblem replacing you cursor type by a sys_refcusror, the answer is no and the explication in the answer 2.

Please explain what you want to do and what you don't understand.

Regards
Michel
Re: Ref Cursors [message #445311 is a reply to message #445303] Sun, 28 February 2010 08:05 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
getritesh wrote on Sun, 28 February 2010 13:13
Hi Michel,
So if i use sys_refcursor as type for both the cursors , they will not refer each other like as shown below in package specification

What does "refer" mean? Something like "use the same place to store the result"? If so, it does not depend on name/type of the parameter, but on the way procedures are called (just like with any other parameter types). As you did not post here any sample call of that procedures, this is just a (wild) guess.
Re: Ref Cursors [message #445314 is a reply to message #445303] Sun, 28 February 2010 08:43 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,
My package specification is same

Create Or Replace Package test_package Is
 Type test_cur Is Ref Cursor;
 
 Procedure test_sp1(p_cur OUT test_cur,p_chk IN  Number,p_no_of_records OUT Number);

Procedure test_sp2(p_cur OUT test_cur,p_entry IN Char);

End test_package;



In ado.net if i have to call test_sp1 my .net developer calls it as

test_package.test_sp1(test_cur,1,p_no_of_records)

Regards,
Ritesh

[Updated on: Sun, 28 February 2010 08:51]

Report message to a moderator

Re: Ref Cursors [message #445316 is a reply to message #445314] Sun, 28 February 2010 09:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And...?

Regards
Michel
Re: Ref Cursors [message #445317 is a reply to message #445292] Sun, 28 February 2010 09:38 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>When one of my packaged procedure (test_package.test_sp1)is getting called from client side (ado.net) it is giving performance problems,

When you don't know with 100% certainty where time is being spent, you are merely guessing at hopeful "solution".

The best way to know for sure where time is being spent is via SQL_TRACE.

ALTER SESSION SET SQL_TRACE=TRUE;
-- invoke problem code
ALTER SESSION SET SQL_TRACE=FALSE;

now find the trace file within ./udump folder
tkprof <trace_file.trc> trace_results.txt explain=<username>/<password>

post the contents of trace_results.txt back here

I suspect the root cause is at the client/ADO side & if true making changes within the DB is an exercise in futility.
Re: Ref Cursors [message #445319 is a reply to message #445295] Sun, 28 February 2010 10:28 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi Michel,
Quote:
1) Yes as it is a weakly type cursor, just a reference to any one. Don't you see it works when you tested it?


Please explain it little more

Regards
Michel
Re: Ref Cursors [message #445321 is a reply to message #445319] Sun, 28 February 2010 10:39 Go to previous message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database PL/SQL User's Guide and Reference
Chapter 6 Performing SQL Operations from PL/SQL
Section Using Cursor Variables

Regards
Michel
Previous Topic: what is called table function?
Next Topic: send pdff
Goto Forum:
  


Current Time: Thu Dec 08 03:49:50 CST 2016

Total time taken to generate the page: 0.11458 seconds