Re: fetching rows from a certain point after closing cursor

In article <3231C574.24AC_at_ibmail.smst290.att.com>,

   Ashley Coutinho <ashley_at_ibmail.smst290.att.com> wrote:
>Problem:
>User conducts a query.
>Declare cursor
>Query matches 100 records but I fetch 20 only.
>Close cursor
>Program exits
>
>User asks for the next 20.
>?????? How do I start from the previous point
>without querying the old 20 records ????????

this is a common problem in designing GUI with oracle. usual solution, as implemented by oracle in their GUI tools:

1. open cursor
2. fetch first/next N rows
3. <user asks for next N>
4. write current N to file or memory
5. goto 2
6. <user asks for previous N rows>
7. write current N to file or memory
8. read prior N from file or memory
9. wait for 3. or 6. 10.<user signifies he's done>
10. post changes and commit
11. optionally close cursor

it seems silly to keep your own result set but: a. oracle doesn't permit non-sequential access to its result set; b. your changes are usually made just to your GUI widgets; generally

   we post and commit changes in bulk rather than as the user mouses around.    so you must store the current state of the widgets until commit.

--
Dave Mausner, Consulting Manager, Braun Technology Group, Chicago.