Re: stored procedures

From: Phil Haigh <phil_haigh_at_bigfoot.com>
Date: Wed, 10 Nov 1999 21:58:41 -0000
Message-ID: <942271111.4915.0.nnrp-02.d4e52d42@news.demon.co.uk>

Larry,

> If a store procedure returns only one row, I use the
> select/into format, otherwise I use a cursor with a loop.
>
> Is there any other way to return multiple rows. We're using a visual c++
> client and it works with only one row but it's not working when the
stored
> procedure (with a cursor) returns many.
>
> It works fine both ways in SQL *PLUS
>
> Any suggestions?
> Thanks.
>

I would suggest you use a database package along the following lines: - Procedure 'A' in the package executes the query, populating the data into a record set and setting an index variable to the first record. You call this prior to retrieving any rows into your 'C' program. - Procedure 'B' in the package returns the 'next record' as defined by the index variable, incrementing it in the process.

In this manner you can retrieve all the data from the select, albeit somewhat indirectly, into your C++ client.

I've done this on an Oracle 7 database (7.3.4, PL/SQL 2.3). It may be possible to do something more 'objecty' with Oracle 8 but I've not had chance to bone up on PL/SQL 8 yet.

Incidentally if you're interested in a sample of 'hwo its done' I can mail my source over to you. I'd do it now but it's in the office...

Phil.

Phil Haigh is phil_haigh_at_bigfoot.com.

Tel (Home): 01952 248040
Tel (Work): 01952 292680
Fax (Home):0870 056 2448
Fax (Work): 01952 291016

Web: http://www.bigfoot.com/~phil_haigh Fantasy F1: http://www.bigfoot.com/~fantasy_f1 Received on Wed Nov 10 1999 - 15:58:41 CST