title: Create an Oracle function that returns a table
body: <p>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.</p>
  
  <p>So far, this is what I have:</p>
  
  <p>Header:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Body:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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.</p>
  
  <p>What am I missing?</p>