Re: Interesting question: Result set break down by multiple pages on Web site.

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Wed, 05 Jan 2000 15:51:19 -0500
Message-ID: <bta77sgmkk705u19i6cvh1rhp9d1prdv8h@4ax.com>

On Wed, 5 Jan 2000 14:35:04 -0600, "Shaojie Hu" <maichen_at_rols.com> wrote:

>Hi, all:
>
>I am trying to figure it out how to break down Oracle results by pages so
>that I can
>put results on many dynamic web pages. To be more acuate, I don't want
>Oracle to
>return all rows, but only rows in certain range one at a time. For example,
>if a search
>returns 1000 rows and 50 rows make a page, then I will have 20 web pages. If
>a user
>click on page number 5, he will get rows between 201 and 250 and these 50
>rows are
>acutually fetched from database and the rest 950 rows are not fetched.

in 8i you can do this

procedure paginate( p_page number ) is
begin
  htp.tableOpen;
  for c1 in ( select *

                from ( select x.*, rownum r
                         from ( select * 
                                  from foo 
                                 order by col_1 ) x )
               where r between (p_page-1)*50+1 and (p_page*50) )

  loop
    htp.tableRowOpen;

      htp.tableData( c1.col_1 );
      htp.tableData( c1.col_2 );
      htp.tableData( c1.col_3 );

    htp.tableRowClose;
  end loop;
  htp.tableClose;
end;
/

hope this helps.

chris.

>
>Any ideas?
>
>Shu
>

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jan 05 2000 - 14:51:19 CST