Create an Oracle function that returns a table
- by Craig
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?
