Procedure or Function return cursor over normal statement

[ponic]

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...]

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...

[ponic]

That make sense. Great answer as always.

Regards

[Tim...]