Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: DOES ANYBODY KNOW THE ANSWER TO THIS QUESTION?????

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

From: Stubler <Stubler_at_t-online.de>
Date: 1997/12/21
Message-ID: <67imvn$5jj$1@news01.btx.dtag.de>#1/1

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
Received on Sun Dec 21 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US