| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to return array of values from procedure?
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
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;
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
6 open p_child_codes for 7 select empno from emp where mgr = p_parent_id;8 end;
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;
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;
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
 |
 |