Paging In Oracle

Hi All,

I have requirement of sending the records based on the input received from web.

I have a ref_cursor returning 30 rows..
Here I am supposed to do paging on the set of records present in the above ref cursor.

If web gives 1 , then I should send first 10 records in the ref cursor..
If gives 2 , then I should send next 10 records in the ref cursor..
similarly..I will get some sort of indicator from web to do pagination ..

Could any one tell me , how can we do paging in oracle

Thanks,
Vijay

vijay.patil2005
Profile Answers by vijay.patil2005 Questions by vijay.patil2005
Jun 11th, 2008
6
7513

Questions by vijay.patil2005

PL/SQL

Answer

Showing Answers 1 - 6 of 6 Answers

pvidhya
Profile Answers by pvidhya

Sep 3rd, 2008

First you have to filter the no of records you need totally (i.e., if you receive input as 2 and if the no of records you need to return are 20, then you need to filter 20 rows) using where rownum <= 20, then out of the 20 rows we need to filter only the 11th to 20th row, so an outer query can filter this using

SELECT *
FROM    (
                  [TABLE NAME]
                  WHERE rownum <= 20 ( this is got by input_no * no of recors )
               )
WHERE rownum > = 10 ( this is got by (( input_no - 1)* no of records ) )

Hope this helps

muraligrandhi
Profile Answers by muraligrandhi

Jun 9th, 2010

With the help of Ref cursor you can get particular no of records(means 1=10 records,2=20 records like that), for that you can use rownum in the filter condition.

If you add below query in Ref cursor you can get records; like if you pass 1 from the web you can get 10,for 2 you can get 20 like that.

select * from table_name where rownum<=&web_no*10;