DOES ANYBODY KNOW THE ANSWER TO THIS QUESTION?????

[Adam F. Kelley]

I am trying to figure out how to pass multiple results from a stored
procedure to a client ODBC app.

For example :

CREATE or REPLACE PROCEDURE WhatEver
AS
BEGIN
SELECT * FROM Table ;
END;

I can perform these types of stored procs. in other RDBMS's (eg MS-SQL,
Sybase). I have heard from a few sources that this type of stored proc.
is not possible under Oracle, on the other hand I have heard from other
sources that there is a way of doing this sort of thing (Of course those
same people can't tell me how...). I need to be able to call Oracle
stored procs. from custom client apps. as well as SW-Apps. like Crystal
Report Writer. It needs to be able to return multiple rows of data.

Anybody that has any suggestions on this subject, I would certainly
appprecitate hearing from you.

Thnx,

AFK

[Nathan D. Hughes]

"Adam F. Kelley" <afke...@southeast.net> writes:

>I am trying to figure out how to pass multiple results from a stored
>procedure to a client ODBC app.

I'm not sure I understand your application specific question, but the
answer to your real question, "how do I return multiple row set", is to
use explicit cursors. This is covered in all the manuals regarding pl/sql
programming. An example would be:

declare
cursor c1 is
select * from foo;
c1_rec c1%rowtype;
begin
open c1;
loop
fetch c1 into c1_rec;
exit when c1%notfound;
/* process your row */
end loop;
close c1;
end;
/

There's plenty of different ways to manipulate explicit cursors - differnt
looping mechanisms, row storage/retrievel, etc.
--

Nathan D. Hughes <nhu...@well.com>
Computer Consultants of America, Inc.

[Stubler]

Adam F. Kelley wrote:
>
> I am trying to figure out how to pass multiple results from a stored
> procedure to a client ODBC app.
>

> For example :
>
> CREATE or REPLACE PROCEDURE WhatEver
> AS
> BEGIN
> SELECT * FROM Table ;
> END;
>
> I can perform these types of stored procs. in other RDBMS's (eg MS-SQL,
> Sybase). I have heard from a few sources that this type of stored proc.
> is not possible under Oracle, on the other hand I have heard from other
> sources that there is a way of doing this sort of thing (Of course those
> same people can't tell me how...). I need to be able to call Oracle
> stored procs. from custom client apps. as well as SW-Apps. like Crystal
> Report Writer. It needs to be able to return multiple rows of data.
>
> Anybody that has any suggestions on this subject, I would certainly
> appprecitate hearing from you.
>
> Thnx,
>
> AFK

A solution to your problem could be this:

Create a package with a Cursor Variable (global to your session!)

Write a procedure that that opens der cursor the first time called and
fetches rows in every succesive calls until there are no more rows.

To get an optimal client/server performance you should use ps/sql tables
as output parameters of your procedure to utilize teh oracle host array
interface.

I hope that helps

regards

Dieter Stubler
topIT Informationstechnlogie GmbH

[Matthias Gresz]

Adam F. Kelley wrote:
>
> I am trying to figure out how to pass multiple results from a stored
> procedure to a client ODBC app.
>
> For example :
>
> CREATE or REPLACE PROCEDURE WhatEver
> AS
> BEGIN
> SELECT * FROM Table ;
> END;
>
> I can perform these types of stored procs. in other RDBMS's (eg MS-SQL,
> Sybase). I have heard from a few sources that this type of stored proc.
> is not possible under Oracle, on the other hand I have heard from other
> sources that there is a way of doing this sort of thing (Of course those
> same people can't tell me how...). I need to be able to call Oracle
> stored procs. from custom client apps. as well as SW-Apps. like Crystal
> Report Writer. It needs to be able to return multiple rows of data.
>
> Anybody that has any suggestions on this subject, I would certainly
> appprecitate hearing from you.
>
> Thnx,
>
> AFK

Hi,

if you intend to do something like Access's querires can do then you'll
be dissapointed 'cause Oracle won't to this.
--
Regards

Matthias Gresz :-)