Create an Oracle function that returns a table

Question

I'm trying to create a function in package that returns a table. I hope to call the function once in the package, but be able to re-use its data mulitple times. While I know I create temp tables in Oracle, I was hoping to keep things DRY.

So far, this is what I have:

Header:

CREATE OR REPLACE PACKAGE TEST AS       TYPE MEASURE_RECORD IS RECORD (       L4_ID VARCHAR2(50),       L6_ID VARCHAR2(50),       L8_ID VARCHAR2(50),       YEAR NUMBER,       PERIOD NUMBER,       VALUE NUMBER     );      TYPE MEASURE_TABLE IS TABLE OF MEASURE_RECORD;      FUNCTION GET_UPS(       TIMESPAN_IN IN VARCHAR2 DEFAULT 'MONTLHY',       STARTING_DATE_IN DATE,       ENDING_DATE_IN DATE       ) RETURN MEASURE_TABLE;  END TEST; 

Body:

CREATE OR REPLACE PACKAGE BODY TEST AS     FUNCTION GET_UPS (     TIMESPAN_IN IN VARCHAR2 DEFAULT 'MONTLHY',     STARTING_DATE_IN DATE,     ENDING_DATE_IN DATE   ) RETURN MEASURE_TABLE IS      T MEASURE_TABLE;    BEGIN          SELECT  ...         INTO    T         FROM    ...        ;    RETURN T;    END GET_UPS;  END TEST; 

The header compiles, the body does not. One error message is 'not enough values', which probably means that I should be selecting into the MEASURE_RECORD, rather than the MEASURE_TABLE.

What am I missing?

Answer 1

I think you want a pipelined table function.

Something like this:

CREATE OR REPLACE PACKAGE test AS      TYPE measure_record IS RECORD(        l4_id VARCHAR2(50),         l6_id VARCHAR2(50),         l8_id VARCHAR2(50),         year NUMBER,         period NUMBER,        VALUE NUMBER);      TYPE measure_table IS TABLE OF measure_record;      FUNCTION get_ups(foo NUMBER)         RETURN measure_table         PIPELINED; END;  CREATE OR REPLACE PACKAGE BODY test AS      FUNCTION get_ups(foo number)         RETURN measure_table         PIPELINED IS          rec            measure_record;      BEGIN         SELECT 'foo', 'bar', 'baz', 2010, 5, 13           INTO rec           FROM DUAL;          -- you would usually have a cursor and a loop here            PIPE ROW (rec);          RETURN;     END get_ups; END; 

For simplicity I removed your parameters and didn't implement a loop in the function, but you can see the principle.

Usage:

SELECT *   FROM table(test.get_ups(0));    L4_ID L6_ID L8_ID       YEAR     PERIOD      VALUE ----- ----- ----- ---------- ---------- ---------- foo   bar   baz         2010          5         13 1 row selected. 

Answer 2

To return the whole table at once you could change the SELECT to:

SELECT  ... BULK COLLECT INTO T FROM    ... 

This is only advisable for results that aren't excessively large, since they all have to be accumulated in memory before being returned; otherwise consider the pipelined function as suggested by Charles, or returning a REF CURSOR.