Implement paging through pl/sql

Question

I'm trying to figure out a way to implement paging via stored procedure calls. For example, I have an 'Images' table that has say 100 rows. A website is going to make a request for the 'first' 12 then when the user 'goes to the next page' the site will make a request for the next 12.

I'll be getting 2 in params (p_Offset and p_RecordCount) and I'll need to return a refcursor. The p_Offset will tell me where to start getting data from and p_RecordCount will tell me how many (p_Offset = 13, p_RecordCount = 12 will tell me to return 12 rows starting from the 13th row.

We're using Oracle 10g and I started looking at RECORD types but I have a feeling I'm making this more difficult then it needs to be. Any help would be appreciated.

Answer 1

I think I've got a solution but I'm still curious if there are other/better ways of handling it. Here's what I came up with:

open refcursor for
select x.*
from (select col1, col2, row_number()
    over (order by col desc) rn
    from [table])x
where rn >= p_Offset and rownum <= p_RecordCount