I have searched the web/archives for a solution to this and have found a
few entries to my question but nothing definitive. In essence I have a
function that opens a cursor (refcursor). It would appear though that
one can only traverse the cursor forward-only and only one record at a
time; is there any way (or has the ability been added in 8.0) to open
the cursor, fetch into variable until NOT FOUND and then reposition the
pointer to the beginning of the cursor?
In essence what I am doing is a type of best match algorithm; a key
(firstkey) of length x and owner y and rating (firstrating) represents
the first element. A best fit is considered the key (newkey) of
shortest length belonging to a different owner such that newkey and
substring(firstkey,1,length(newkey) match and newrating is better than
firstrating.
The initial pre-match table contains all the owners, keys, and ratings
and this is loaded in as a cursor in order of key desc (key is an
integer so naturally sorts from longest to shortest here) and value.
Right now, I have to reload the cursor for each iteration through the
table, offsetting by the iteration:
DECLARE
curs CURSOR (key integer) FOR SELECT * FROM foo ORDER BY key desc,
rating offset key;
Ideally I could see saving tons of time if I could simply:
FETCH ABSOLUTE iteration FROM curs
for each iteration without having to use offsets, etc.
Any ideas on this or on a better way of trying this?
Sven
