In this post I'm going to share some examples of passing collections as an out variable from Oracle PLSQL Stored Procedures and also Returning a collection object from a function and using it in a query to directly populate a Oracle Table.



Step 1:
Create an Oracle Table
create table address
(
   street VARCHAR2 (32),
   city VARCHAR2 (20),
   state CHAR (2),
   postal_code VARCHAR2 (6)
);

Step 2:
Create a collection of record TYPE. same as the above table.
CREATE OR REPLACE TYPE address_typ AS OBJECT
(
   street VARCHAR2 (32),
   city VARCHAR2 (20),
   state CHAR (2),
   postal_code VARCHAR2 (6)
);

Step 3:
Create a Collection Table TYPE of the above record Type object.
create or replace TYPE address_tab AS table of address_typ;

Step 4:
Create a procedure that constructs and returns the above table type as an out parameter
CREATE OR REPLACE PROCEDURE set_address_list (p_address_tab OUT address_tab)
AS
BEGIN
   p_address_tab := NEW address_tab ();
   p_address_tab.EXTEND;

   p_address_tab (1) :=
      address_typ ('A street',
                   'Fremont',
                   'CA',
                   '94536');

   p_address_tab.EXTEND;

   p_address_tab (2) :=
      address_typ ('B street',
                   'Fremont',
                   'CA',
                   '94536');
END;

Step 5:
Create a function that calls the above procedure and receives the collection object from the procedure and returns it out.
CREATE OR REPLACE FUNCTION get_address_list
   RETURN address_tab
AS
   v_address_tab   address_tab := NEW address_tab ();
BEGIN
   set_address_list (v_address_tab);

   RETURN v_address_tab;
END;

Step 6:
Call the function from a SQL query FROM clause using the TABLE() function and insert it directly into the above table.

INSERT INTO address
   SELECT * FROM TABLE (get_address_list);

--2 Records will be inserted

Step 7:
Query the address table.

SELECT * FROM address;

STREET                           CITY                 STATE POSTAL_CODE
-------------------------------- -------------------- ----- -----------
A street                         Fremont              CA    94536      
B street                         Fremont              CA    94536      

2 rows selected.