Implement Paging Mechanish with Oracle as backend

Hi frenz!!

I have Oracle 9i as the backend. My front screen has paging mechanism similar to Google.
The total number of records will be displayed say 1000
No of records per page will be 50

so now there will 20 pages that User can movew through using "Prev First Last Next" buttons.

How do i handle this from Oracle side?

suresh1977Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

andrewstCommented:
Use a query like this:

select a,b,c from
(
  select a,b,c, ROWNUM as rn
  from
  ( select a,b,c
    from mytable
    order by a,b
  )
  where ROWNUM <= :last_num
)
where rn >= :first_num

Then pass in suitable bind values for :first_num and :last_num, e.g. 1 and 50, 51 and 100, ...
0
pavelhCommented:
1. you can use analytic function. I can find syntax in documentation.

2.
Other way is use rownum. But!!!!!!!!!!!!!!!!!!!!!
If you use :
select rownum,name, salary from emp where order by salary desc;
you can get this:
8      John       10000
4      Peter        5000
3       Mark      15200

you must use
select * from (
    select rownum as rn,tmp.*  (select name, salary from emp where rownum<6 order by salary desc ) tmp )
where rn > your_skip and rn < (your_skip + your_rows_on_page)

3.  you can use PL/SQL (cursor  , loop , skip rows, print rows and close cursor)


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
suresh1977Author Commented:
Hi frenz..

I don't want to go in for a rownum concept...

Pavelh can you elaborate on the PL/SQL paging mechanish and about the analytic function?

thanks for the speedy reply
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

andrewstCommented:
Here is the analytic version:

select a,b,c from
(
  select a,b,c, row_number() over (order by a,b) as rn
  from mytable
  order by a,b
)
where rn between :first_num and :last_num;

I find the ROWNUM version to be slightly faster, but not significantly so - and the analytic version is a little simpler to read.
0
andrewstCommented:
BTW, what is your objection to using ROWNUM?
0
suresh1977Author Commented:
Guys...I don't have any objection in using RowNum...but my PL wants me to NOT to implement RowNum...so I am exploring using Cursor...

if any one can throw light on it it will be of immense help
0
andrewstCommented:
Only your PL can shed light on his/her own prejudices.  You now have an analytic alternative to offer, as long as your PL's phobia doesn't extend to the similarly named ROW_NUMBER() function.

Using a cursor is OK as long as you maintain state between pages, which usually is NOT the case in this type of requirement, and as long as you only want to go forwards (Oracle cursors don't go backwards).  I sincerely hope you are not thinking of doing something terrible like this pseudo-code:

open cursor
for n in 1..last_record
loop
  fetch record
  if n >= first_num then
    output record
  end if
end loop
0
seazodiacCommented:
Suresh1977:
take a look at this link, the same question I have answered for another asker...
it's exactly what you are looking for....return a cursor but paging the results



http://oldlook-search.experts-exchange.com/Databases/Oracle/Q_20753840.html
0
suresh1977Author Commented:
PL wants to implement something like the pseudo code given by andrewst, but it shldn't use any Temporary table creation like as said by seazodiac
0
andrewstCommented:
You are saying that the PL wants to do something like this?:

open cursor
for n in 1..last_record
loop
  fetch record
  if n >= first_num then
    output record
  end if
end loop

So if you want rows 2001-2050 it will fetch 2050 rows from the server to the client, and ignore the first 2000?

That is a really BAD idea!!!  All the other approaches suggested would only fetch 50 rows, not 2050.

If your PL won't use a ROWNUM (which is a ridiculous attitude IMHO) or temporary tables, why not try the analytic functions?
0
pavelhCommented:
Hi
Andrewst already made this sample for you.
Rownum is fast only witth small tables. If you can, use analytic function.
Tom Kyte from AskTom recommed it to in his book (Oracle Expert  - Ono to one)  to.  :)

PavelH
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.