With collections, it is possible to return a table from a pl/sql function.

First, we need to create a new object type that contains the fields that are going to be returned:

Click here for more help on Object type and Collection.

*************************************

create or replace type emp_type as object

(

EMPNO NUMBER(4),

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(4),

HIREDATE DATE,

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER(2)

)

/

*************************************

Then, out of this new type, a nested table type is created.

*************************************

create or replace type emp_nested_table as table of emp_type;

/

*************************************

Now, we're ready to actually create the function:

*************************************

/* Formatted on 2008/05/09 14:37 (Formatter Plus v4.7.0) */

create or replace function return_table return emp_nested_table as

v_return_tab emp_nested_table ;

begin

v_return_tab := emp_nested_table();

v_return_tab.extend;

v_return_tab(v_return_tab.count) := emp_type(

7369, 'SMITH', 'CLERK', 7902, TO_Date( '12/17/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')

, 800, NULL, 20);

v_return_tab.extend;

v_return_tab(v_return_tab.count) := emp_type(

7499, 'ALLEN', 'SALESMAN', 7698, TO_Date( '02/20/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')

, 1600, 300, 30);

v_return_tab.extend;

v_return_tab(v_return_tab.count) := emp_type(

7788, 'SCOTT', 'ANALYST', 7566, TO_Date( '12/09/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')

, 3000, NULL, 20);

return v_return_tab;

end return_table;

/

*************************************

Here's how the function is used:

*************************************

select * from table(return_table);

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-12-2008	800.00		20.00
7499	ALLEN	SALESMAN	7698	20-02-1981	1,600.00	300.00	30.00
7788	SCOTT	ANALYST	7566	09-12-2008	3,000.00		20.00

*************************************

Returning a dynamic set

Now, the function is extended so as to return a dynamic set.

The function will return the row values from emp whose hiredate is in the range that is passed to the function.

*************************************

/* Formatted on 2008/05/09 16:17 (Formatter Plus v4.7.0) */

CREATE OR REPLACE FUNCTION ret_type (frhiredate varchar2, tohiredate varchar2)

RETURN emp_nested_table

AS

ret_table emp_nested_table;

BEGIN

SELECT CAST (MULTISET (SELECT *

FROM emp

WHERE to_char(hiredate,'DD-MM-YYYY') BETWEEN frhiredate AND tohiredate) AS emp_nested_table

)

INTO ret_table

FROM DUAL;

RETURN ret_table;

END;

/

*************************************

And here's how the function is called.

select * from table(ret_type(ret_type ('01-01-2008','01-12-2008'));

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-11-2008	800.00		20.00
7499	ALLEN	SALESMAN	7698	20-02-2008	1,600.00	300.00	30.00
7788	SCOTT	ANALYST	7566	09-07-2008	3,000.00		20.00