How to return array of values from procedure?

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. Received on Mon Mar 15 1999 - 10:56:24 CST