Re: SDriverOracle OffesetStrategy

 these 2 links have some solutions:

http://www.club-oracle.com/forums/limit-clause-for-oracle-sql-t637/
http://www.delphifaq.com/faq/databases/oracle/f594.shtml

the basic syntax looks like this:

  select *
  from (
    select rownum rnum, a.*
    from (your_query) a
    where rownum <= :M )
 where rnum >= :N;

in order to get rows n through m from 'your query.'

-- Noel.

Franck Routier wrote:
>  
>
> Seems I spoke too quickly. Oracle does not seem to support the ansi
> (2008) "OFFSET x FETCH y ROWS" syntax, nor any proprietary syntax to do
> that...
>
> Le vendredi 01 octobre 2010 à 13:55 +0200, Franck Routier a écrit :
> >
> > Hi,
> >
> > I am bitten by an oom exception using offset/limit with SDriverOracle.
> > The driver uses SDriver.OffsetStrategy.JDBC. The was done assuming
> > (from
> > various reading but without any evidence) that the Oracle Driver was
> > smart enought to use a cursor on the server side to handle this.
> >
> > But I definitely get an oom caused by OracleResultCacheImpl eating all
> > memory.
> >
> > So I searched again and found this, on
> > http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/resltset.htm#CIHCHBJB
> >
> > "Oracle JDBC Implementation for Result Set Scrollability
> >
> > Because the underlying server does not support scrollable cursors,
> > (sic!) Oracle JDBC must implement scrollability in a separate layer.
> >
> > It is important to be aware that this is accomplished by using a
> > client-side memory cache to store rows of a scrollable result set.
> >
> > Important:
> > Because all rows of any scrollable result set are stored in the
> > client-side cache, a situation where the result set contains many
> > rows,
> > many columns, or very large columns might cause the client-side Java
> > Virtual Machine (JVM) to fail. Do not specify scrollability for a
> > large
> > result set."
> >
> > So it seems that Oracle is not any smarter than Postgresql is, and
> > that
> > resorting to a limit clause in the sql (SDriver.OffsetStrategy.QUERY)
> > would be a better solution for Oracle as well.
> >
> > So I propose to change the SDriverOracle to implement this strategy.
> > Does it sound ok ?
> >
> > Franck
> >
> >
> >
> >
> >
>
> -- 
> Franck Routier
> Logo Axège
> Axège
> 23,rue Saint-Simon
> 63000 Clermont-Ferrand
> Tél: +33 (0)4 63 05 95 40
> Fax: +33 (0)4 73 70 65 29
> Email: [email protected] <mailto:franck.routier%40axege.com>
>
> 

Disclaimer: http://www.peralex.com/disclaimer.html