Thanks for the question, Munzer.

Asked: March 30, 2002 - 1:28 pm UTC

Last updated: January 19, 2010 - 5:10 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Tom:

1. When you create a stored procedure that creates a list of recrods in html format and let us say the query may result in hundreds or thousands of records.

Can you run into a problem if you do not limit the number of records listed on one page.

2. Do you usually implement it so that procedure will list 50 records and then you will create two buttons "Next 50" and "Previous 50".

3. If you do the buttons would those be stored procedures. Did you already put an example of this on your web site.

Thank you,

and Tom said...

1) sure, the page will be really big and the end users will get really mad.

2) I use the technique demonstrated here:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:127412348064 <code>

that is how asktom works for example with its search.

3) everything to me is a stored procedure -- yes, the ACTION of the form surrounding the buttons would be a stored procedure that would run the query as shown in #2 with different max/min values.

Rating

(14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Limiting Records

munz, March 30, 2002 - 7:18 pm UTC

Tom:

1. OK let us say you create a hyperlink ir a button and when clicked or presses it runs the SQL query you define.

If min_rows=1 and Max_rows=25 is hardcoded in the procedure it will always select the same rows. Where do you change those parameters in the link and how do you know that this is the second set user asks for or fourth set and so on.

You are basically calling the same page with the link.

March 31, 2002 - 8:52 am UTC

why would you hardcode it? pass in the row to start with, let it default to 1. This routine paginates over EMP:

create or replace procedure paginate( p_start in number default 1 )
as
l_next boolean default false;
l_pagesize number default 5;
begin
htp.bold( 'p_start = ' || P_start );
htp.p( '<table>' );
for x in ( select *
from ( select a.*, rownum r
from emp a
where rownum <= p_start+l_pagesize ) a
where r >= p_start )
loop
l_next := ( x.r = p_start+l_pagesize );
htp.p( '<tr><td>' || x.r || '</td><td>' ||
x.ename || '</td><td>' || x.empno || '</td></tr>' );
end loop;
htp.p( '</table>' );

if ( p_start > 1 )
then
htp.formOpen( 'paginate' );
htp.formHidden( 'p_start', p_start-l_pagesize );
htp.formSubmit( cvalue => 'prev ' || (p_start-l_pagesize) );
htp.formClose;
end if;
if ( l_next )
then
htp.formOpen( 'paginate' );
htp.formHidden( 'p_start', (p_start+l_pagesize) );
htp.formSubmit( cvalue => 'next ' || (p_start+l_pagesize) );
htp.formClose;
end if;
end;
/

Parag

Parag, March 31, 2002 - 10:26 am UTC

Very Usefull Material.

Limiting Rows

Munz, March 31, 2002 - 1:49 pm UTC

Tom:

This is great answer. You are one of the very few great people on earth.

paginate code

mo, January 21, 2003 - 5:20 pm UTC

Tom:

There is a problem with the above paging code. Here are the results:

p_start = 1
1 SMITH 7369
2 ALLEN 7499
3 WARD 7521
4 JONES 7566
5 MARTIN 7654
6 BLAKE 7698
NEXT 6

p_start = 6
6 BLAKE 7698
7 CLARK 7782
8 SCOTT 7788
9 KING 7839
10 TURNER 7844
11 ADAMS 7876
PREV 1
NEXT 11

p_start = 11
11 ADAMS 7876
12 JAMES 7900
13 FORD 7902
14 MILLER 7934
PREV 6

1. You can see that some names are repeated each page. Each page should display only 5 records. The buttons should say "PREV 5" and "NEXT 5". I took the equal sign from the query but it seems I can not get the "NEXT" button.

What is this mean:
l_next := ( x.r = p_start+l_pagesize );

l_next is boolean. are you comaring x.r to a total. I am using a ref cursor and not an implicit cursor like this example.

Any fixes?

Thanks,

January 21, 2003 - 7:30 pm UTC

no problem -- i did that on purpose actually. If you don't like the way it works -- just nudge the numbers.

Me -- I like to have the last row from the prev page on the top of the next page...

Any fixes? you got the code -- and it is just a mathematical alogorithm -- adjust as you see fit! (really, use some *creativity* -- this is all about ideas here, take them, make them your own, apply them)

mo, January 21, 2003 - 8:03 pm UTC

Tom:

I did but I can't understand this:

l_next := ( x.r = p_start+l_pagesize );

what does this mean in pl/sql?

How do you decide if there should be a "NEXT" button or not? DO I have to count all records in the result set before I present the 5 record-page query?

THank you

January 21, 2003 - 8:54 pm UTC

l_next is a boolean (takes true, false or NULL)

x.r = p_start+l_pagesize

is a boolean expression that returns true, false or NULL.

I am assiging:

o true to l_next IF x.r is equal to p_start+l_pagesize
o FALSE to l_next if x.r is NOT equal to ....
o NULL if x.r, p_start, or l_pagesize is null (and hence the answer is not known)

I am going through the result set. x.r is in the domain of:

p_start TO p_start+l_pagesize

(the query does that). If we get to where x.r (the current rownumber) is p_start+l_pagesize - that means "hey, there is more data to come, we aint' going to see it with this query cause we said only give us up to p_start+l_pagesize rows -- but they are there"... so, we give them a next button to press in order to starting showing the data at a different p_start offset.

Now -- do you see me counting anything anywhere there? did I have to count them all first? No -- you do not.

paging

mo, January 21, 2003 - 11:23 pm UTC

Tom:

Let us say emp table has only 6 records. BAsed on the code it will display one page with 6 records and a next button.

p_start = 1;
l_pagesize := 5;

for x in ( select *
from ( select a.*, rownum r
from emp a
where rownum <= 6 ) a
where r >= 1 )
loop
l_next := ( x.r = p_start+l_pagesize );

The innermost query will result in 6 rows selected. When we get inside the loop, the sixth pass will have x.r =6 = p_start + l_pagesize which will result in l_next being true.

So it will show a "NEXT" button even though there are only six records in the table and they are displayed on the page.

The only way this will work if we retrieve 6 records in the innermost query but only display 5 within the loop and check if there was a sixth one and then set l_next to true based on that one. Am I missing somthing here?

January 22, 2003 - 7:53 am UTC

I know, it does that by design. I figured "hey, that is not a big deal, end user will actually live with it"

YOU (as i've said) can do ANYTHING AT ALL you wish to do. go for it! Please, take the ideas from here, apply them as you see fit. Me, I did it my way -- you, do it your way.

This is just *math* here, make the numbers add up the way you want.

Limiting Web Page Rows in Oracle 7.3.4

Michael Maramzin, January 22, 2003 - 2:18 am UTC

This statement will be work in Oracle 7.3.4 if don't include the ORDER BY clause.

select * from (
select a.*, rownum rnum
from ( YOUR_QUERY_GOES_HERE -- including the order by ) a
where rownum <= MAX_ROWS
)
where rnum >= MIN_ROWS
/

Can I limiting web page rows in Oracle 7.3.4?

Thank you

January 22, 2003 - 8:13 am UTC

nope. you cannot use this technique prior to 8i

web paging

mo, March 08, 2003 - 12:23 pm UTC

Tom:

The pagination code you listed works very nicely in applications. However, I am having a proble with the <FORM> tags. Here is WHat I have:

User enters a search criteria in a query page and then i go to "results page" with 10 records displayed. If there are more than 10 I display "next 10 records". The link has its own form that submits it to the results page again with a different start count.

The problem is that when I display the 10 records I display them in their own form which submits it to an "update procedure". IF the user decide to update a value and hit "SAVE" it should submit the first form values to the update procedure. Problem is that "SAVE" is not working because of the above two forms "NEXT" and "RREV". Is there a way around this other than moving the save button on top of the "PREV" and "NEXT" links since it does not look very good.

<form name=save method="post" action="update_list">

<form name=next method="post" action="results_list">
<A HREF="javascript:doSubmit_next();">Next 10 Records</A>
</form>
<form name= prev method="post" action="results_list">
<A HREF="javascript:doSubmit_prev();">Previous 10 Records</A>
</form>

<INPUT type="submit" name="button" value=" Save ">
</form>

March 08, 2003 - 1:46 pm UTC

time to get an html and javascript book.

if you have an Oracle database question....

Nested forms are not valid html

Tom, July 02, 2004 - 5:29 am UTC

The reason you are having problems with having paging and update forms in your report is because nested forms is not valid html. The best way to do this would be along the lines of...

<FORM NAME="pagingForm" ACTION="pagingHandler">
<INPUT TYPE="HIDDEN" NAME="pStart" VALUE=""></INPUT>
</FORM>

[ report goes here - ie close the paging form before the report]

and then your next and previous buttons can be

<BUTTON TYPE="BUTTON" onClick="document.pagingForm.pStart.value=1; document.pagingForm.submit();">previous</BUTTON>
<BUTTON TYPE="BUTTON" onClick="document.pagingForm.pStart.value=10; document.pagingForm.submit();">Next</BUTTON>

This will allow you to have forms inside the report without having problems with nested forms.

Thanks Tom

Dave, December 11, 2004 - 4:49 am UTC

I just did a little jig on my bed after I found this article--I'd been scouring the internet for the last 6 hours just to find I was declaring my variable for the first row of the page in the wrong spot.

Pagination for Expensive Queries

A Reader, January 16, 2010 - 11:53 pm UTC

Hi Tom

we have a web-site that has a text search and presents data in pages, the query we have is the normal pagination query such as :

SELECT seq, book_cd, book_name, lots of other fields
FROM (SELECT ROW_NUMBER () OVER (ORDER BY order_name) seq,
book_cd, book_name, lots of other fields

FROM test_order t1
WHERE contains (t1.book_text, 'JACK AND JILL') > 0)
WHERE seq BETWEEN 1 AND 25
ORDER BY seq

Since the table test_order is pretty large (20 million rows) it takes a while to run.

Issue our users have is .. they are OK if he query takes a while initially ... but they find it frustrating that when you page it again takes approx the same amount of time as the initial display, for every paging operation.

They want it setup in such a way that it can take as much time as reqd upfront but then pages quite fast ...

any techniques you can suggest to meet that requirement ?

Thanks in advance

January 18, 2010 - 5:33 pm UTC

you would typically use a "do it yourself temporary table"

create table result_cache
( session_id number,
seq number,
rid rowid,
primary key (session_id,seq)
)
organization index;

and you would insert into that once and then use that to paginate again (do a join to the original table by rowid)

select *
from test_order, (select seq, rid from result_cache where session_id = :x and seq between :a and :b order by session_id, seq ) s
where test_order.rowid = s.rid
order by s.seq;

You would delete from result_cache where session_id= :x before running a search and populate that table.

You need to setup something to clear out that result cache from old searches over time if necessary. If you have a table of active sessionids - that'd be great to use, else you'll have to come up with something (maybe put a date column in the result cache and purge anything older than X days or something)

paging

sam, January 18, 2010 - 7:29 pm UTC

A Reader

A reader, January 19, 2010 - 2:09 pm UTC

Thanks Tom - I'll try this out

One followup q -

if the search is too permissive and yeields too many rows when inserting rows in the result_cache table ... is there a way to tell oracle that if an insert is inserting more than 10,000 rows then raise an exception ?

I could also do a count(*) upfront to do that but that would mean always doing two queries - a count(*) followed by the insert and i am trying to see if i can avoid the count(*) ... (for getting better performance) ..

Thanks in advance

January 19, 2010 - 5:10 pm UTC

here is a "cheesy" way to do it:

ops$tkyte%ORA10GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set autotrace traceonly statistics;
ops$tkyte%ORA10GR2> select owner, object_type, object_name, case when count(*) over () > 10000 then 1/0 else null end flag
  2    from t
  3   where owner = 'SCOTT'
  4  /

29 rows selected.


Statistics
----------------------------------------------------------
         88  recursive calls
          0  db block gets
        765  consistent gets
        687  physical reads
          0  redo size
       1242  bytes sent via SQL*Net to client
        411  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         29  rows processed

ops$tkyte%ORA10GR2> select owner, object_type, object_name, case when count(*) over () > 10000 then 1/0 else null end flag
  2    from t
  3   where owner = 'SYS'
  4  /
select owner, object_type, object_name, case when count(*) over () > 10000 then 1/0 else null end flag
                                                                                 *
ERROR at line 1:
ORA-01476: divisor is equal to zero


ops$tkyte%ORA10GR2> set autotrace off

maybe better would be to use a where clause on the 'seq' during the index and have code like:

insert... where seq <= 10000 ...

if (sql%rowcount = 10000)
then
   warn user that only 10k hits are saved
   OR
   raise application error
end if

google uses the "warn user" approach, I like that myself

re: Pagination for Expensive Queries

Stew Ashton, January 20, 2010 - 11:22 am UTC

I just happened to notice that starting with version 11G you can specify an ORDER BY in the creation of the CONTEXT index :
http://download.oracle.com/docs/cd/E11882_01/text.112/e10945/ind.htm#CCAPP9103
Assuming you use the same ORDER BY every time, with that version you could speed up the first query. It could conceivably go fast enough that you could do a query every time instead of using the result_cache table.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Limiting Records

Parag

Limiting Rows

paginate code

paging

Limiting Web Page Rows in Oracle 7.3.4

web paging

Nested forms are not valid html

Thanks Tom

Pagination for Expensive Queries

paging

A Reader

re: Pagination for Expensive Queries

More to Explore

PL/SQL demos

PL/SQL docs