Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Extracting a piece of a cursor

Re: Extracting a piece of a cursor

From: Francesco M. <fmarchioniNIENTESPAM_at_libero.it>
Date: Wed, 05 Mar 2003 08:12:46 GMT
Message-ID: <2ki9a.178366$YG2.5411251@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 - 02:12:46 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US