Use the rownum suggestion that Scott suggested. It works and will do what
you want. You are making it more complex than it needs to be. Also look at
asktom.oracle.com and do a search.
Jim
--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Francesco M." <fmarchioniNIENTESPAM_at_libero.it> wrote in message
news:2ki9a.178366$YG2.5411251_at_twister1.libero.it...
> Hi and thanks for your answers,
> I'll explain better what I need to do. I have a web application
> that shows a large resultset in pages.
>
> 1 2 3 4 5 6
> | |
> | ---- records from 1001-2000 etc.
> ---records from 1-1000
>
> I agree I cannot rely on plain sql to fetch a "piece" of a cursor
> becuase the result could be unpredictable if new records are added
> so I think I could only use PL-SQL. At the moment I think the
> only solution is:
>
> -declaring cursor
> -declaring a dummy counter = 0
> -open cursor
> -increase counter 'til I reach the "start" (in page 2 1001)
> -once reached the "start" fetch the cursor
> -fetch 'til the "end" (in page 2 2000)
> -close cursor
>
> I ask your help to write this in the most optimized way.....
> (I know pl-sql basic but I don't have idea what is the best
> approach to it, for example using a implicit cursor or an
> explicit one.....)
> thanks in advance
> Francesco
>
>
>
>
> "Andrew Allen" <andrew.allen_at_sppaammkiller.handleman.com> ha scritto nel
> messaggio news:3E64DECA.4050700_at_sppaammkiller.handleman.com...
> > Scott Mattes wrote:
> > > Maybe something like
> > >
> > > select *
> > > from ( select rownum row, table.*
> > > from table ) a.
> > > where a.row between 1000 and 1100;
> > >
> > > "Francesco M." <fmarchioniNIENTESPAM_at_libero.it> wrote in message
> > > news:UK49a.176847$ZE.5303458_at_twister2.libero.it...
> > >
> > >>Hi all Oracle Users,
> > >>I need to write a Stored Procedure that extract
> > >>a "piece" of a large resultset. Let's say I want to retrieve
> > >>from record n. 1000 to record n. 1100 of a Table. What's the
> > >>best way (for performance and less impact on the db)
> > >>to do it in PL/SQL ?
> >
> > Of course, how could ever know what your result set would be. You
> > cannot count on the same set of rows being returned in the same sequence
> > every time. If my query scans the table and I later insert a row into a
> > block on the free list then that row will now become part of my result
> > set and change what is returned from the query. Even if you sort the
> > output before adding the rownum pseudo column then you still can get a
> > different result set. Perhaps you need to rethink what you are trying
> > to accomplish.
> > --
> > AjA
> >
>
>
Received on Wed Mar 05 2003 - 03:25:49 CST
