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

User · Answer

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.

User · Answer

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   MEASURE RECORD L4 ID   L6 ID  L8 ID  YEAR                PERIOD VALUE    BULK COLLECT  INTO    T     FROM                   RETURN T      END GET UPS   END TEST

User · Answer

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

[plsql] Create an Oracle function that returns a table

Examples related to plsql

Examples related to oracle10g

Examples related to package