Solved

return an array of data from a pl/sql function

Posted on 2009-04-01
3
1,778 Views
Last Modified: 2013-12-07
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?
0
Comment
Question by:cookiejar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 11

Accepted Solution

by:
yuching earned 250 total points
ID: 24045763
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 24045847
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

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
exp/imp 25 100
how to find out the count of records based on the subfolders paths 11 39
Read XML values 8 56
format dd/mm/yyyy parameter 16 48
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question