Solved

Implement Paging Mechanish with Oracle as backend

Posted on 2003-11-11
13
1,016 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
  • 5
  • 3
  • 2
  • +1
13 Comments
 
LVL 15

Expert Comment

by:andrewst
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 15

Expert Comment

by:andrewst
Comment Utility
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
Comment Utility
BTW, what is your objection to using ROWNUM?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:suresh1977
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now