Return Multiple Values From Oracle Function

I want to create a function that returns multiple rows into a table that is of object type. I have created an object and a nested table object and now when I run the function ther

Solution 1:

Update:

do you know how to modify this for scenario whereby I have an sql statement contained in a string variable to execute?

Yes, we can use a cursor reference (SYS_REFCURSOR) and OPEN/FETCH/CLOSE instead of a CURSOR and CURSOR FOR LOOP.

The syntax is OPEN <cursor-reference> FOR <string-containing-sql-statement> . See below.

CREATEOR REPLACE FUNCTION load_test_object_sn
RETURN test_otable_sn
AS  
  details test_otable_sn := test_otable_sn();

  -- Variable stores SQL statement for cursor
  l_sql CLOB :=
    q'[with ad as (
         select 'a' column_1, 'b' column_2, 4 column_3 from dual union all
         select 'r', '5', 3  from dual union all 
         select 'g', 's', 3  from dual
       )
       select *
         from ad]';

  -- Cursor reference allows us to open cursor for SQL statement above
  rc SYS_REFCURSOR;

  -- Define object instance to store each row fetched from the cursor
  l_obj test_object_sn := test_object_sn(NULL, NULL, NULL);

  i PLS_INTEGER :=1;
BEGIN-- Explicitly open, fetch from, and close the cursorOPEN rc FOR l_sql;
  LOOP
    FETCH rc INTO l_obj.column_1, l_obj.column_2, l_obj.column_3;
    EXIT WHEN rc%NOTFOUND;
    details.extend();
    details(i) := test_object_sn(l_obj.column_1, l_obj.column_2, l_obj.column_3);
    i := i +1;
  END LOOP;
  CLOSE rc;

  RETURN details;
END;

Original answer:

Unfortunately, one can't use SELECT * INTO with a collection in this manner, so here's an alternative way to populate the table:

createor replace function load_test_object_sn
return test_otable_sn
as  
    details test_otable_sn := test_otable_sn();
    cursor c_ad iswith ad as (select'a' column_1, 'b' column_2, 4 column_3   from dual
    unionallselect'r', '5', 3from dual
    unionallselect'g', 's', 3from dual)
    select*from ad;
    i pls_integer :=1;

beginfor ad_rec in c_ad loop     
      details.extend();
      details(i) := test_object_sn(ad_rec.column_1, ad_rec.column_2, ad_rec.column_3);
      i := i +1;
   end loop;

    return details;
end;
/

Output:

SQL>SELECT*FROMTABLE(load_test_object_sn);

COLUMN_1   COLUMN_2     COLUMN_3
---------- ---------- ----------
a          b                   4
r          53
g          s                   3