Re: Oracle Webserver + PL/SQL

victor tsien wrote:
>
> When I code a pl/sql to do select from a large database, the rows returned
> might be over thousands and that will make the desktop hanged (depends on
> the memory).
> How can I make it a hundred rows a page to send to the browser or browser
> will
> handle that? (I am using Oracle Webserver)

Hmmm. A very interesting problem, but one I think a lot of people have or will run into. Just off the top of my head, I would say you would have to base it off of sequence numbers. Create a sequence, and have it generate a sequence number for each row inserted into the database. The sequence number is inserted into a field in each row. You may want to use the sequence numbers to create associations between the data in different tables.

Once you have your data set up with a unique sequence number associated with each row, I believe you can accomplish what you want. In the html document you generate for return, set up a hidden field, and store the last sequence number of the last row inserted into the html table. Then, when the user goes to get 100 more rows, or whatever, have this hidden field sent to your PL/SQL procedure, through the CGI URL, and execute the same SELECT as before (this may be another thing you want to store as a hidden field: the CGI URL that generated the first query), but add the condition:

WHERE ... seqnum_field > last_seqnum

In order for this to work, you would also have to use:

ORDER BY seqnum_field

in your SELECT statements to insure that rows will not be repeated in subsequent html output.

The other option, if you do not already use sequence numbers in each row, is to create a query table with the same structure as the html table you want to return, with the addition of a sequence number field. When the user makes the first query, do the SELECT, and INSERT the rows into your query table. Then go through the same process of returning rows in subsequent queries that have a sequence number larger than the last one inserted into the last html table.

The problem in this approach is deciding when to delete the old rows that have already been sent to the user, and how to handle simultaneous transactions going on with the same data.

---Mark
mmiller_at_nyx.cs.du.edu Received on Tue May 20 1997 - 00:00:00 CDT