Oracle Programming Concepts

Well, this is the basic problem of any report: the Web Server does not seem to be coping when the query fetches thousands of rows. Yet, most of the reports are not designed to prevent such outrageous result sets.

There are two specific situations.

1. IT Cell applications are all running ASP currently.
   This means that a lot of things have to be managed "manually." In that case, go to CASE 1.
   
   
2. IT Cell might, in the future, after the IMS (existing), especially the Reports section, becomes a much too sluggish, might opt to relaunch the sites in ASP.NET. In that case, go to CASE 2.

CASE 1. (Solution for Classic ASP)
There are at least two options.
1.1 File Download Option.

The best solution is to send the entire contents as a csv file, which can be opened in Excel and printed out. There are so many solutions available from a simple search on googly, so I'm not recapitulating any. This is the most elegant option, as ASP offers easy exporting to the text or excel or any other format. (Converting to text might be especially easy on the Web Server. Excel files are large depending upon the format. Excel 2003 might be very large. It is also to be noted that these .xls files are not true Excel files, only xml-style files which can also be viewed in a browser after renaming. However, it might be fine for printing out.)

1.2 Paged HTML. ("Normal" response of an ASP application.)

Use a count(*) statement to get the number of records retrieved by the query.

Use three variables :~

cnt_tot the total count
cnt_rec records per page
cnt_pages total page count

Using these three variables, select only the appropriate records from a temporary table. I did the following:

1. Created a dummy table based on the source table. (The source need not be a table. The only requirement is that the table be made "in the image" of the result set.) For an alternative, see below, "Alternative to the Dummy Table".
   
   CREATE TABLE t_books AS (
   SELECT ROWNUM AS rnum, id, title, filesize, filename
   FROM v_books);
   
   
2. Note the additional field, rnum. For a Web application, until we master the use of temporary tables, we might need to use another field, to store something like a session id, so that we do not retrieve irrelevant fields if somehow we forget to delete all the inserted dummy records. So we can have
   
   rnum
   field1
   field2
   ...
   sid (which must be unique for each connected user for each submitted query).
   
   We can use (userid + long form of SYSDATE).
   
   
3. The actual query that retrieves records will be replaced by a count(*) query. This returns a number, say n. (It will soon become clear why we need a stored procedure - better still a package - to accomplish all this.) So we have
   
   cnt_tot = n;
   cnt_rec = 10; /* This can be user-defined;
   /* If so, pass its value to the procedure */
   cnt_pages = CEIL(cnt_tot/cnt_rec) /* to be passed back to Web form */
   
   
4. If the total number of records is large, tell him that unless he REALLY wants to see all that data, he should not proceed.
1. Present him with the total count.
2. Present him the total number of pages he's going to sift through.
3. Give him an idea of the time it is going to take (at approx. 4 kB/s).
4. Also, give him the count of connected users. (150 users connected through *.*.*.* means each user will have a throughput of about 8 kbps one-way, or 4 kbps in duplex)
5. Now present him with a button, "Proceed..."
6. If this button is clicked, populate the data on the server. The user will be presented with the paged view.When user clicks on a link, retrieve that data. (Web form will pass pagenum parameter to procedure.)
   
   
5. The procedure (or function) will return exactly the required pages as per the mouse clicks, as below: ~
   

   SELECT *
   FROM t_books
   WHERE rnum BETWEEN 21 AND 30; /* If user clicks Page 3, etc. */

   (Or, more formally, if you want to make it generic, after a fashion which was once popular in our 'cell:)

   SELECT
   FROM
   WHERE rnum BETWEEN (pagenum - 1) * cnt_rec + 1 AND (pagenum * 10);

6. The result will be sent to the Web Server. however, only minimal data needs to be handled, and data will be handled per-click, not in one go.
   
   
7. When the transaction is done (when the user closes the page or leaves it or the session is otherwise closed), the following things should happen. (Which is why I advocate the usage of a package/procedure).
   

• Delete the contents of the dummy table. This will not hurt if you devise a foolproof tracking field, which you must populate along with the real data.
• Explicitly close the connection, and clean up the Web application memory.

As far as I know this is the only feasible solution in classic ASP. If classic ASP supports DataSet etc., then other solutions can be thought of, but I think ADO will provide only this flexibility (=none).

Alternative to the Dummy Table.
Instead of creating a dummy table with two extra fields, populating these extra fields correctly, etc., you can actually retrieve the correct recordsets in a single query using an inline query, as follows.

SELECT *
FROM (SELECT ROWNUM rnum, a.*
FROM (SELECT * FROM v_books ORDER BY ID) a
WHERE ROWNUM < 20)
WHERE rnum >= 10;

This query will return 10 records from 10 - 19.

(Yet another elegant solution, provided of course you are accomplishing all this in a single transaction, would be to use temporary tables - with the appropriate scope. These can be automatically destroyed when the session is closed/transaction ends. I'm not sure if this is possible directly from ASP. Keen readers are directed to Reference 3.)

So how exactly do the two approaches compare? You can use the subquery method if you decide the user is going to view only a few pages. In this case also, you have to take the count of the total records fetched by the query. If you're sure the user is going to view all the pages, then you can follow the dummy table approach.

CASE 2. (Solution for .NET)
[Forthcoming. I hope. Well, consequent on the quoted condition, this might be a long time in coming. Until we switch over to .NET, that is.]


References

1. *ASP.NET Cookbook; Kittel and LeBlond; O'Reilly
2. Pro .NET Oracle Programming; Williams; Apress
3. *Expert Oracle Signature Edition; Kyte; Apress
4. *Programming MS Visual InterDev 6.0; Evans, Miller, Spencer; MS Press
5. *ASP in a Nutshell; Weissinger; O'Reilly

* means the book is highly recommended. Ref #2 is really a basic book with lots of coverage but really simple stuff. 4 and 5 are classic texts. Ref #1 gives you a lot of ideas, currently into its third edition. Kyte's book is de facto standard on all things Oracle.

Acknowledgments

• IMS Application, esp the ailing Reporting section
• The three colleagues mentioned in the Dedication, not in any specific order but preferably order by DESC;
• ☼ Netscape Navigator 9
• ♫ Junoon (23 songs)
  
• ⌂ Nescafé Sunrise Classic (1 cup)
• ☺Brita (1 full pack)

Please leave some comments... if you find this useful. Comment even otherwise, it doesn't hurt. Took me an hour to think up the solution, and 75 min to blog this over dial-up.