Re: Stored procedure that return multiple records

From: Slarti <1012-873_at_gmx.de>
Date: 6 Feb 2002 22:52:57 -0800
Message-ID: <ccb8e3b6.0202062252.2ceb03a9@posting.google.com>

Hi,

just do the following; declare a REF CURSOR type to your needs (any collection is allowed) like this e. g. in package specification:

TYPE WHATEVERCurTyp IS REF CURSOR
RETURN WHATEVER%ROWTYPE; ...and then declare your stored procedure...

FUNCTION selectAll RETURN PKG_WHATEVER.WHATEVERCurTyp;

Just call it via JDBC and it works.

This allows you to allocate a memory adress for REF CURSOR type without duplicating the memory used due to assigning the same result set to multiple variables.

The WHATEVER function will compute a result set and set just a pointer to this. This pointer will be given to the calling procedure NOT the result set itself (as a data structure). And probably you know: A cursor (may) deliver multiple rows.

Hope it helps.

Regards Received on Thu Feb 07 2002 - 00:52:57 CST