2008 acetone africa bladesystem c3000 cleaning count database debug denver diamond disposal DRM flashback gaudeau golf grown lab lacquer long moisonite mpio oil oracle polystyrene procedure pvs query record recovery recrutement recycler recycling result select server software solvent table take tank thinner time timestamp togo vegetable vehicle waste water windows

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