Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Paging through recordsets

Re: Paging through recordsets

From: M. Armaghan Saqib <armaghan_at_yahoo.com>
Date: Tue, 22 Feb 2000 03:04:21 GMT
Message-ID: <88subl$46a$1@nnrp1.deja.com> 





This issue was recently discussed in detail on websys_l mailing list. I
am including all emails on this issue for you and othe people reference.



Release 2.1 of my SQLPlusPlus will include this code in the BLDHTM
command output.



regards,


M. Armaghan Saqib

+---------------------------------------------------------------
| 1. SQL PlusPlus => Add power to SQL Plus command line
| 2. SQL Link for XL => Integrate Oracle with XL


| 3. Oracle CBT with sample GL Accounting System
| Download free: http://www.geocities.com/armaghan/


+---------------------------------------------------------------
| SQLPlusPlus now on http://www.ioug.org/

|   "PL/SQL package that extends SQL*Plus to another dimension.


|   Contains a PL/SQL code generator and set of extremely useful
|   utilites with extensive documentation." IOUG Web Site

+---------------------------------------------------------------





Date:    Fri, 4 Feb 2000 10:58:57 -0000
From:    Bharat Bhushan <bharat.bhushan_at_BT.COM>
Subject: Re: Paging and makeup



Hi Folks,



As we are talking about navigations in a web-page i.e., "next"
and "back" to


view


the data-set. Now I need to hear your opnion about the following two
approaches: -



    
  
  1.   Download the whole data once and using client-site scripting
languages,
display
     part(s) of the data-set.

  
  2.   Each time execute the CGI script/ PLSQL procedure to fetch the
result
and then
     display the result-set.







My opnion: -



In approach (a) we would save the execution and would put less load at
the


server


moreoever the client response would be in fraction of seconds but if we
are


talking


about mission critical applications then we may loose any server side
updations.



In approach (b) we would get the current data set but at the same time
we


may be


bombarding the server with lots of small-small requests and taking more
time


to


display the result-set.




I would appreciate your responses.



Thanks and Regards,






PS: Lets assume that we are talking about a web-based shop.




> -----Original Message-----


> From: Jeremy Ovenden [SMTP:jovenden_at_HAZELWEB.CO.UK]


> Sent: Wednesday, February 02, 2000 7:19 AM


> To:   WEBCYS_L_at_LISTSERV.VT.EDU


> Subject:      Re: Paging and makeup


>


> Hi in this example, if I understand it correctly, if you had just


viewed


> rows 1-10, to see 11-20 it would re-read rows 1-10 again plus an


> additional


> 10. Is this right?


>


> Not that I know better :) but I was wondering whether this is an


efficient


> way of doing it, or is  there a 'purists' version too?


>


> __________________________________________________________________


> Jeremy Ovenden                   jovenden_at_hazelweb.co.uk


>


> -----Original Message-----


> From: Elizabeth Bell <elizabeth.bell_at_GTRI.GATECH.EDU>


> To: WEBCYS_L_at_LISTSERV.VT.EDU <WEBCYS_L_at_LISTSERV.VT.EDU>


> Date: 01 February 2000 16:29


> Subject: Re: Paging and makeup


>


>


>


> I've attached a sample procedure which pages through the scott.emp


table.


> The cursor fetches all the rows from the result set, but only


displays the


> appropriate report "page".


>


> -- Elizabeth Bell


>


> At 10:24 AM 2/1/00 +0100, you wrote:


> >Hi


> >


> >I am developing an application with OAS 4.0.8 and Oracle 8.0.6. I


want to


> create html pages with Next/Previous buttons so users


> >can navigate through big tables. I have used owa_util.cellsprint but


i


> havenīt total control over presentation (I cannot, for


> >example, change alternatively the color of the rows or similar


things).


> I


> also have used dynamic SQL but, in this case, how


> >can i control the paging of the table? How can i close the cursor if


the


> user, for example, close the browser?


> >


> >I am looking for a solution for paging and makeup.


> >


> >Thanks in advance.


> >


> >Ignacio Fernandez


> >i_fernandez_at_thepentagon.com


> >








Date:    Fri, 4 Feb 2000 13:12:53 +0100
From:    Ignacio =?iso-8859-1?Q?Fern=E1ndez?=
<i_fernandez_at_THEPENTAGON.COM>


Subject: Re: Paging and makeup



Hi Jeremy



 I've improved the example of Elizabeth. This new version only read
the rows neccessary to reach the last row to be shown. It needs a
count(*)


to obtain total number of rows of the table. But if you don't want to
show the string 'Records X-Y of Z' you can avoid count(*).



 I think that solution is simple and work, but what about big big
tables?


When we retrieve last rows of a enormous table we need a lot of
processing


to show, for example, 10 rows.



 Anybody know a better solution?



 Example of paging and makeup v2.0

--########################
CREATE OR REPLACE PROCEDURE FACTURA.emp_report
                           ( qlastRecCount   VARCHAR2 DEFAULT '0',
                             quserRequest    VARCHAR2 DEFAULT 'NEXT' )
is
    maxRec        NUMBER := 0;
    recCount      NUMBER := 0;
    rowsDisplayed NUMBER := 2;
    rowsLeft      NUMBER := 0;
    rowsReturned  NUMBER := 0;
    startRec      NUMBER := 0;

    -- ### NEW
    l_total       NUMBER :=0;
    l_alterno     NUMBER :=0;






BEGIN







IF ( quserRequest = 'PREV' ) THEN


-- The user is requesting the PREVIOUS page, not the NEXT page.  Adjust
the startRec and


-- maxRec values.


  maxRec   := ( maxRec - ( 2*( rowsDisplayed ) ) );
  startRec := ( startRec- ( 2*( rowsDisplayed ) ) );
END IF;






htp.htmlOpen;
htp.bodyOpen;
htp.print( '<CENTER>' );





htp.tableOpen( 'BORDER=1' );


  htp.tableRowOpen;

    htp.tableData( htf.header( 2,'Emp Number' ));
    htp.tableData( htf.header( 2,'Emp Name' ));
    htp.tableData( htf.header( 2,'Job' ));
    htp.tableData( htf.header( 2,'Manager' ));
    htp.tableData( htf.header( 2,'Hired' ));
    htp.tableData( htf.header( 2,'Salary' ));
    htp.tableData( htf.header( 2,'Comission' ));
    htp.tableData( htf.header( 2,'Department' ));


  htp.tableRowClose;



  FOR emp IN cur_scott_emp LOOP


    recCount := recCount + 1;




        if (l_alterno mod 2 = 0) then
          htp.tableRowOpen (cattributes => 'BGCOLOR="#CCCCCC"');
        else
          htp.tableRowOpen;
        end if;
        l_alterno := l_alterno + 1;
        -- ###

          htp.tableData( emp.empno );
          htp.tableData( emp.ename );
          htp.tableData( emp.job );
          htp.tableData( emp.mgr );
          htp.tableData( emp.hiredate );
          htp.tableData( emp.sal );
          htp.tableData( NVL(to_char(emp.comm), '&nbsp;' ));
          htp.tableData( emp.deptno );
       htp.tableRowClose;
     END IF;




   END LOOP;






htp.tableClose;






htp.header(3,' Records '|| startRec ||' - '|| maxRec ||' of '||
rowsReturned ||'


shown.');





htp.print( '</CENTER>' );
htp.bodyClose;
htp.htmlClose;





EXCEPTION



  WHEN no_data_found THEN

      htp.tableClose;
      htp.header(1,'No matching records found');
      htp.print( '</CENTER>' );
      htp.bodyClose;
      htp.htmlClose;





END emp_report;

--########################





 Thanks in advance.



 Ignacio Fernandez


 i_fernandez_at_thepentagon.com



Date:    Fri, 4 Feb 2000 14:44:14 +0000
From:    Damianos Dorizas <ddorizas_at_CISCO.COM>
Subject: Re: Paging and makeup



My opinion about (a) is that you have to be 100% sure
that you won't get more than a certain amount of cells, because:



    
  
  1.   the client system may run out of memory or even crash, as a result
of a 'fat' page
  
  2.   you may unecessarily create network traffic






If response time is critical and the amount of data is not a lot,
another solution maybe displaying the whole result set in one page,
maybe split in several small tables of -say- 10 rows each
(so you can see the first one(s) while the others are being downloaded)
and maybe providing a 'page' index on top of every table for easier
navigation.



My opinion about (b) is that it is a standard 'web'-way of doing things.
'Standard' basically indicates that this debate is closed by 99% of the
industry


in favour of solution (b). I can trust they had better reasons to go
this way.


If performance is an issue, you can always buy a faster server or
upgrade the existing.



Regards,



Damianos.



At 10:58 04/02/00 +0000, you wrote:


>Hi Folks,


>


>As we are talking about navigations in a web-page i.e., "next"


and "back" to


>view


>the data-set. Now I need to hear your opnion about the following two


>approaches: -


>


>a)  Download the whole data once and using client-site scripting


languages,


>display


>     part(s) of the data-set.


>


>b)  Each time execute the CGI script/ PLSQL procedure to fetch the


result


>and then


>     display the result-set.


>


>


>My opnion: -


>


>In approach (a) we would save the execution and would put less load at


the


>server


>moreoever the client response would be in fraction of seconds but if


we are


>talking


>about mission critical applications then we may loose any server side


>updations.


>


>In approach (b) we would get the current data set but at the same time


we


>may be


>bombarding the server with lots of small-small requests and taking


more time


>to


>display the result-set.


>


>


>I would appreciate your responses.


>


>Thanks and Regards,


>


>- Bharat.


>


>PS: Lets assume that we are talking about a web-based shop.


>


>


>> -----Original Message-----


>> From: Jeremy Ovenden [SMTP:jovenden_at_HAZELWEB.CO.UK]


>> Sent: Wednesday, February 02, 2000 7:19 AM


>> To:   WEBCYS_L_at_LISTSERV.VT.EDU


>> Subject:      Re: Paging and makeup


>>


>> Hi in this example, if I understand it correctly, if you had just


viewed


>> rows 1-10, to see 11-20 it would re-read rows 1-10 again plus an


>> additional


>> 10. Is this right?


>>


>> Not that I know better :) but I was wondering whether this is an


efficient


>> way of doing it, or is  there a 'purists' version too?


>>


>> __________________________________________________________________


>> Jeremy Ovenden                   jovenden_at_hazelweb.co.uk


>>


>> -----Original Message-----


>> From: Elizabeth Bell <elizabeth.bell_at_GTRI.GATECH.EDU>


>> To: WEBCYS_L_at_LISTSERV.VT.EDU <WEBCYS_L_at_LISTSERV.VT.EDU>


>> Date: 01 February 2000 16:29


>> Subject: Re: Paging and makeup


>>


>>


>>


>> I've attached a sample procedure which pages through the scott.emp


table.


>> The cursor fetches all the rows from the result set, but only


displays the


>> appropriate report "page".


>>


>> -- Elizabeth Bell


>>


>> At 10:24 AM 2/1/00 +0100, you wrote:


>> >Hi


>> >


>> >I am developing an application with OAS 4.0.8 and Oracle 8.0.6. I


want to


>> create html pages with Next/Previous buttons so users


>> >can navigate through big tables. I have used owa_util.cellsprint


but i


>> havenīt total control over presentation (I cannot, for


>> >example, change alternatively the color of the rows or similar


things).


>> I


>> also have used dynamic SQL but, in this case, how


>> >can i control the paging of the table? How can i close the cursor


if the


>> user, for example, close the browser?


>> >


>> >I am looking for a solution for paging and makeup.


>> >


>> >Thanks in advance.


>> >


>> >Ignacio Fernandez


>> >i_fernandez_at_thepentagon.com


>> >





Date:    Fri, 4 Feb 2000 10:37:07 -0500
From:    Elizabeth Bell <elizabeth.bell_at_GTRI.GATECH.EDU>
Subject: Re: Paging and makeup



I don't think users will browse through more than a few pages of a large
result set.  Nothing motivates a user to refine a search more than the
prospect of browsing through tens (or hundreds!) of pages.  As Matthew
Malcheski pointed out, queries which could return anything larger than a
trivial result set should be fronted by "an easy search form that will
help


the user find the rows that he is searching for."  Ideally, the
generated


report page would have a link back to the search form.



I have re-examined my example, and decided that employing the cursor
%ROWCOUNT attribute is a more elegant solution.  Since I'm changing
from an


implicit to an explicit cursor, I need to define the cursor rowtype:



emp_rec cur_scott_emp%ROWTYPE;



Beyond that, in Ignacio's version, replace:





        if (l_alterno mod 2 = 0) then
          htp.tableRowOpen (cattributes => 'BGCOLOR="#CCCCCC"');
        else
          htp.tableRowOpen;
        end if;
        l_alterno := l_alterno + 1;
        -- ###

          htp.tableData( emp.empno );
          htp.tableData( emp.ename );
          htp.tableData( emp.job );
          htp.tableData( emp.mgr );
          htp.tableData( emp.hiredate );
          htp.tableData( emp.sal );
          htp.tableData( NVL(to_char(emp.comm), '&nbsp;' ));
          htp.tableData( emp.deptno );
       htp.tableRowClose;
     END IF;




   END LOOP;


--------- End Ignacio's Version ----------------------





with:





        if (l_alterno mod 2 = 0) then
          htp.tableRowOpen (cattributes => 'BGCOLOR="#CCCCCC"');
        else
          htp.tableRowOpen;
        end if;
        l_alterno := l_alterno + 1;

          htp.tableData( emp_rec.empno );
          htp.tableData( emp_rec.ename );
          htp.tableData( emp_rec.job );
          htp.tableData( emp_rec.mgr );
          htp.tableData( emp_rec.hiredate );
          htp.tableData( emp_rec.sal );
          htp.tableData( NVL(to_char(emp_rec.comm), '&nbsp;' ) );
          htp.tableData( emp_rec.deptno );
      htp.tableRowClose;




    END IF;



  END LOOP;



  CLOSE cur_scott_emp;

--------- End Elizabeth's Revision ----------------------






Elizabeth Bell


Research Scientist


Georgia Tech Research Institute


(404) 385-0182   Phone


(404) 894-9081   Fax




End of WEBCYS_L Digest - 3 Feb 2000 to 4 Feb 2000 (#2000-28)





Joerg Leute <jl_at_itdesign.de> wrote in message news:88pfu5
$m5i$1_at_news.online.de...


> Hi everybody


>


> i guess this question has been asked a million times before....


>


> i'm trying to implement paging in my applikation and therefore i


would like


> to have a select-statement that returns 10 records from position X


on.....


>


> Thanks for your help


>


> Joerg






Sent via Deja.com http://www.deja.com/



Before you buy.
Received on Mon Feb 21 2000 - 21:04:21 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US