This forum is currently locked. You can't register or post questions at this time. (read more)

Procedure or Function return cursor over normal statement

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

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

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

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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

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

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

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 0 guests

cron