stored procedures 
Author Message
 stored procedures
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.



Wed, 18 Jun 1902 08:00:00 GMT
 stored procedures

Larry,

Quote:
> 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.


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



Wed, 18 Jun 1902 08:00:00 GMT
 stored procedures

(if that email address didn't require changing)

Quote:

>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.

Returing result sets is easy from a stored procedure -- you'll use a ref cursor
(a cursor opened in the stored procedure, fetched from on the client).

See the url in my signature for examples in various programming languages.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st


Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Calling a Java Stored Procedure from another Java Stored Stored Procedure

2. Stored Procedure calling Stored Procedure

3. Executing a Stored Procedure in a Stored Procedure and selecting on the result

4. Call a stored procedure from another stored procedure

5. Calling a stored procedure with parameters from another stored procedure

6. using SQL stored procedure results in stored procedure

7. Using Resultset in Stored Procedure in another stored procedure

8. Stored Procedure using another Stored Procedure

9. Calling a Stored Procedure from a Stored Procedure

10. How to call an Oracle Stored procedure from MSSQL Server Stored Procedure

11. Calling a stored procedure with parameters from another stored procedure

12. Stored procedure calls multiple stored procedures


 
Powered by phpBB® Forum Software