[SOLVED] Oracle Pagination

[Adam]

Hey guys. Not a massive expert when it comes to Oracle and I'm stuck with some pagination type problems. I've got a procedure setup that I can pass in "pStart" and "pLength" to appear to act like MySQL's LIMIT clause. This is the actual select statement I'm using (I've left out the surrounding procedure related stuff):

 

select p.insight_prod_cd as sku, pa.attr_val as cnet_desc
  from prods p, prod_attrs pa
  where p.prod_id = pa.prod_id
  and pa.cat_attrs_id = A.CUSTOMCAT
  and pa.num_attr_val = 3
  and rownum >= pStart
  and rownum <= (pStart + pLength);

 

Without the last 2 lines this works perfect, however as I mentioned I want to paginate the results. If I provide pStart as 0 and pLength as 10 (or any number > 1 really), it works. However if pStart is > 1 it returns nothing. I've looked around on the net and noticed this isn't how to achieve pagination and that 'rownum' doesn't work like that, but I'm stuck trying to apply some of the other methods I've read about (like this for example) to my query.

 

I've noticed online tutorials on PL/SQL tend to assume you know a fair bit about the language already, and I'm still a little hazy with quite a few areas. Could anybody offer any advise or help for me to paginate this query?

 

Thanks in advance.

 

Adam

[Adam]

Managed to get it working in the end

 

Solution for anyone it would benefit:

 

  select * from (
    select
      p.insight_prod_cd as sku,
      ped.product_data.GetProductName(p.insight_prod_cd) as product_name,
      ped.product_data.getAttribValue(p.prod_id, A.CNETDESCRIPTION) as cnet_desc,
      row_number() over (order by p.insight_prod_cd) rn
    from prods p, prod_attrs pa
    where p.prod_id = pa.prod_id
    and pa.cat_attrs_id = A.CUSTOMCAT
    and pa.num_attr_val = 3
  )
  where rn between pStart and (pStart+pLength)
  order by rn;

 

I've made some changes to the main select query as well in case anyone noticed it's different.

 

Thanks anyway!