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 <uda...@wso2.com> 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 <uda...@wso2.com> 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 <duli...@wso2.com> >> 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 duli...@wso2.com <duli...@wso2mobile.com>* >>> * ~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 >>> Dev@wso2.org >>> 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 > Dev@wso2.org > 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 Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev