Retrieving multiple Oracle cursor variables 
Author Message
 Retrieving multiple Oracle cursor variables

Hey all,

I'm in the process of streamlining an application I wrote in VB6 using ADO.
I have a particular class that currently takes two database fetches to
populate itself and I'm trying to reduce that down to one call.

The strategy I'm working on is calling a PL/SQL package that returns both
recordsets I need. The PL/SQL spec looks like so:

TYPE t_cursortype IS REF CURSOR;
PROCEDURE get_cursorvars(io_cursor1 IN OUT t_cursortype, io_cursor2 IN OUT
t_cursortype);

The problem: If the procedure returns one cursor variable, I can snag it
from ADO directly into a recordset just by using the following syntax for
the SQL statement for that recordset.

strSQL = "{call test.get_cursor_vars({resultset 0, io_cursor1})}"

I can't for the life of me figure how/if I can get two recordsets populated
from that one single call to the PL/SQL procedure.

If any of you know how to do this, you will have singlehandedly earned my
eternal gratitude and that of my users. (Enticing, isn't it?!?)

Thanks,

Jason



Sun, 24 Oct 2004 03:33:38 GMT  
 Retrieving multiple Oracle cursor variables
Jason,

Returned cursors must not be explicitly bound variables (ref cursor type
variables are automatically bound by the provider).

If this is the declaration of the procedure in your package(note that the
cursors are OUT only parameters):
TYPE t_cursortype IS REF CURSOR;
PROCEDURE get_cursorvars(io_cursor1 OUT t_cursortype, io_cursor2 OUT
t_cursortype);

then this is the syntax for the ADO Command Execute method.
strSQL = "{call test.get_cursor_vars()}"
cmd.CommandText = strSQL
Set recSet1 = cmd.Execute

To get the next recordset from a stored procedure that returns multiple
recordset, use the NextRecordset method on the first recordset object
returned:
Set recSet2 = recSet1.NextRecordset

I hope this helps.

best regards,
roy fine


Quote:
> Hey all,

> I'm in the process of streamlining an application I wrote in VB6 using
ADO.
> I have a particular class that currently takes two database fetches to
> populate itself and I'm trying to reduce that down to one call.

> The strategy I'm working on is calling a PL/SQL package that returns both
> recordsets I need. The PL/SQL spec looks like so:

> TYPE t_cursortype IS REF CURSOR;
> PROCEDURE get_cursorvars(io_cursor1 IN OUT t_cursortype, io_cursor2 IN OUT
> t_cursortype);

> The problem: If the procedure returns one cursor variable, I can snag it
> from ADO directly into a recordset just by using the following syntax for
> the SQL statement for that recordset.

> strSQL = "{call test.get_cursor_vars({resultset 0, io_cursor1})}"

> I can't for the life of me figure how/if I can get two recordsets
populated
> from that one single call to the PL/SQL procedure.

> If any of you know how to do this, you will have singlehandedly earned my
> eternal gratitude and that of my users. (Enticing, isn't it?!?)

> Thanks,

> Jason



Mon, 25 Oct 2004 00:03:27 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Return Multiple Ref Cursors from Oracle

2. How to retrieve multiple IP address info from my computer's multiple NIC

3. Storing and retrieving custom cursor images using an ImageList

4. retrieve Cursor HANDLE

5. Retrieve default Cursors of Windows??

6. Retrieve default Cursors of Windows??

7. Retrieve default Cursors of Windows??

8. Retrieve default Cursors of Windows??

9. oracle ref cursors / dropdown menu and labels

10. oracle cursors not releasing

11. ADO and Oracle Ref Cursor

12. Oracle SP, ref Cursor with VB6

 

 
Powered by phpBB® Forum Software