Re: How to return array of values from procedure?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 15 Mar 1999 22:07:09 GMT
Message-ID: <36f3846a.33501933@192.86.155.100>

A copy of this was sent to "John Haskins" <76054.334_at_compuserve.com> (if that email address didn't require changing) On Mon, 15 Mar 1999 13:08:35 -0800, you wrote:

>Thomas:
>
>Thanks for the reply. You've helped me out before; nice to hear from you
>again.
>
>Now, to your answer. I appreciate the information. I was already able to
>get the returned dataset to display via dbms_output, although the code you
>sent is a much tighter way to get the same result. What I need to figure
>out now is how to have the resulting dataset get returned to whatever
>procedure called the "get children records" routine. That transfer of data
>back to the calling procedure is the part I don't understand. Any
>assistance you can provide would be greatly appreciated!
>
>Thanks again.
>

I thought that is what I showed?? I built 2 routines -- get_children_1 and _2. Each return a result set of sorts to their caller. Then I used 2 anonymous pl/sql blocks to call each one. So, for example:

SQL> create or replace

  2  procedure get_children_1( P_parent_id      in number,
  3                            p_child_codes out MY_TYPES.idArray )

4 as
5 begin

  6      for x in ( select empno, rownum r from emp where mgr = p_parent_id )
  7      loop
  8          p_child_codes( x.r ) := x.empno;
  9      end loop;

10 end;
11 /

Procedure created.

is called from another procedure/block of code as such:

SQL> declare
2 child_codes my_types.idArray; 3 begin
4 get_children_1( 7839, child_codes ); 5

  5      for i in 1 .. child_codes.count
  6      loop
  7          dbms_output.put_line( child_codes(i) );
  8      end loop;

9 end;
10 /

change the declare in the above snippet to "create or replace procedure foo as" and it is another procedre that calls the get_children_1 procedure to get the data and do something with it.

The other example was a variation on a theme (instead of returning an array, it returned a cursor to the answer)

does that make sense?

>
>

[snip]

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

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Mar 15 1999 - 16:07:09 CST