Procedure or Function return cursor over normal statement

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Procedure or Function return cursor over normal statement

Postby ponic » Tue Oct 23, 2012 1:02 pm

Hi Tim,

I have been going through your post regarding Using Ref Cursors To Return Recordsets
http://www.oracle-base.com/articles/mis ... rdsets.php

I would like to know the impact of this when using a sys_refcursor over a plain sql statement.

I do have a few functions which return sys_refcursor. So does this have any impact in performance if a plain normal sql statement is used instead of
sys_refcursor? Because a normal sql statement executes faster than a cursor.
Of course when calling from Java there are quite a few advantages of calling a function which returns a sys_refcursor.

Would like to hear your view point on this.

Thanks
ponic
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Procedure or Function return cursor over normal statemen

Postby Tim... » Tue Oct 23, 2012 2:31 pm

Hi.

The nice thing about using REF CURSORs is you are keeping your SQL in the database, rather than having it in your client application (Java or .NET). This makes impact analysis and tuning much simpler from a DBA perspective.

There is no major impact on performance. All you are basically doing it passing out a pointer to the cursor.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Procedure or Function return cursor over normal statemen

Postby ponic » Tue Oct 23, 2012 4:39 pm

That make sense. Great answer as always.

Regards
ponic
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Procedure or Function return cursor over normal statemen

Postby Tim... » Tue Oct 23, 2012 4:53 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 4 guests