using rownum with union

[Pranav Sharma]

In order implement pagination on the front end, I need specific number(1-10, 11-20 ..) of rows to be returned from the db.

I know we can use constraints like
select * from table Where ROWNUM < 10

However I'm using multiple (4) joins in my sql query to fetch data from oracle 10g db.

How do i achieve this.

[Brian Mozhdehi]

setMaxRows(int maxRows) on statement object

[Purushoth Thambu]

If you want to fetch for each page then you need to use ORDER BY and ROWNUM. Let's say I join 3 tables emp, dept, loc and I need n rows at a time.

The query would be

  SELECT * FROM 
   (
     SELECT a.*, b.*, c.* FROM emp a, dept b, loc c
     WHERE ... 
     ORDER BY <some column>
   ) WHERE ROWNUM BETWEEN ? AND ?

You need to bind the values for ROWNUM predicate.

- ROWNUM does not ensure that records are fetched in any particular record. If you fire a query like
SELECT * FROM emp WHERE rownum < 10
Oracle does not guarantee you will get same set of 10 row very time. The ORDER BY clause will help to overcome this unpredictable behavior.