Re: How to return array of values from procedure?

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.

>On Mon, 15 Mar 1999 08:56:24 -0800, you wrote:
>
>>I know that for those of you who have done it, this is a newbie question,
>>but I've read all the Oracle docs and the Oracle Press PL/SQL programming
>>book on this, and I haven't found an answer to this simple question yet.
>>
>>I want to create a procedure that, when called by another procedure,
returns
>>IDs of records in a child table that match the criterion of a parent ID.
For
>>instance, given a child table structure like this:
>> Parent_ID (PK) (FK)
>> Child_Code (PK)
>> Child Attribute 1
>> Child Attribute 2
>>
>>...I would like to feed the procedure a Parent_ID, and have it return all
>>the Child_Codes that are related to that Parent_ID. Simple, yes?
>>
>>I have been successful at creating the procedure to receive the Parent_ID
>>and determine the related Child_Codes. I can print them to a SQL*Plus
>>screen using DBMS_OUTPUT. But I can't figure out how to return them to
the
>>calling procedure. Each time I add an OUT parameter to the called
procedure,
>>subsequent calls to the procedure fail.
>>
>
>
>Here are two examples of returning arrays of data. one uses a pl/sql
table, the
>other returns a cursor (set) of data...
>
>SQL> create or replace package my_types
> 2 as
> 3 type refCur is ref cursor;
> 4
> 4 type idArray is table of number index by binary_integer;
> 5 end;
> 6 /
>
>Package created.
>
>SQL>
>SQL>
>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.
>
>SQL> show err
>No errors.
>SQL>
>SQL> create or replace
> 2 procedure get_children_2( P_parent_id in number,
> 3 p_child_codes in out MY_TYPES.refCur )
> 4 as
> 5 begin
> 6 open p_child_codes for
> 7 select empno from emp where mgr = p_parent_id;
> 8 end;
> 9 /
>
>Procedure created.
>
>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 /
>
>PL/SQL procedure successfully completed.
>
>SQL> declare
> 2 child_codes my_types.refCur;
> 3 l_child_code number;
> 4 begin
> 5 get_children_2( 7839, child_codes );
> 6
> 6 loop
> 7 fetch child_codes into l_child_code;
> 8 exit when child_codes%notfound;
> 9
> 9 dbms_output.put_line( l_child_code );
> 10 end loop;
> 11 end;
> 12 /
>
>PL/SQL procedure successfully completed.
>
>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 - 15:08:35 CST