RE: Executing Stored Procedures

At 21:51 24/05/1997 -0300, Marisa Moretto wrote: } } }---------- }De: Bill Ennis[SMTP:ennis@ssax.com] }Enviado: Viernes, Mayo 23, 1997 12:43 }Para: Mike Gallagher }Cc: informix-list@rmy.emory.edu }Asunto: Re: Executing Stored Procedures } }} }} I am currently using Sybase/db-lib and I routinely execute }} stored procs that return multiple (different) result sets. I know }} Informix is limited to one result set per stored procedure. Does anyone }} know if this is also true in Oracle. An example of a stored procedure is }} as follows: }} }} create procedure test1 }} @arg1 int, }} @arg2 float }} as }} select }} col1, }} col2 }} from }} table1 }} where }} col3 = @arg1 }} }} select }} col4, }} col5 }} from }} table2 }} where }} col6 = @arg2 }} go }} }} NOTE: the datatypes of the results do not need to be defined and can be }} different within the 2 result sets. }} }} Michael Gallagher }} Open Link Financial }} Email: mgallagh@olf.com }} Phone: (516) 227-6600 x212 }} } }Mr.Bill: } } The next procedure is one example that return more than one row: } }create procedure xx() returning integer; } } define ver_ret integer; } } foreach } select order_num } into var_ret } from orders } return var_set with resume; } end foreach; } return -- this sentence return status = 100 (not found) at the aplication }end procedure; } }And the aplication, must be have: } } prepare prep_stmt from "execute procedure xx()" } declare traer cursor for prep_stmt } foreach traer into var_order_num } .... } .... } end foreach } } I hope help you. } } } Saludos Gabriel. } } } } } } } } } I thonk the original question refers to the fact that sybase can return multiple return sets with a single call to a stored procedure with no implicit (foreach) or explicit cursors, and without a loop on the client side. -- Oscar Goldes -- Orden Argentina SA