return an array of data from a pl/sql function

For example in the function
Enter
Select task_status
      From task_table
Where task_id =  passed_id

Load the results of the selects into an array
Return the array back to the module that called the function

Within  the module that called the function, how do I iterate through the array to get the values? How to I determine the number of times to loop through the array with ?

Could you please give me an example?
cookiejarAsked:
Who is Participating?
 
yuchingConnect With a Mentor Commented:
Perhaps this help

DECLARE
  TYPE tArray IS TABLE Of Varchar2(100) INDEX BY Binary_Integer;
  tStatusArray tArray;
  l Integer;
FUNCTION get_array RETURN tArray IS
  t tArray;
  i  INTEGER := 1;  
  BEGIN
      FOR r In (Select task_status From task_table Where task_id =  passed_id)
      Loop
        t(i) := r.task_status;
        i := i +1;
      END LOOP;
    RETURN t;
  END get_array;
 
BEGIN
-- interate the array from function
   tStatusArray := get_array;
   For l In tStatusArray.First .. tStatusArray.Last
   Loop
          dbms_output.put_line(tStatusArray(l));
   End Loop;
END;
0
 
sdstuberConnect With a Mentor Commented:
Here's a simple example showing how to build an associative array with a query, return that array from a function.

call the function from a procedure which then iterates through the generated array printing each element
CREATE OR REPLACE PACKAGE test_pkg
IS
    TYPE array_of_tables
    IS
        TABLE OF all_tables.table_name%TYPE
            INDEX BY BINARY_INTEGER;
 
 
    FUNCTION get_tables(p_owner IN VARCHAR2)
        RETURN array_of_tables;
 
    PROCEDURE print_tables(p_owner IN VARCHAR2);
END;
 
 
 
CREATE OR REPLACE PACKAGE BODY test_pkg
IS
    FUNCTION get_tables(p_owner IN VARCHAR2)
        RETURN array_of_tables
    IS
        v_array   array_of_tables;
    BEGIN
        SELECT   table_name
          BULK   COLLECT
          INTO   v_array
          FROM   all_tables
         WHERE   owner = p_owner;
 
        RETURN v_array;
    END get_tables;
 
    PROCEDURE print_tables(p_owner IN VARCHAR2)
    IS
        v_array   array_of_tables;
    BEGIN
        v_array   := get_tables(p_owner);
 
        FOR i IN 1 .. v_array.COUNT
        LOOP
            DBMS_OUTPUT.put_line(v_array(i));
        END LOOP;
    END;
END;
 
begin
test_pkg.print_tables('SOME_USER');
end;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.