Re: How to return array of values from procedure?

From: <rsamuel_thomas_at_hotmail.com>
Date: Thu, 18 Mar 1999 02:31:01 GMT
Message-ID: <7cpoh5$jt8$1@nnrp1.dejanews.com>

In article <7cjdbs$kqk$1_at_news-1.news.gte.net>,   "John Haskins" <76054.334_at_compuserve.com> 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.
>
> 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.

Well u shud put these procedures in one package.

and in the package spec declare a PL/SQL table of type number or something ( or the type of u want )

TYPE myTable is of number(4) indexed by binary integer. ( check the syntax pls )

and in the calling procedure u declare a variable of myTable as and OUT parameter.

In the procedure where u r getting the var_child_code, declare another table of type myTable like,

vChildCodeArray myTable;

and in the loop where u fetch the child code value,populate the above defined table. like

   vChildCodeArray(1) := ( fetched value )

or initialize a variable i to 1 and

i:=1;
LOOP

         FETCH temp_cursor INTO vChildCodeArr(i);
         EXIT WHEN temp_cursor%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE(var_child_code);
         i:=i+1;

END LOOP; Mail me if u have any questions ok ..

Rony

>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Mar 17 1999 - 20:31:01 CST