Re: How to return array of values from procedure?

From: <wjleee_at_my-dejanews.com>
Date: Mon, 15 Mar 1999 22:14:08 GMT
Message-ID: <7ck0n5$l52$1_at_nnrp1.dejanews.com>


Have you tried the PL/SQL table? It might be what you are looking for.

  "John Haskins" <76054.334_at_compuserve.com> 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.
>
> 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.
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Mar 15 1999 - 23:14:08 CET

Original text of this message