Hello Werner,
>> Does Castor support this kind of direct call?
>>
>> 3/
>>
>> The database is Oracle 9i. I am used to limit the number of rows returned
>> by a sql request (with the keyword rownum).
>>
>> Is there a way to limit the number of rows returned by the database with
>> Castor?
>Bad news first: Yes, there is. I recently (re)introduced suppot for LIMIT
>and OFFSET clauses in Castor's OQL engine. But unfortunately, there is no
>support for Oracle at the moment.
>
>Now the good news: If you can give help me a bit with Oracle specifics, I
am
>willing to add support for Oracle as well (as well as for DB2).
As long as you only want to fetch the first n rows from an Oracle DB, and
you don't
need to use an 'order by' clause you can use the 'rownum' in a statement
like:
select *
from emp
where rownum <= 5
This would be a solution for thje simple case of an OQL query with
LIMIT but without OFFSET and without ORDER BY.
If you are using an Oracle DB of version 8.1.6 or above, you can use
the 'rank()' function to implement full support for LIMIT and OFFSET.
For example to query all rows between number 3 and 8 you can use:
select *
from (
select empno, ename, job, sal,
rank() over (order by sal) rnk
from emp
)
where rnk between 3 and 8
So this is how OQL queries should be transformed:
"SELECT o FROM Person o ORDER BY personId LIMIT $1 OFFSET $2"
->
select *
from (
select ... ,
rank() over (order by ...) rnk
from ...
)
where rnk between :2 and :2 + :1
"SELECT o FROM Person o ORDER BY personId LIMIT $1"
->
select *
from (
select ... ,
rank() over (order by ...) rnk
from ...
)
where rnk < 10
Regards,
Martin
--
Martin Fuchs
[EMAIL PROTECTED]
Superg�nstige DSL-Tarife + WLAN-Router f�r 0,- EUR*
Jetzt zu GMX wechseln und sparen http://www.gmx.net/de/go/dsl
-----------------------------------------------------------
If you wish to unsubscribe from this mailing, send mail to
[EMAIL PROTECTED] with a subject of:
unsubscribe castor-dev
