Wednesday, July 25, 2007

Create a function that returns a table

In SQL Server it is easy to create a function that returns a table when used in the from clause. In Oracle this is not as easy as it seems but since my current client uses Oracle, I was forced to find a solution so here it is.

To create a function that returns a table in Oracle you need to follow a few steps:

Create a type that corresponds to a single record

Create a type that corresponds to a table and that has the previously declared type as recordtype

Create the function

To use the function, you need to include it in the TABLE() function.

Here is an example on how to do it:

First I create a simple table that will be used for the sample.

-- Create a dummy table to show how it works.-- The table contains a hierarchy between boss -- and employee.CREATETABLE employee( emp_id NUMBER(10), full_name VARCHAR2(50), emp_boss_id NUMBER(10));

-- Create a type that corresponds to a row that you want to have returned.CREATE TYPE ind_employee AS OBJECT (emp_id NUMBER(10), full_name VARCHAR2(50));-- Create a type that corresponds to a table of rows with the specified type.CREATE TYPE ind_employee_table ASTABLEOF ind_employee;

REMARKS: The PIPELINED command and PIPE ROW() method, work together to make sure that each row is returned to the caller immediately after it is processed. This helps to avoid having big objects in memory.