Re: "return with resume" in Oracle8?

A copy of this was sent to adil_at_msil.sps.mot.com (if that email address didn't require changing) On Tue, 14 Sep 1999 08:43:31 GMT, you wrote:

>Hi there,
>
>We're migrating our database from an old Informix
>database to an Oracle 8 one. However, I could not
>find out how Oracle stored procedures implement
>what Informix calls "return with resume". The
>return with resume feature allows a procedure to
>return a set of values of the same type, similar
>to several rows returned by a select statement.
>
>For example, if I have a table called user_groups,
>which holds two columns, user id and group id, and
>I wanted to write a procedure which given a group
>id returns a list of user ids associated with it,
>in Informix, I would write something like,
>

You would return a CURSOR variable in Oracle. So the code would look like:

create procedure getUsers ( proc_group_id int, THE_CURSOR in out types.refCur ) as
begin

   open the_cursor for

         select user_id into proc_user_id
           from user_groups 
        where group_id=proc_group_id;

You would fetch from this on the client as you would from any other cursor type.

See the website in my url for examples of this (returning result sets from stored procedures) in various languages.

>Procedure getUsers(proc_group_id int)
>begin
> define proc_user_id int;
> .
> .
> foreach select user_id into proc_user_id
> from user_groups
> where group_id=proc_group_id
> return proc_user_id with resume;
> end foreach;
>end procedure;
>
>Or something like that. Obviously, this is just
>an example to demonstrate what I mean; I don't
>need a procedure for the above case (I could just
>use a select). How is this sort of thing done in
>Oracle stored procedures (Oracle 8)?
>
>Thanks,
>
>Adi.
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

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

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Sep 14 1999 - 09:36:26 CDT