[SQL] Oracle function return table dataset multiple rows

In Oracle, procedures may return multiple values as OUT parameters while functions should always return a single value. Functions CAN however return multiple values, but they must be enclosed in a TABLE object which is the "single value" to be returned.



Basically, you will need to:

1) define the row object type:

CREATE OR REPLACE TYPE t_mytype AS OBJECT(
column1 [TYPE],
column2 [TYPE]
);

of course, it can have as many columns as you like

2) define an object as a table of the previously created row type:

CREATE OR REPLACE TYPE t_mytype_tab AS TABLE OF t_mytype;

this will NOT create a new table, just an object which can contain multiple rows and multiple columns as specified by the t_mytype definition. You will be able to access the data stored inside it as long as the object exists; you may pass it to other functions/procedures as you would for an argument as well

3) declare the function which returns said table object:

CREATE OR REPLACE FUNCTION myfunction(
inparam1 [TYPE],
inparam2 [TYPE]
--as many input parameters as you need
)
RETURN t_mytype_tab
IS
--declarations
return_table t_mytype_tab;--return object
i INTEGER; --counter
BEGIN
--initialize our return_table object and counter
return_table := t_mytype_tab();
i:=0;
--fill our return object, there are many ways in which to do so, a possible one is with a for loop with cursors
FOR cur IN ([YOUR SELECT QUERY]) LOOP
--increment counter

i:=i+1;

--add row to return object
return_table.extend();

--fill this new row with one row from the QUERY results

--NOTE: since we declared our type t_mytype as a row with 2 columns, you MUST fill it with exactly 2 values, otherwise you may get the ORA-00947 error
return_table(i):= t_mytype(cur.query_column1, cur.query_column2);
END LOOP;
RETURN return_table;
END myfunction;

you may also use the SELECT INTO syntax to store values inside your object

4) finally, you can call the function as:

SELECT *
FROM TABLE(myfunction(param1, param2));