Solved

Implement Paging Mechanish with Oracle as backend

Posted on 2003-11-11
13
1,031 Views
Last Modified: 2012-06-27
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?

0
Comment
Question by:suresh1977
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
13 Comments
 
LVL 15

Expert Comment

by:andrewst
ID: 9722027
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
 
LVL 1

Accepted Solution

by:
pavelh earned 84 total points
ID: 9722054
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
 

Author Comment

by:suresh1977
ID: 9722133
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:andrewst
ID: 9722144
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
 
LVL 15

Expert Comment

by:andrewst
ID: 9722177
BTW, what is your objection to using ROWNUM?
0
 

Author Comment

by:suresh1977
ID: 9722227
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
 
LVL 15

Expert Comment

by:andrewst
ID: 9722382
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
 
LVL 23

Assisted Solution

by:seazodiac
seazodiac earned 83 total points
ID: 9722826
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
 

Author Comment

by:suresh1977
ID: 9729669
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
 
LVL 15

Assisted Solution

by:andrewst
andrewst earned 83 total points
ID: 9730405
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
 
LVL 1

Expert Comment

by:pavelh
ID: 9730760
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

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question