Re: **PLEASE HELP** Oracle Stored Procedures question

Nnoor_at_cris.com (NNOOR) wrote:

> I have many complex queries in my client app which I would like to
> have stored on the server side to cut down on many time/resource
> intensive variables.
>
> All of these queries return multiple rows as result set.
>
> I am using Delphi 2.0 C/S and Oracle WGS 7.3 (NT v4.0).
>
> I can't seem to figure out how to return a result set from the
> Oracle stored procedures. Oracle doesn't allow use of SELECT on
> the stored procs. Delphi does not have a CURSOR data type that I
> could use as an OUT parameter. So, I am lost here.
>
> I played with CURSOR and FETCH commands in the PL/SQL but it seems
> that Oracle fetches all of the rows before returing from the proc
> so I only get the values of the last row back in the calling routine.
>
> I will greatly appreciate any help on the topic. Oracle has a way of
> executing a SELECT and returning the result set thru a cursor, but
> Delphi does not have a CURSOR data type so I can't pass a variable as
> OUT or IN OUT to receive this cursor handle.

General advice, for what it's worth and only my own $0.02 opinion: I use stored procs only if I need some added functionality, read 'processing', besides retrieving a result set. From your intro, I understand that you want them only to transfer complex (existing) queries. I'd define a view for that...

The last thing I read about this topic is that Delphi 3 acually has a cursor data type? One of the first things to check out when I'll receive my upgrade...

Jasper Received on Mon May 19 1997 - 00:00:00 CDT