Re: Too Many Cursors?

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Tue, 04 Oct 2005 19:47:01 +0200
Message-ID: <dhuf5s$f64$1@ctb-nnrp2.saix.net>

Helge Moulding wrote:

>>The ref cursor struct and resources are only released when the session
>>owning that ref cursor pointer terminates, or when the ref cursor is
>>explicitly closed (e.g. using the PL/SQL call CLOSE).

>
>
> Thanks, Billy. So what I was trying to do is a generally bad idea?
> Is there a way that a function can return several columns at once,
> without using a ref cursor? I looked at a piped function, but it
> seemed as if that would get me no advantage over just selecting
> from the joined tables.

Ref cursors are great in that it does not need SQL in the client to get to the data. The client calls an API (written in PL/SQL) and it hands the client a ref cursor to step through. The PL/SQL side can be changed, optimised, fine-tuned, whatever.. without having to even recompile the client.

The only "issue" (if it can be called that) is that the client should close the ref cursor when it is done with it. Not an unreasonable or illogical request. So ref cursors are by no means a bad idea.

Pipeline functions are ideal for ETL type processing. It should not be used as a substitution for something a plain SQL SELECT can do.

For a function to return several columns, have it return a record struct. This can be a %ROWTYPE data type, a PL/SQL user defined struct (which like will not work for a non-PL/SQL client), or an ADT (Advance Data Type).

E.g.
create or replace type TEmployeeRecord is object (

     id number,
     surname varchar2(100),
     initials varchar2(10)

);

create or replace function GetEmployee( nID number )

   return TEmployeeRecord is
begin

   ...
end;

The OCI8 and later interface support ADTs and a client can "deref" the struct returned to access the members in the struct. If I recall correctly, this was available in IDEs and compilers like Delphi7 (released several years ago).

The old saying of using the right tool for the job applies. A screwdriver makes a poor chissel. There's a bunch of tools in the Oracle PL/SQL toolbox.. determine the requirements and then select the best tool for that. :-)

--
Billy

Received on Tue Oct 04 2005 - 12:47:01 CDT