Re: How to return array of values from procedure?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 15 Mar 1999 18:20:47 GMT
Message-ID: <36f14f62.19925301@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 08:56:24 -0800, you wrote:

>How to return array of values from procedure?
>
>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> show err
No errors.

SQL> 
SQL> 
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> 
SQL> 
SQL> 
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.

>The called procedure looks like this:
>CREATE OR REPLACE PROCEDURE get_children (
> input_parent_id IN NUMBER
>-- , output_child_code OUT VARCHAR2
> )
> IS
> var_child_code VARCHAR2(10);
> CURSOR temp_cursor (cursor_parent_id NUMBER) IS
> SELECT child_code
> FROM child_table
> WHERE parent_id = cursor_parent_id
> ORDER BY child_code;
>
>BEGIN
> OPEN temp_cursor(input_parent_id);
> LOOP
> FETCH temp_cursor INTO var_child_code;
> EXIT WHEN temp_cursor%NOTFOUND;
> DBMS_OUTPUT.PUT_LINE(var_child_code);
>-- output_child_code := var_child_code;
> END LOOP;
> CLOSE temp_cursor;
>END get_children;
>/
>
>
>As written, this procedure will successfully write the appropriate
>Child_Code values to the screen in SQL*Plus. But when I uncomment the lines
>related to created output to send to a calling procedure, and then call it,
>I get the following error message in return:
>
> PLS-00306: wrong number or types of arguments in call to
>'GET_CHILDREN'
>
>Can anyone tell me how to get the values back into the calling procedure?
>
>Thanks for all assistance.
>
>
 

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 - 12:20:47 CST