Re: PL/SQL Logic Help Needed...

Tim Smith wrote:

> Hi,
>
> I have a table as follows
> pk_id status
> 1 P
> 2 P
> 3... U
> 30 U
>
> I want to return a reference cursor for the next 5 (by order of pk_id)
> records and (within this PL/SQL) set the status to Q so that if the
> procedure is executed again it never returns the same record.
>
> I believe I need to
> a) select for update a list of U's e.g. 3,4,5,6, and 7
> b) set status to Q
> c) return a ref cursor for the original 5 records
>
> Is this possible without using a (physical) temporary table in version
> 8.0.5 (yes, upgrade pending..). A data structure would be fine if
> possible.

First off get rid of 8.0.5. it is not supported and near jurassic in age.

Now to your question ... what is it that defines the order of the records? By definition data in heap tables has no order. Your Oracle version is so old I even remember what it did and didn't do ... but if I were to solve what I think is your problem in a supported version such as 9i or 10g I would save min_val and max_val from each returned set in a package variable and then increment from there using an array to hold the return row set.

A variation of what you can find at http://www.psoug.org Click on Morgan's Library
Click on Associative Arrays

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)