Hi Dulitha,
Found this Pagination with ROWNUM example (source [1]), sharing it if you
could find it helpful:
select *
from ( select /*+ FIRST_ROWS(n) */
a.*, ROWNUM rnum
from ( your_query_goes_here,
with order by ) a
where ROWNUM <=
:MAX_ROW_TO_FETCH )
where rnum >= :MIN_ROW_TO_FETCH;
:FIRST_ROWS(N) tells the optimizer, "Hey, I'm interested in getting the
first rows, and I'll get N of them as fast as possible."
:MAX_ROW_TO_FETCH is set to the last row of the result set to fetch—if you
wanted rows 50 to 60 of the result set, you would set this to 60.
:MIN_ROW_TO_FETCH is set to the first row of the result set to fetch, so to
get rows 50 to 60, you would set this to 50.
[1] -
http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
Thank you,
Vishanth
On Mon, Mar 23, 2015 at 2:10 PM, Udara Rathnayake <[email protected]> wrote:
> Hi All,
>
> We faced an issue while following the above method. Even if we store the
> DB specific select query within a configurable file, number of properties
> and property order may vary from one to another. If we are using prepared
> statements we need to reflect above differences within our logic. We can
> solve this by using stored procedures but calling a stored procedure is
> still difference from one to another.
>
> Following is the proposed solution:
>
> We are going to delegate these DB specific logic to a driver. We can
> provide a default driver for H2/MySQL/Postgres and another driver for
> Oracle initially. If someone is going to use a different DB type they can
> develop their own.
>
>
>
>
>
> How to use :
>
> During product creation we copy the default driver (Since we are using H2
> by default) to the <CARBON_HOME>/repository/components/lib directory. If
> we are going with Oracle we can replace the default one with Oracle.
> (Remove default one from lib and dropins directories)
> Appreciate any feedback on this.
>
> Regards,
> UdaraR
>
> On Wed, Mar 4, 2015 at 12:57 PM, Udara Rathnayake <[email protected]> wrote:
>
>> Hi All,
>>
>> We had the same discussion yesterday and had a small chat with Prabath
>> too. Since there is no viable solution ATM we decided to store these select
>> statements in a script and pick according to the data-source configuration.
>>
>> Eg:- /dbscripts/social/oracle/<select_script>,
>> /dbscripts/social/sql/<select_script>
>>
>> Since we can use the same select statement with limit,offset for
>> h2,MySQL,PostgreSQL may be we can have a common one.
>>
>> Thoughts?
>>
>> Regards,
>> UdaraR
>>
>>
>> On Mon, Mar 2, 2015 at 8:31 AM, Dulitha Wijewantha <[email protected]>
>> wrote:
>>
>>> Hi guys,
>>> I am working on creating a device listing API that supports pagination.
>>> In the SQL world, LIMIT and OFFSET seems to be the standard way of doing
>>> pagination in MySQL and PostgreSQL. Below is an example,
>>>
>>> SELECT Device.id, Device.name FROM Device
>>> LIMIT 10 OFFSET 10
>>>
>>>
>>> But Oracle seems to be using rownum. What's the recommended approach in
>>> doing this?
>>>
>>> Cheers~
>>>
>>> --
>>> Dulitha Wijewantha (Chan)
>>> Software Engineer - Mobile Development
>>> WSO2 Inc
>>> Lean.Enterprise.Middleware
>>> * ~Email [email protected] <[email protected]>*
>>> * ~Mobile +94712112165 <%2B94712112165>*
>>> * ~Website dulitha.me <http://dulitha.me>*
>>> * ~Twitter @dulitharw <https://twitter.com/dulitharw>*
>>> *~Github @dulichan <https://github.com/dulichan>*
>>> *~SO @chan <http://stackoverflow.com/users/813471/chan>*
>>>
>>> _______________________________________________
>>> Dev mailing list
>>> [email protected]
>>> http://wso2.org/cgi-bin/mailman/listinfo/dev
>>>
>>>
>>
>>
>> --
>> *Udara Rathnayake*
>> Software Engineer
>> WSO2 Inc. : http://wso2.com
>>
>> Mobile : 1 4087864651 | 94 772207239
>> Twitter : http://twitter.com/udarakr
>> Blog : http://udarakr.blogspot.com
>>
>>
>
>
> --
> *Udara Rathnayake*
> Software Engineer
> WSO2 Inc. : http://wso2.com
>
> Mobile : 1 4087864651 | 94 772207239
> Twitter : http://twitter.com/udarakr
> Blog : http://udarakr.blogspot.com
>
>
> _______________________________________________
> Dev mailing list
> [email protected]
> http://wso2.org/cgi-bin/mailman/listinfo/dev
>
>
--
*Vishanth Balasubramaniam*
Software Engineer
WSO2 Inc.; http://wso2.com
lean.enterprise.middleware
mobile: *+94771737718*
about me: *http://about.me/vishanth <http://about.me/vishanth>*
_______________________________________________
Dev mailing list
[email protected]
http://wso2.org/cgi-bin/mailman/listinfo/dev
