2008 acetone bladesystem c3000 cleaning collins count dan database debug diamond disposal eyadema flashback gaudeau golf grown lab lacquer lightsheer long membrane mpio nologging oil oracle orleans polystyrene querie query record recovery recycler recycling scn select server software solvent SYSTIMESTAMP table tetrahydrofuran thinner timestamp togo togog waste water windows work

Stored function/procedure in Oracle returning dataset

Every now and then you need to return dataset from stored procedure. On Oracle, any executable method that returns value is actually called function. In following example we will create small package with function that returns oracle cursor. Why cursor? Oracle does not return dataset, rather, it returns internal cursor which can be then looped through to retrieve values. This is much faster if the client abandons execution after few hundred records when the dataset would, for example, return thousands or tens of thousands of records. By keeping result as open cursor, the memory utilization can be controlled by server and query is evaluated on demand when needed.

Without further ado, here is an example of package implementation:

CREATE OR REPLACE PACKAGE MYDB.PACKAGE_TEST
AS
    -- late binding untyped cursor
    TYPE CURS_OUT IS REF CURSOR;
    -- test function that returns reference cursor
    FUNCTION TEST RETURN CURS_OUT;
END PACKAGE_TEST;

CREATE OR REPLACE PACKAGE BODY MYDB.PACKAGE_TEST
AS
    -- test function that returns reference cursor
    FUNCTION TEST RETURN CURS_OUT
    AS
    -- use late binding ref cursor type
    MYCURSOR CURS_OUT;
    BEGIN
        -- open cursor
        OPEN MYCURSOR FOR
            SELECT * FROM MYDB.MYTABLE WHERE ID BETWEEN 1700 AND 1750;
        -- return open cursor
        RETURN MYCURSOR;
    END;
END MYDB;

More info:

permanent link to article http://freecash.hogger.net/stored_function_procedure_in_oracle_returning_dataset