Oracle Table Functions

An Oracle table function returns a collection that can be queried as if it were a table.

For example.

SELECT * FROM TABLE(my_function);

The TABLE keyword informs Oracle that the collection produced by my_function should be treated as a table in this query.

Example

To demonstrate this, let's create a simple function which generates a list of dates for a specified range, which could be used as follows.

SELECT column_value gen_date
FROM TABLE (date_list('1-jan-08', '5-jan-08'));

gen_date                   
----------------
01-jan-08
02-jan-08
03-jan-08
04-jan-08
05-jan-08

5 rows selected

Incidentally, this article provides a simple solution for generating rows, including dates in a range, using the CONNECT BY LEVEL clause.

The DATE_LIST function

First, we create a collection to be used as the return type from the function. Here we use a nested table but a VARRAY would also be valid.

CREATE TYPE interval_tab
 AS TABLE OF date;
/

Type created.

Now we can go ahead and create the date_list function.

CREATE OR REPLACE FUNCTION date_list(p_start_date IN DATE, 
                                     p_end_date IN DATE) 
RETURN interval_tab AS
  l_interval interval_tab := interval_tab();
  l_days binary_integer := p_end_date - p_start_date + 1;
BEGIN

IF (l_days < 0) THEN
    RETURN null;
  END IF;

l_interval.extend(l_days);

FOR i IN 1 .. l_days
  LOOP
    l_interval(i) := p_start_date + i -1;
  END LOOP;

RETURN l_interval;
END;
/

Function created.

This function populates a nested table by adding an incrementing value to the start date.

Using the table function

One use for this function would be to generate a list of dates to be used in an outer join. For example, the following query displays the number of schema objects created on each day of a specified date range.

SELECT to_char(column_value, 'dd-Mon-yy') created, nvl(cnt, 0) cnt
FROM TABLE(date_list('1-feb-08',   '5-feb-08'))
LEFT OUTER JOIN
  (SELECT TRUNC(created, 'dd') created, COUNT(*) cnt
   FROM all_objects
   GROUP BY TRUNC(created, 'dd'))
ON created = column_value
ORDER BY column_value;

CREATED        CNT                    
--------- --------
01-Feb-08        4                      
02-Feb-08        0                      
03-Feb-08        0                      
04-Feb-08        1                      
05-Feb-08        2

5 rows selected

Using the date_list function and an outer join we can display a row per day, including those days that have no entries in the ALL_OBJECTS table.

Oracle table functions can also be pipelined which allows the data to be returned iteratively as it is produced, rather than all at once when the function has run to completion.